MySQL Deadlock Log Analysis

2. ------------------------
4. LATEST detected DEADLOCK
6. ------------------------
8. 140824 1:01:24
10. (1) TRANSACTION:
12. TRANSACTION 110E, active starting SEC index Read # # transaction id=110E, active 73s
14. MySQL tables in use 1, locked 1
16. Lock WAIT 3 lock struct (s), Heap size 376, 2 row lock (s) # # There are 2 row locks
18. MySQL thread ID 1, OS thread handle 0x7f55ea639700, query ID bayi localhost root updating # # threads for the transaction id=1
20. Delete from T1 where a=1 # # This is the SQL that the current transaction executes
22. (1) Waiting for the lock to be granted: # # above SQL waits for the locking information
24. RECORD LOCKS Space ID page No 3 n bits index ' PRIMARY ' of table ' test '. ' T1 ' Trx ID 110E lock_mode X LOCKS Rec But no T gap waiting
26. Record Lock, Heap No 2 physical record:n_fields 4; Compact format; Info bits 32 # # Wait on the primary key on page num=3 with an X lock (not gap waiting), lock the bits
28. 0:len 4; Hex 80000001; ASC;;
30. 1:len 6; Hex 00000000110c; ASC;;
32. 2:len 7; Hex 0d000002350084; ASC 5;;
34. 3:len 4; Hex 80000001; ASC;;
38. (2) TRANSACTION:
40. TRANSACTION 110C, active 1716 SEC starting index read, thread declared inside InnoDB 500 # # transactions id=110C, active 1716s /c1>
42. MySQL tables in use 1, locked 1
44. 3 lock struct (s), Heap size 376, 2 row lock (s), undo log Entries 1 # # 3 lock, 2 row lock, 1 undo Log
46. MySQL thread ID 2, OS thread handle 0x7f563c05e700, query ID of updating localhost root # # # threads for the transaction id=2
48. Delete from T1 where a=3 # # This is the SQL that the current transaction executes
50. (2) holds the Lock (S): # # This transaction holds the lock information
52. RECORD LOCKS Space ID page No 3 n bits index ' PRIMARY ' of table ' test '. ' T1 ' Trx ID 110C lock_mode X LOCKS Rec But no T gap
54. Record Lock, Heap No 2 physical record:n_fields 4; Compact format; Info bits 32 # # on the primary key on page num=3 already holds an X lock (not gap), lock the bits
56. 0:len 4; Hex 80000001; ASC;;
58. 1:len 6; Hex 00000000110c; ASC;;
60. 2:len 7; Hex 0d000002350084; ASC 5;;
62. 3:len 4; Hex 80000001; ASC;;
66. (2) Waiting for the lock to be granted: # # at the same time this transaction also waits for the locking information
68. RECORD LOCKS Space ID page No 3 n bits index ' PRIMARY ' of table ' test '. ' T1 ' Trx ID 110C lock_mode X LOCKS Rec But no T gap waiting
70. Record Lock, Heap No 3 physical record:n_fields 4; Compact format; Info bits 0 # # also waits on page num= 3 on the primary key to have an X lock (not gap waiting), lock the bits
72. 0:len 4; Hex 80000003; ASC;;
74. 1:len 6; Hex 000000000f71; ASC q;;
76. 2:len 7; Hex ed0000022f0090; ASC/;;
78. 3:len 4; Hex 80000003; ASC;;
82. WE Roll Back TRANSACTION (1) # # Here Choose ROLLBACK TRANSACTION 110E.
86. In other words, this deadlock uses SQL in transaction 110E without execution and rolls back:
88. Delete from T1 where a=1
90. The SQL in transaction 110C is executed normally:
92. Delete from T1 where a=3

MySQL Deadlock Log Analysis