MySQL死锁分析一例

Tomcat日志报死锁错误，show innodb status获取死锁信息：

------------------------
LATEST DETECTED DEADLOCK
------------------------
181107  9:30:46
*** (1) TRANSACTION:
TRANSACTION 2067C318, ACTIVE 16 sec fetching rows
mysql tables in use 1, locked 1
LOCK WAIT 2183 lock struct(s), heap size 407992, 145 row lock(s), undo log entries 8
MySQL thread id 20259, OS thread handle 0x189c, query id 276602977 localhost 127.0.0.1 root updating
delete from a93 where patientId=164136 and count=9
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 4609 page no 4 n bits 136 index `PRIMARY` of table `emr_new2`.`a93` trx id 2067C318 lock_mode X locks rec but not gap waiting
Record lock, heap no 4 PHYSICAL RECORD: n_fields 71; compact format; info bits 0
 0: len 4; hex 00000003; asc     ;;
 1: len 6; hex 000010d0240c; asc     $ ;;
 2: len 7; hex 9b000023d70110; asc    #   ;;
 3: len 2; hex 3630; asc 60;;
 4: len 1; hex 31; asc 1;;
 5: len 8; hex d2f2c0bbceb2d1d7; asc         ;;
 6: len 10; hex d0d0c0bbceb2c7d0b3fd; asc           ;;
 7: len 0; hex ; asc ;;
 8: len 1; hex 32; asc 2;;
 9: len 0; hex ; asc ;;
 10: len 0; hex ; asc ;;
 11: len 0; hex ; asc ;;
 12: len 0; hex ; asc ;;
 13: len 0; hex ; asc ;;
 14: len 0; hex ; asc ;;
 15: len 0; hex ; asc ;;
 16: len 0; hex ; asc ;;
 17: len 0; hex ; asc ;;
 18: len 0; hex ; asc ;;
 19: len 0; hex ; asc ;;
 20: len 0; hex ; asc ;;
 21: len 0; hex ; asc ;;
 22: len 0; hex ; asc ;;
 23: len 0; hex ; asc ;;
 24: len 0; hex ; asc ;;
 25: len 0; hex ; asc ;;
 26: len 0; hex ; asc ;;
 27: len 0; hex ; asc ;;
 28: len 0; hex ; asc ;;
 29: len 0; hex ; asc ;;
 30: len 0; hex ; asc ;;
 31: len 0; hex ; asc ;;
 32: len 0; hex ; asc ;;
 33: len 0; hex ; asc ;;
 34: len 0; hex ; asc ;;
 35: len 0; hex ; asc ;;
 36: len 0; hex ; asc ;;
 37: len 0; hex ; asc ;;
 38: len 0; hex ; asc ;;
 39: len 0; hex ; asc ;;
 40: len 0; hex ; asc ;;
 41: len 0; hex ; asc ;;
 42: len 0; hex ; asc ;;
 43: len 0; hex ; asc ;;
 44: len 0; hex ; asc ;;
 45: len 0; hex ; asc ;;
 46: len 0; hex ; asc ;;
 47: len 0; hex ; asc ;;
 48: len 0; hex ; asc ;;
 49: len 0; hex ; asc ;;
 50: len 0; hex ; asc ;;
 51: len 0; hex ; asc ;;
 52: len 0; hex ; asc ;;
 53: len 0; hex ; asc ;;
 54: len 0; hex ; asc ;;
 55: SQL NULL;
 56: len 1; hex 30; asc 0;;
 57: len 1; hex 30; asc 0;;
 58: len 1; hex 6e; asc n;;
 59: len 4; hex 80000a40; asc    @;;
 60: len 4; hex 80000000; asc     ;;
 61: len 2; hex 3020; asc 0 ;;
 62: len 0; hex ; asc ;;
 63: len 20; hex 3020202020202020202020202020202020202020; asc 0                   ;;
 64: len 6; hex 6e2020202020; asc n     ;;
 65: len 4; hex 59f974ee; asc Y t ;;
 66: len 2; hex 3931; asc 91;;
 67: len 6; hex cdf5bed5c7ef; asc       ;;
 68: len 30; hex 3230c4eac7b0d2f2d3d2b1c7b2e0c6a4b7f4bbf9b5d7cfb8b0fbb0a9a3ac; asc 20                            ; (total 114 bytes);
 69: len 0; hex ; asc ;;
 70: len 4; hex 80000001; asc     ;;

