Shulou(Shulou.com)06/01 Report--

In the beginner's understanding of the MySQL deadlock problem from the source code, this article introduces the process of viewing the deadlock by debugging the MySQL source code. This article will talk about a common case.

This time we talk about the love and hatred of the unique index S lock and X lock.

Let's look at a simplified example.

# Construction data CREATE TABLE `t1` (`id` int (11) NOT NULL AUTO_INCREMENT, `name` varchar (10), `level` int (11), PRIMARY KEY (`id`), UNIQUE KEY `uk_ name` (`name`); INSERT INTO `t1` (`name`, `level`) VALUES ('Achievement 0); # the sql statements that have problems are as follows: deadlock INSERT ignore INTO `t1` (`name`, `level`) VALUES (' Achievement 0); update T1 set level = 1 where name = "A"

Using the source code analysis method introduced earlier, we first take a look at what locks are added to these two statements, and then analyze the process of deadlock formation.

The first sentence

INSERT ignore INTO T1 (name, level) VALUES ('Achilles Phone0)

The results obtained in debugging are as follows

You can see that this statement adds a shared lock (S lock) to the unique key uk_name, and succeeds.

The second sentence

Update T1 set level = 1 where name = "A"

Update the database field with a unique key.

This situation has been described in previous articles, which adds an X lock to the unique index and then an X lock to the primary key index.

This makes it very easy to reproduce the deadlock problem, as follows

1. Open two session, one begin

2.session1 executes INSERT ignore INTO T1 (name, level) VALUES.

3.session2 executes INSERT ignore INTO T1 (name, level) VALUES.

4.session1 executes update T1 set level = 1 where name = "A"; enters waiting state

5.session2 executes update T1 set level = 1 where name = "A";, deadlock occurs, is rolled back, and transaction 1 executes successfully

The detailed lock state changes are as follows

T1t2 remarks INSERT IGNORE INTO-t1 successfully acquired uk's S lock DB_SUCCESS-INSERT IGNORE INTOt2 successfully acquired uk's S lock DB_SUCCESSUPDATE-t1 attempted to acquire uk's X lock, but failed, DB_LOCK_WAIT-UPDATEt2 in the waiting state attempted to acquire uk's X lock, and found that the deadlock resulted in DB_DEADLOCK-Deadlockt2 releasing the S lock successfully--

The deadlock log is as follows:

LATEST DETECTED DEADLOCK----181208 2300 ACTIVE sec starting index readmysql tables in use 1, locked 1LOCK WAIT 3 lock struct (s), heap size 376,2 row lock (s) MySQL thread id 12, OS thread handle 0x700010522000 Query id 1424 localhost root Updatingupdate T1 set level = 1 where name = "A" * * (1) WAITING FOR THIS LOCK TO BE GRANTED:RECORD LOCKS space id 89 page no 4 n bits 72 index `uk_ name`of table `lock_de mo2`.`t1` trx id 53A7 lock_mode X locks rec but not gap waitingRecord lock, heap no 2 PHYSICAL RECORD: n_fields 2 Compact format; info bits 00: len 1; hex 41; asc Aten; 1: len 4; hex 80000001; asc * (2) TRANSACTION:TRANSACTION 53A8, ACTIVE 8 sec starting index readmysql tables in use 1, locked 13 lock struct (s), heap size 376,2 row lock (s) MySQL thread id 96, OS thread handle 0x70001062e000, query id 1425 localhost root Updatingupdate T1 set level = 1 where name = "A" * * (2) HOLDS THE LOCK (S): RECORD LOCKS space id 89 page no 4 n bits 72 index `uk_ name`of table `lock_ demo2`.`t1` trx id 53A8 lock mode SRecord lock, heap no 2 PHYSICAL RECORD: n_fields 2; compact format Info bits 00: len 1; hex 41; asc Aten; 1: len 4; hex 80000001; asc; * (2) WAITING FOR THIS LOCK TO BE GRANTED:RECORD LOCKS space id 89 page no 4 n bits 72 index `lock_ name` of table `lock_ demo2`.`t1` trx id 53A8 lock_mode X locks rec but not gap waitingRecord lock, heap no 2 PHYSICAL RECORD: n_fields 2; compact format; info bits 00: len 1; hex 41; asc Aten; 1: len 4; hex 80000001; asc; * * WE ROLL BACK TRANSACTION (2)

Let's take a closer look at this deadlock log.

* * (1) WAITING FOR THIS LOCK TO BE GRANTED:RECORD LOCKS space id 89 page no 4 n bits 72 index uk_name of table lock_demo2.t1 trx id 53A7 lock_mode X locks rec but not gap waiting

Transaction 1 wants to acquire an X lock on the uk_name unique index (a record lock that is not a gap lock)

* * (2) HOLDS THE LOCK (S): RECORD LOCKS space id 89 page no 4 n bits 72 index uk_name of table lock_demo2.t1 trx id 53A8 lock mode S

Transaction 2 holds the S lock on the uk_name unique index (shared lock)

* * (2) WAITING FOR THIS LOCK TO BE GRANTED:RECORD LOCKS space id 89 page no 4 n bits 72 index uk_name of table lock_demo2.t1 trx id 53A8 lock_mode X locks rec but not gap waiting

Transaction 2 wants to acquire an X lock on the uk_name unique index (a record lock that is not a gap lock)

It is consistent with the previous theoretical inference.

The above is the whole content of this article, I hope it will be helpful to your study, and I also hope that you will support it.

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.