Understanding MySQL deadlock routine through unique index S locks and X locks

Time:2019-9-1

In the beginner’s understanding of MySQL deadlock from source code, the process of using debugging MySQL source code to view deadlock is introduced. This article describes a common case.
This time we’re going to talk about love and hatred between S lock and X lock, the only index.

Let’s look at a simplified example.

# Constructing 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);

# Questioned SQL statements are as follows, deadlock occurs in concurrent cases
INSERT ignore INTO `t1` (`name`, `level`) VALUES ('A',0);
update t1 set level = 1 where name = "A";

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

The first statement


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

The results obtained in debugging are as follows.

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

The second statement


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

Update database fields with unique keys.

This situation has been described in previous articles.X locks are added to the unique index, and then X locks are added to the primary key index.

This makes it very easy to reproduce deadlock problems, as follows

1. Open two sessions, begin respectively
2. Session 1 executes INSERT ignore INTO T1 (name, level) VALUES (‘A’, 0);
3. Session 2 executes INSERT ignore INTO T1 (name, level) VALUES (‘A’, 0);
4. Session 1 executes update T1 set level = 1 where name = A; enters the waiting state
5. Session 2 executes update T1 set level = 1 where name = A;, deadlock is generated, rolled back, and transaction 1 executes successfully

Detailed lock status changes are as follows

t1 t2 Remarks
INSERT IGNORE INTO T1 Successfully Achieves uk’s S-Lock DB_SUCCESS
INSERT IGNORE INTO T2 Successfully Achieves uk’s S-Lock DB_SUCCESS
UPDATE T1 tried to get uk’s X lock, but failed, and was in a waiting state DB_LOCK_WAIT
UPDATE T2 tries to get the X lock of UK and finds that the deadlock produces 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 get the X lock on the unique index of uk_name (record lock without 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 S locks on uk_name unique index (shared locks)


*** (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 get the X lock on the unique index of uk_name (record lock without gap lock)
This is consistent with previous theoretical inferences.

The above is the whole content of this article. I hope it will be helpful to everyone’s study, and I hope you will support developpaer more.