*** (2) TRANSACTION:
TRANSACTION 2067C43F, ACTIVE 7 sec fetching rows, thread declared inside InnoDB 424
mysql tables in use 1, locked 1
2223 lock struct(s), heap size 440760, 192 row lock(s), undo log entries 8
MySQL thread id 20135, OS thread handle 0xf28, query id 276602976 localhost 127.0.0.1 root updating
delete from a93 where patientId=164136 and count=9
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 4609 page no 4 n bits 136 index `PRIMARY` of table `emr_new2`.`a93` trx id 2067C43F lock_mode X locks rec but not gap
Record lock, heap no 4 PHYSICAL RECORD: n_fields 71; compact format; info bits 0
 0: len 4; hex 00000003; asc     ;;
 1: len 6; hex 000010d0240c; asc     $ ;;
 2: len 7; hex 9b000023d70110; asc    #   ;;
 3: len 2; hex 3630; asc 60;;
 4: len 1; hex 31; asc 1;;
 5: len 8; hex d2f2c0bbceb2d1d7; asc         ;;
 6: len 10; hex d0d0c0bbceb2c7d0b3fd; asc           ;;
 7: len 0; hex ; asc ;;
 8: len 1; hex 32; asc 2;;
 9: len 0; hex ; asc ;;
 10: len 0; hex ; asc ;;
 11: len 0; hex ; asc ;;
 12: len 0; hex ; asc ;;
 13: len 0; hex ; asc ;;
 14: len 0; hex ; asc ;;
 15: len 0; hex ; asc ;;
 16: len 0; hex ; asc ;;
 17: len 0; hex ; asc ;;
 18: len 0; hex ; asc ;;
 19: len 0; hex ; asc ;;
 20: len 0; hex ; asc ;;
 21: len 0; hex ; asc ;;
 22: len 0; hex ; asc ;;
 23: len 0; hex ; asc ;;
 24: len 0; hex ; asc ;;
 25: len 0; hex ; asc ;;
 26: len 0; hex ; asc ;;
 27: len 0; hex ; asc ;;
 28: len 0; hex ; asc ;;
 29: len 0; hex ; asc ;;
 30: len 0; hex ; asc ;;
 31: len 0; hex ; asc ;;
 32: len 0; hex ; asc ;;
 33: len 0; hex ; asc ;;
 34: len 0; hex ; asc ;;
 35: len 0; hex ; asc ;;
 36: len 0; hex ; asc ;;
 37: len 0; hex ; asc ;;
 38: len 0; hex ; asc ;;
 39: len 0; hex ; asc ;;
 40: len 0; hex ; asc ;;
 41: len 0; hex ; asc ;;
 42: len 0; hex ; asc ;;
 43: len 0; hex ; asc ;;
 44: len 0; hex ; asc ;;
 45: len 0; hex ; asc ;;
 46: len 0; hex ; asc ;;
 47: len 0; hex ; asc ;;
 48: len 0; hex ; asc ;;
 49: len 0; hex ; asc ;;
 50: len 0; hex ; asc ;;
 51: len 0; hex ; asc ;;
 52: len 0; hex ; asc ;;
 53: len 0; hex ; asc ;;
 54: len 0; hex ; asc ;;
 55: SQL NULL;
 56: len 1; hex 30; asc 0;;
 57: len 1; hex 30; asc 0;;
 58: len 1; hex 6e; asc n;;
 59: len 4; hex 80000a40; asc    @;;
 60: len 4; hex 80000000; asc     ;;
 61: len 2; hex 3020; asc 0 ;;
 62: len 0; hex ; asc ;;
 63: len 20; hex 3020202020202020202020202020202020202020; asc 0                   ;;
 64: len 6; hex 6e2020202020; asc n     ;;
 65: len 4; hex 59f974ee; asc Y t ;;
 66: len 2; hex 3931; asc 91;;
 67: len 6; hex cdf5bed5c7ef; asc       ;;
 68: len 30; hex 3230c4eac7b0d2f2d3d2b1c7b2e0c6a4b7f4bbf9b5d7cfb8b0fbb0a9a3ac; asc 20                            ; (total 114 bytes);
 69: len 0; hex ; asc ;;
 70: len 4; hex 80000001; asc     ;;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 4609 page no 5 n bits 144 index `PRIMARY` of table `emr_new2`.`a93` trx id 2067C43F lock_mode X locks rec but not gap waiting
