- 论坛徽章:
- 0
|
本帖最后由 justin033 于 2010-04-10 12:47 编辑
RT,死锁的信息如下:- ------------------------
- LATEST DETECTED DEADLOCK
- ------------------------
- 100409 10:19:46
- *** (1) TRANSACTION:
- TRANSACTION 28B491, ACTIVE 0 sec, process no 12065, OS thread id 1101347136 fetching rows
- mysql tables in use 1, locked 1
- LOCK WAIT 39 lock struct(s), heap size 6752, 79 row lock(s)
- MySQL thread id 1079022, query id 13143355 10.10.10.151 yilin init
- update email_package set claimed_process = '1010101519330', start_time = '2010-04-09 10:24:24.0', task_status_id = 1 where service_id = 'Carrier' and (task_status_id = 0 or (task_status_id = 1 and start_time <= '2010-04-09 09:24:24.0')) and (schedule_time is null or schedule_time <= '2010-04-09 10:24:24.0') and domain_name not in ('126.com','qq.com') and (claimed_process is null or claimed_process = '1010101519330') order by immediately desc, mixed_priority asc, priority asc limit 1
- *** (1) WAITING FOR THIS LOCK TO BE GRANTED:
- RECORD LOCKS space id 0 page no 3080 n bits 1120 index `ix_ep_01` of table `carrierDB_0`.`email_package` trx id 28B491 lock_mode X waiting
- *** (2) TRANSACTION:
- TRANSACTION 28B490, ACTIVE 0 sec, process no 12065, OS thread id 1101879616 inserting, thread declared inside InnoDB 500
- mysql tables in use 1, locked 1
- 3 lock struct(s), heap size 1216, 2 row lock(s), undo log entries 5
- MySQL thread id 1079021, query id 13143358 10.10.10.141 yilin update
- insert into email_package (task_id,task_name,task_type_id,service_id, client_id,user_id,object_id,group_id,task_status_id,immediately,schedule_time, start_time,end_time,create_time,order_number,priority,restart_times,weight,parameters,language,domain_name,claimed_process) values ('8bkqnje6','send msg 1043.3226-gmail.com',2,'Carrier',1043,144,3226,null,0,'N','2010-04-09 10:24:25.0','1970-01-01 08:00:00.0','1970-01-01 08:00:00.0','2010-04-09 10:24:25.0',0,31,0,11,null,'en','gmail.com',null)
- *** (2) HOLDS THE LOCK(S):
- RECORD LOCKS space id 0 page no 3080 n bits 1120 index `ix_ep_01` of table `carrierDB_0`.`email_package` trx id 28B490 lock_mode X locks rec but not gap
- *** (2) WAITING FOR THIS LOCK TO BE GRANTED:
- RECORD LOCKS space id 0 page no 3080 n bits 1120 index `ix_ep_01` of table `carrierDB_0`.`email_package` trx id 28B490 lock_mode X locks gap before rec insert intention waiting
- *** WE ROLL BACK TRANSACTION (2)
复制代码 表的索引结构如下:- "email_package","0","PRIMARY","1","task_id","A","95",NULL,NULL,"","BTREE",""
- "email_package","1","ix_ep_01","1","task_status_id","A","7",NULL,NULL,"","BTREE",""
复制代码- update email_package set claimed_process = '1010101519330', start_time = '2010-04-09 10:24:24.0', task_status_id = 1 where service_id = 'Carrier' and (task_status_id = 0 or (task_status_id = 1 and start_time <= '2010-04-09 09:24:24.0')) and (schedule_time is null or schedule_time <= '2010-04-09 10:24:24.0') and domain_name not in ('126.com','qq.com') and (claimed_process is null or claimed_process = '1010101519330') order by immediately desc, mixed_priority asc, priority asc limit 1
复制代码
- insert into email_package (task_id,task_name,task_type_id,service_id, client_id,user_id,object_id,group_id,task_status_id,immediately,schedule_time, start_time,end_time,create_time,order_number,priority,restart_times,weight,parameters,language,domain_name,claimed_process) values ('8bkqnje6','send msg 1043.3226-gmail.com',2,'Carrier',1043,144,3226,null,0,'N','2010-04-09 10:24:25.0','1970-01-01 08:00:00.0','1970-01-01 08:00:00.0','2010-04-09 10:24:25.0',0,31,0,11,null,'en','gmail.com',null)
复制代码
由于这个表email_package的insert、update都挺经常。上述红色的两条sql,和开发人员交流了说业务逻辑是那么复杂的,暂时没法改。现在就是经常出现死锁。有什么好的建议, |
|