MySQL Deadlock analysis

1. Test description

Environment Description: RHEL 6.4 x86_64 + MySQL 5.5.37, transaction ISOLATION level RC

Test table:

Mysql> Show CREATE TABLE t1\g*************************** 1. Row ***************************       table:t1create table:create Table ' t1 ' (  ' a ' int (one) not NULL DEFAULT ' 0 ',  ' B ' int (one) default NULL,  PRIMARY key (' a '),  key ' B ' (' B ')) Engine=innodb default charset=utf81 row in Set (0.00 SE C

To test the data in the table:

Mysql> SELECT * FROM t1;+----+------+| A  | b    |+----+------+|  1 |    1 | |  3 |    3 | |  5 |    6 |+----+------+3 rows in Set (0.01 sec)

2. Test process

3. Deadlock log analysis This query show engine InnoDB status\g to view the deadlock information, the following only the deadlock Information section, the other omitted.

------------------------LATEST detected DEADLOCK------------------------140824 1:01:24*** (1) TRANSACTION: TRANSACTION 110E, active starting SEC index Read # # transaction id=110e, active 73smysql tables in use 1, locked 1LOCK WAIT 3 lock s Truct (s), Heap size 376, 2 row lock (s) # # There are 2 row locks MySQL thread ID 1, OS thread handle 0x7f55ea639700, query ID bayi localhost Root updating # # The thread of the transaction id=1delete from T1 where A=1 # # This is the current transaction execution sql*** (1) Waiting for this LOCK to be granted: # # above S QL waits for lock information record LOCKS Space ID of page No 3 n bits index ' PRIMARY ' of table ' test '. ' T1 ' Trx ID 110E lock_mode X LOCKS re C but not gap Waitingrecord lock, heap No 2 physical record:n_fields 4; Compact format; Info bits 32 # # waits for an X lock (not gap waiting) on the page num=3 on the primary key, locking the bits 0:len 4; Hex 80000001; ASC;; 1:len 6; Hex 00000000110c; ASC;; 2:len 7; Hex 0d000002350084; ASC 5;; 3:len 4; Hex 80000001; ASC;; (2) transaction:transaction 110C, ACTIVE 1716 sec starting index read, thread declared INSIDE InnoDB 500 # # transaction id=110c, active 1716smysql tables in use 1, locked-lock struct (s), Heap size 376, 2 row lock (s), Undo L OG entries 1 # # 3 locks, 2 row locks, 1 undo logmysql Thread ID 2, OS thread handle 0x7f563c05e700, query ID--localhost root updatin G # # The thread of the transaction id=2delete from T1 where A=3 # # This is the current transaction execution sql*** (2) holds the Lock (S): # # This transaction holds the lock information record LOCKS Space ID 1 2 Page No 3 n bits index ' PRIMARY ' of table ' test '. ' T1 ' Trx ID 110C lock_mode X locks Rec but not gaprecord lock, heap No 2 physical record:n_fields 4; Compact format; Info bits 32 # # has an X lock (not gap) on the page num=3 on the primary key, locking the bits 0:len 4; Hex 80000001; ASC;; 1:len 6; Hex 00000000110c; ASC;; 2:len 7; Hex 0d000002350084; ASC 5;; 3:len 4; Hex 80000001; ASC;;  (2) Waiting for this lock to be granted: # # at the same time this transaction also waits for the lock information record LOCKS Space ID of page No 3 n bits index ' PRIMARY ' of table ' Test '. ' T1 ' Trx ID 110C lock_mode X locks Rec but not gap Waitingrecord lock, heap No 3 physical Record:n_field s 4;Compact format; Info bits 0 # # also waits for the page num=3 on the primary key to have an X lock (not gap waiting), locking the bits 0:len 4; Hex 80000003; ASC;; 1:len 6; Hex 000000000f71; ASC q;; 2:len 7; Hex ed0000022f0090; ASC/;; 3:len 4; Hex 80000003; ASC;; WE Roll Back TRANSACTION (1) # # Here Choose ROLLBACK TRANSACTION 110E. That is, this deadlock uses SQL in transaction 110E without execution, rollback: delete from T1 where a=1 and SQL in transaction 110C is performed normally: delete from T1 where a=3

--Bosco----END----

-------------------------------------------------------------------------------------------------------

Copyright, the article allows reprint, but must be linked to the source address, otherwise investigate legal liability !

MySQL Deadlock analysis