Understanding MySQL deadlock routines through unique index S lock and X lock

In Beginners' Understanding MySQL Deadlock Problems from Source Code, we introduced the process of debugging MySQL source code to view deadlocks. This article will talk about a common case.
This time we will talk about the love-hate relationship between unique index S lock and X lock.

Let's look at a simplified example

# Construct 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 ('A',0);

# The problematic SQL statement is as follows, which will cause a deadlock in concurrent situations INSERT ignore INTO `t1` (`name`, `level`) VALUES ('A',0);
update t1 set level = 1 where name = "A";

We use the source code analysis method introduced before to first look at what locks are added to these two statements, and then analyze the process of deadlock formation.

First statement

INSERT ignore INTO t1 (name, level) VALUES ('A',0);

The results obtained during 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 statement

update t1 set level = 1 where name = "A";

Updates a database field by a unique key.

This situation has been introduced in the previous article. X lock will be added to the unique index, and then X lock will be added to the primary key index.

In this way, the deadlock problem can be reproduced very easily. The steps are as follows

1. Open two sessions, begin
2.session1 executes INSERT ignore INTO t1 (name, level) VALUES ('A',0);
3.session2 executes INSERT ignore INTO t1 (name, level) VALUES ('A',0);
4.session1 executes update t1 set level = 1 where name = "A"; and enters the waiting state
5. Session2 executes update t1 set level = 1 where name = "A";, a deadlock occurs and is rolled back, while transaction 1 is executed successfully

The detailed lock status changes are as follows

t1	t2	Remark
INSERT IGNORE INTO	-	t1 successfully obtains the S lock of ukDB_SUCCESS
-	INSERT IGNORE INTO	t2 successfully obtains the S lock of ukDB_SUCCESS
UPDATE	-	t1 tries to obtain the X lock of uk, but fails and is in the waiting state DB_LOCK_WAIT
-	UPDATE	t2 tries to obtain the X lock of uk and finds a deadlock, generating DB_DEADLOCK
-	Deadlock	t2 Release S lock
success	-	-

The deadlock log is as follows:

LATEST DETECTED DEADLOCK
------------------------
181208 23:00:52
*** (1) TRANSACTION:
TRANSACTION 53A7, ACTIVE 162 sec starting index read
mysql tables in use 1, locked 1
LOCK 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 Updating
update 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_demo2`.`t1` trx id 53A7 lock_mode X locks rec but not gap waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 1; hex 41; asc A;;
 1: len 4; hex 80000001; asc ;;

*** (2) TRANSACTION:
TRANSACTION 53A8, ACTIVE 8 sec starting index read
mysql tables in use 1, locked 1
3 lock struct(s), heap size 376, 2 row lock(s)
MySQL thread id 96, OS thread handle 0x70001062e000, query id 1425 localhost root Updating
update 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 S
Record lock, heap no 2 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 1; hex 41; asc A;;
 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 `uk_name` of table `lock_demo2`.`t1` trx id 53A8 lock_mode X locks rec but not gap waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 1; hex 41; asc A;;
 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 obtain an X lock (non-gap lock record lock) on the unique index uk_name

*** (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 an S lock (shared lock) on the unique index uk_name

*** (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 obtain an X lock (non-gap lock record lock) on the uk_name unique index.
This is consistent with the previous theoretical conclusions.

The above is the full content of this article. I hope it will be helpful for everyone’s study. I also hope that everyone will support 123WORDPRESS.COM.

You may also be interested in: