Shulou(Shulou.com)05/31 Report--

MySQL how to read deadlock logs, I believe that many inexperienced people do not know what to do, so this article summarizes the causes of the problem and solutions, through this article I hope you can solve this problem.

MySQL 5.6 transaction isolation level is RR

CREATE TABLE `ty` (

`id`int (11) NOT NULL AUTO_INCREMENT

`a`int (11) DEFAULT NULL

`b` int (11) DEFAULT NULL

PRIMARY KEY (`id`)

KEY `idxa` (`a`)

) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8mb4

Insert into ty (arem b) values (2pr 3), (5pr 4), (6pr 7)

2.2 Test cases

T2

T1

Begin

Delete from ty where axi5

Begin

Delete from ty where axi5

Insert into ty (a ~ (b)) values (2 ~ (10))

Delete from ty where axi5

ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

2.3 the log we view through show engine innodb status is the latest log of deadlocks.

-

LATEST DETECTED DEADLOCK

-

2017-09-09 22:34:13 7f78eab82700

* * (1) TRANSACTION: # transaction 1

TRANSACTION 462308399, ACTIVE 33 sec starting index read

Mysql tables in use 1, locked 1

LOCK WAIT 2 lock struct (s), heap size 360,1 row lock (s)

MySQL thread id 3525577, OS thread handle 0x7f896cc4b700, query id 780039657 localhost root updating

Delete from ty where axi5

* * (1) WAITING FOR THIS LOCK TO BE GRANTED:

RECORD LOCKS space id 219 page no 4 n bits 72 index `idxa` of table `test`.ty` trx id 462308399 lock_mode X waiting

* * (2) TRANSACTION:

TRANSACTION 462308398, ACTIVE 61 sec inserting, thread declared inside InnoDB 5000

Mysql tables in use 1, locked 1

5 lock struct (s), heap size 1184, 4 row lock (s), undo log entries 2

MySQL thread id 3525490, OS thread handle 0x7f78eab82700, query id 780039714 localhost root update

Insert into ty (a ~ (b)) values (2 ~ (10))

(2) HOLDS THE LOCK (S):

RECORD LOCKS space id 219 page no 4 n bits 72 index `idxa` of table `test`.ty` trx id 462308398 lock_mode X

* * (2) WAITING FOR THIS LOCK TO BE GRANTED:

RECORD LOCKS space id 219 page no 4 n bits 72 index `idxa` of table `test`.`ty` trx id 462308398 lock_mode X locks gap before rec insert intention waiting

* WE ROLL BACK TRANSACTION (1)

2.4 Log analysis

* * (1) TRANSACTION: # transaction 1

TRANSACTION 462308399, ACTIVE 33 sec starting index read

The transaction number is 462308399, active for 33 seconds, and starting index read indicates that the transaction status is to read data according to the index. Other common states:

Fetching rows indicates that the transaction status is set in row_search_for_mysql, indicating that the record is being looked up.

Updating or deleting indicates that the transaction has really entered the functional logic (row_update_for_mysql) of Update/delete.

Thread declared inside InnoDB indicates that the transaction has entered the innodb layer. Generally speaking, most transactions that are not in the innodb layer are rolled back.

Mysql tables in use 1, which indicates that the current transaction uses a table. Locked 1 indicates that there is a table lock on the table, or LOCK_IX for DML statements

LOCK WAIT 2 lock struct (s), heap size 360,1 row lock (s)

LOCK WAIT indicates waiting for a lock, 2 lock struct (s) indicates that the length of the trx- > trx_locks linked list is 2, and each linked list node represents a lock structure held by the transaction, including table locks, record locks, auto_ Inc locks, and so on. In this case, 2locks stands for IX lock and lock_mode X (Next-key lock)

Heap size 360indicates the amount of lock heap memory allocated by the transaction, which is generally of no specific use.

1 row lock (s) represents the number of row record locks / gap locks held by the current transaction.

Delete from ty where await 5 represents the sql that transaction 1 is executing, but the sad thing is that the show engine innodb status cannot view the sql of the complete transaction, usually showing the sql that is currently waiting for the lock.

* * (1) WAITING FOR THIS LOCK TO BE GRANTED:

RECORD LOCKS space id 219 page no 4 n bits 72 index `idxa` of table `test`.ty` trx id 462308399 lock_mode X waiting

RECORD LOCKS means record lock, space id is 219 bits page number 4, and n bits 72 indicates that there are 72 Bit bits left on the clustered index record lock structure.

Indicates that transaction 1 is waiting for the X lock of idxa on the table ty. In this case, it is actually Next-Key lock.

The log of transaction 2 is similar to the above analysis

(2) HOLDS THE LOCK (S):

RECORD LOCKS space id 219 page no 4 n bits 72 index `idxa` of table `test`.ty` trx id 462308398 lock_mode X

It shows that transaction 2 insert into ty (values b) holds the Lock mode X | LOCK_GAP of transaction 5, but we can't see the delete from ty where execution of transaction 2 in the log; this is also the root cause of the problem that it is difficult for DBA to analyze deadlocks based on the log alone.

* * (2) WAITING FOR THIS LOCK TO BE GRANTED:

RECORD LOCKS space id 219 page no 4 n bits 72 index `idxa` of table `test`.`ty` trx id 462308398 lock_mode X locks gap before rec insert intention waiting

The insert statement indicating transaction 2 is waiting for the insertion of the intention lock lock_mode X locks gap before rec insert intention waiting (LOCK_X + LOCK_REC_GAP)

What needs your attention here is the lock combination, such as lock_mode X waiting, lock_mode X locklock mode X locks gap before rec insert intention waiting is the core focus of our analysis of deadlocks. How to understand the lock combination?

First of all, we need to know that there are two regular lock modes for MySQL.

LOCK_S (read lock, shared lock)

LOCK_X (write lock, exclusive lock)

The most easily understood lock mode, read plus shared lock, write plus exclusive lock.

There are several properties of locks

LOCK_REC_NOT_GAP (lock record)

LOCK_GAP (GAP before lock record)

LOCK_ORDINARY (simultaneously lock the record + GAP before the record. The legendary Next key lock)

LOCK_INSERT_INTENTION (insert intention lock, which is actually a special gap lock)

The properties of the lock can be arbitrarily combined with the lock mode. For example.

Lock- > type_mode can be Lock_X or Lock_S

Locks gap before rec is represented as gap lock: lock- > type_mode & LOCK_GAP

Locks rec but not gap is represented as a record lock, non-gap lock: lock- > type_mode & LOCK_REC_NOT_GAP

Insert intention is expressed as insert intention lock: lock- > type_mode & LOCK_INSERT_INTENTION

Waiting means lock waiting: lock- > type_mode & LOCK_WAIT

After reading the above, have you mastered how to read the deadlock log in MySQL? If you want to learn more skills or want to know more about it, you are welcome to follow the industry information channel, thank you for reading!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.