innodb死鎖問題

justin033

RT，死鎖的信息如下：

1. ------------------------
   
2. LATEST DETECTED DEADLOCK
   
3. ------------------------
   
4. 100409 10:19:46
   
5. *** (1) TRANSACTION:
   
6. TRANSACTION 28B491, ACTIVE 0 sec, process no 12065, OS thread id 1101347136 fetching rows
   
7. mysql tables in use 1, locked 1
   
8. LOCK WAIT 39 lock struct(s), heap size 6752, 79 row lock(s)
   
9. MySQL thread id 1079022, query id 13143355 10.10.10.151 yilin init
   
10. 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
    
11. *** (1) WAITING FOR THIS LOCK TO BE GRANTED:
    
12. 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
    
13. *** (2) TRANSACTION:
    
14. TRANSACTION 28B490, ACTIVE 0 sec, process no 12065, OS thread id 1101879616 inserting, thread declared inside InnoDB 500
    
15. mysql tables in use 1, locked 1
    
16. 3 lock struct(s), heap size 1216, 2 row lock(s), undo log entries 5
    
17. MySQL thread id 1079021, query id 13143358 10.10.10.141 yilin update
    
18. 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)
    
19. *** (2) HOLDS THE LOCK(S):
    
20. 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
    
21. *** (2) WAITING FOR THIS LOCK TO BE GRANTED:
    
22. 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
    
23. *** WE ROLL BACK TRANSACTION (2)

復(fù)制代碼

表的索引結(jié)構(gòu)如下：

1. "email_package","0","PRIMARY","1","task_id","A","95",NULL,NULL,"","BTREE",""
   
2. "email_package","1","ix_ep_01","1","task_status_id","A","7",NULL,NULL,"","BTREE",""

復(fù)制代碼

1. 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

復(fù)制代碼

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)

復(fù)制代碼

由于這個表email_package的insert、update都挺經(jīng)常。上述紅色的兩條sql，和開發(fā)人員交流了說業(yè)務(wù)邏輯是那么復(fù)雜的，暫時沒法改�，F(xiàn)在就是經(jīng)常出現(xiàn)死鎖。有什么好的建議，

denniswwh

首先，要根據(jù)update那句的條件優(yōu)化索引。
其次，把update改成兩句，先select出來主鍵ID，再按主鍵ID更新。

pkman110

目前不能更改update語句，呵呵，還有其他好辦法么？

justin033

占位。。。

pkman110

1. *** (1) WAITING FOR THIS LOCK TO BE GRANTED:
   
2. 
   
3. 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
   
4. 
   

復(fù)制代碼

1. *** (2) HOLDS THE LOCK(S):
   
2. 
   
3. 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

復(fù)制代碼

兩個玩意都爭用這個索引上的鎖了

pkman110

說明死鎖是由這個索引造成的，你這個查詢寫的索引好差啊。。。。我汗了

justin033

正解。學(xué)習(xí)了。。。

Coolriver

正解。學(xué)習(xí)了。。。
justin033 發(fā)表于 2010-04-10 11:51

    這種SQL可以說是表在設(shè)計時造成的。
    優(yōu)化這種SQL有兩個方法：
    一、改表結(jié)構(gòu)，走第三范式設(shè)計。
    二、用更強(qiáng)的機(jī)器的機(jī)器。