MySQL deadlock analysis, mysql deadlock

MySQL deadlock analysis, mysql deadlock
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!

Mysql deadlock: how to solve the mysql deadlock

Run show engine innodb status \ G on the mysql command line to view the SQL statements that cause deadlocks, analyze the index conditions, optimize the SQL statements, and then show processlist. In addition, you can enable slow query logs, in linux. add the following content to [mysqld] of cnf:

Mysql runs the deadlock check command. The result is as follows:

No problem. What do you mean by using this statement?
Show status displays some statistics of MYSQL server running. Are you optimizing tables or statistics?
Mysql cannot be restarted.