Record lock, heap no 36 PHYSICAL RECORD: n_fields 71; compact format; info bits 0
 0: len 4; hex 00000068; asc    h;;
 1: len 6; hex 000010fb7bde; asc     { ;;
 2: len 7; hex 4a000040381ed0; asc J  @8  ;;
 3: len 0; hex ; asc ;;
 4: len 0; hex ; asc ;;
 5: len 0; hex ; asc ;;
 6: len 0; hex ; asc ;;
 7: len 0; hex ; asc ;;
 8: len 1; hex 33; asc 3;;
 9: len 0; hex ; asc ;;
 10: len 0; hex ; asc ;;
 11: len 0; hex ; asc ;;
 12: len 0; hex ; asc ;;
 13: len 0; hex ; asc ;;
 14: len 0; hex ; asc ;;
 15: len 0; hex ; asc ;;
 16: len 0; hex ; asc ;;
 17: len 0; hex ; asc ;;
 18: len 0; hex ; asc ;;
 19: len 0; hex ; asc ;;
 20: len 0; hex ; asc ;;
 21: len 12; hex bfd5b8b9d1aaccc7cadccbf0; asc             ;;
 22: len 1; hex 33; asc 3;;
 23: len 4; hex c4ead3e0; asc     ;;
 24: len 10; hex ceb4d3c3d2a9d6cec1c6; asc           ;;
 25: len 0; hex ; asc ;;
 26: len 0; hex ; asc ;;
 27: len 0; hex ; asc ;;
 28: len 0; hex ; asc ;;
 29: len 0; hex ; asc ;;
 30: len 0; hex ; asc ;;
 31: len 0; hex ; asc ;;
 32: len 0; hex ; asc ;;
 33: len 0; hex ; asc ;;
 34: len 0; hex ; asc ;;
 35: len 0; hex ; asc ;;
 36: len 0; hex ; asc ;;
 37: len 0; hex ; asc ;;
 38: len 0; hex ; asc ;;
 39: len 0; hex ; asc ;;
 40: len 0; hex ; asc ;;
 41: len 0; hex ; asc ;;
 42: len 0; hex ; asc ;;
 43: len 0; hex ; asc ;;
 44: len 0; hex ; asc ;;
 45: len 0; hex ; asc ;;
 46: len 0; hex ; asc ;;
 47: len 0; hex ; asc ;;
 48: len 0; hex ; asc ;;
 49: len 0; hex ; asc ;;
 50: len 0; hex ; asc ;;
 51: len 0; hex ; asc ;;
 52: len 0; hex ; asc ;;
 53: len 0; hex ; asc ;;
 54: len 0; hex ; asc ;;
 55: SQL NULL;
 56: len 1; hex 30; asc 0;;
 57: len 1; hex 30; asc 0;;
 58: len 1; hex 6e; asc n;;
 59: len 4; hex 80000876; asc    v;;
 60: len 4; hex 80000000; asc     ;;
 61: len 2; hex 3020; asc 0 ;;
 62: len 0; hex ; asc ;;
 63: len 20; hex 3020202020202020202020202020202020202020; asc 0                   ;;
 64: len 6; hex 6e2020202020; asc n     ;;
 65: len 4; hex 5a1a2d94; asc Z - ;;
 66: len 4; hex 31373133; asc 1713;;
 67: len 6; hex d5c5d1debaec; asc       ;;
 68: len 9; hex 756e646566696e6564; asc undefined;;
 69: len 3; hex 313435; asc 145;;
 70: len 4; hex 80000001; asc     ;;

*** WE ROLL BACK TRANSACTION (1)

TRANSACTION 2067C318, ACTIVE 16 sec fetching rows
mysql tables in use 1, locked 1
LOCK WAIT 2183 lock struct(s), heap size 407992, 145 row lock(s), undo log entries 8
MySQL thread id 20259, OS thread handle 0x189c, query id 276602977 localhost 127.0.0.1 root updating
delete from a93 where patientId=164136 and count=9
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 4609 page no 4 n bits 136 index `PRIMARY` of table `emr_new2`.`a93` trx id 2067C318 lock_mode X locks rec but not gap waiting
Record lock, heap no 4 PHYSICAL RECORD: n_fields 71; compact format; info bits 0

*** (2) TRANSACTION:
TRANSACTION 2067C43F, ACTIVE 7 sec fetching rows, thread declared inside InnoDB 424
mysql tables in use 1, locked 1
2223 lock struct(s), heap size 440760, 192 row lock(s), undo log entries 8
MySQL thread id 20135, OS thread handle 0xf28, query id 276602976 localhost 127.0.0.1 root updating
delete from a93 where patientId=164136 and count=9
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 4609 page no 4 n bits 136 index `PRIMARY` of table `emr_new2`.`a93` trx id 2067C43F lock_mode X locks rec but not gap
Record lock, heap no 4 PHYSICAL RECORD: n_fields 71; compact format; info bits 0

可以看到两个事务 TRANSACTION 2067C318 和 TRANSACTION 2067C43F 分别执行： delete from a93 wehre patientId=164136 and count=9 

导致了相互等待。

查看表结构和数据量，发现 patientId未加索引，数据量很大，导致delete语句会全表扫描，容易长时间持有锁。

加上索引： alter table a93 add index patientId(patientId);