MySQL deadlock Analysis

MySQL deadlock Analysis
1. Test Description

Environment Description: RHEL 6.4 x86_64 + MySQL 5.5.37. The transaction isolation level is RC.

Test Table:

mysql> show create table t1\G*************************** 1. row ***************************       Table: t1Create Table: CREATE TABLE `t1` (  `a` int(11) NOT NULL DEFAULT '0',  `b` int(11) DEFAULT NULL,  PRIMARY KEY (`a`),  KEY `b` (`b`)) ENGINE=InnoDB DEFAULT CHARSET=utf81 row in set (0.00 sec)

Data in the test table:

mysql> select * from t1;+----+------+| a  | b    |+----+------+|  1 |    1 ||  3 |    3 ||  5 |    6 |+----+------+3 rows in set (0.01 sec)

2. Test process
3. Analyze the deadlock log. query the show engine innodb status \ G to view the deadlock information. Only the deadlock Information Section is extracted, and other information is omitted.

------------------------ Latest detected deadlock---140824 1:01:24 *** (1) TRANSACTION: TRANSACTION 110E, ACTIVE 73 sec starting index read # transaction id = 110E, ACTIVE 73 smysql tables in use 1, locked 1 lock wait 3 lock struct (s), heap size 376, 2 row lock (s) # There are 2 rows to lock MySQL thread id 1, OS thread handle 0x7f55ea639700, query id 81 localhost root updating # The thread ID of the transaction = 1 delete from t1 where a = 1 ## THIS is the SQL statement executed by the current transaction *** (1) WAITING FOR THIS LOCK TO BE GRANTED: # record locks space id 12 page no 3 n bits 80 index 'primary' of table 'test '. 't1' trx id 110E lock_mode X locks rec but not gap waitingRecord lock, heap no 2 physical record: n_fields 4; compact format; info bits 32 # Wait for an X lock (not gap waiting) to be added to page num = 3 on the primary key, lock 80 bits 0: len 4; hex 80000001; asc ;; 1: len 6; hex 201710000110c; 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 1716 smysql tables in use 1, locked 13 lock struct (s), heap size 376, 2 row lock (s), undo log entries 1 #3 locks, 2 row locks, 1 undo logMySQL thread id 2, OS thread handle 0x7f563c05e700, query id 82 localhost Root updating # THE Transaction Thread ID = 2 delete from t1 where a = 3 # This is the SQL statement executed by THE current transaction *** (2) HOLDS THE LOCK (S ): # record locks space id 12 page no 3 n bits 80 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 # An X lock (not gap) is held on page num = 3 on the primary key, and the lock 80 bits 0: len 4; hex 80000001; asc; 1: len 6; Hex 201710000110c; asc; 2: len 7; hex 0d000002350084; asc 5; 3: len 4; hex 80000001; asc; *** (2) waiting for this lock to be granted: # record locks space id 12 page no 3 n bits 80 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_fields 4; compact format; info bits 0 # Add an X to the page num = 3 of the primary key Lock (not gap waiting), lock 80 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 WE chose to roll back the TRANSACTION 110E. That is to say, this deadlock uses the SQL statement in transaction 110E not executed, and rolled back: delete from t1 where a = 1 and the SQL statement in transaction 110C is normally executed: delete from t1 where a = 3

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

Bytes -------------------------------------------------------------------------------------------------------

All rights reserved. reprinted articles are allowed, but source addresses must be indicated by links. Otherwise, the documents will be held legally responsible!