MySQL中多并发更新单条记录引发的死锁分析_多线程更新数据库一条记录

锁机制是数据库系统保障数据的完整性和一致性的重要技术，各数据库存储引擎在实现机制上虽各有不同，但整体实现上是有异曲同工之妙。本文首先介绍mysql中锁类型以及死锁检测机制，并结合多并发场景下更新单条记录的死锁分析进行分析讨论，以了解背后的处理机制。

---

1、MySQL中的锁类型

数据库系统使用锁机制用于管理对共享资源的并发访问，提供数据的完整性和一致性。InnoDB存储引擎会在行级别对表数据上锁，同时也会在数据库内部比如缓冲池资源LRU进行加锁以保证一致性。

1.1 Lock和Latch

在数据库中，lock和latch都可以称为锁，但是二者有着截然不同的含义：

• Latch：一般称为闩锁（轻量级的锁），其要求锁定的时间非常短，否则对应用的性能影响很大。在InnoDB存储引擎中，latch又可以分为mutex和rwlock，目的是用来保证并发线程操作临界资源的正确性，并且通常没有死锁检测机制
• Lock：lock的对象是事务，用来锁定数据库中的对象，包括表、页和行，一般lock资源在事务commit或rollback时候释放，并且lock是有死锁机制的

• InnoDB存储引擎中的latch，可通过命令show engine innodb mutex查看

MySQL [(none)]> show engine innodb mutex;
+--------+----------------+------------+
| Type   | Name           | Status     |
+--------+----------------+------------+
| InnoDB | log0log.cc:907 | os_waits=2 |
+--------+----------------+------------+
1 row in set (0.68 sec)
1
2
3
4
5
6
7

1.2 锁的类型

在InnoDB存储引擎中实现了以下类型的行锁：

• 共享锁（S）：允许一个事务去读一行，阻止其他事务获得相同数据集的排他锁。
• 排他锁（X）：允许获得排他锁的事务更新数据，阻止其他事务取得相同数据集的共享读锁和排他写锁。

另外，为了允许行锁和表锁共存，实现多粒度锁机制，InnoDB还有两种内部使用的意向锁（Intention Locks），这两种意向锁都是表锁。

• 意向共享锁（IS）：事务打算给数据行加行共享锁，事务在给一个数据行加共享锁前必须先取得该表的IS锁。
• 意向排他锁（IX）：事务打算给数据行加行排他锁，事务在给一个数据行加排他锁前必须先取得该表的IX锁。

上述锁模式的兼容情况具体如下表所示：

需要注意的是，意向锁是InnoDB自动加的，不需用户干预。对于UPDATE、DELETE和INSERT语句，InnoDB会自动给涉及数据集加排他锁（X)；对于普通SELECT语句，InnoDB不会加任何锁；事务可以通过以下语句显示给记录集加共享锁或排他锁：

共享锁（S）：SELECT * FROM table_name WHERE … LOCK IN SHARE MODE
排他锁（X）：SELECT * FROM table_name WHERE … FOR UPDATE
1
2

通过命令show engine innodb status;可以查看当前锁请求信息：

------------
TRANSACTIONS
------------
Trx id counter 20748
Purge done for trx's n:o < 20276 undo n:o < 0 state: running but idle
History list length 528
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 0, not started
MySQL thread id 3, OS thread handle 0x7fe8b2180700, query id 34 192.168.112.110 root init
show engine innodb status
---TRANSACTION 20747, ACTIVE 5 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 4, OS thread handle 0x7fe8b1f21700, query id 33 localhost root Sending data
select * from test.test01 lock in share mode
------- TRX HAS BEEN WAITING 5 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 8 page no 3 n bits 72 index `GEN_CLUST_INDEX` of table `test`.`test01` trx id 20747 lock mode S waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
 0: len 6; hex 000000000411; asc       ;;
 1: len 6; hex 000000001d76; asc      v;;
 2: len 7; hex c2000001760110; asc     v  ;;
 3: len 8; hex 7a68616e6773616e; asc zhangsan;;
 4: len 20; hex 323031372d31322d31392d32302e303020202020; asc 2017-12-19-20.00    ;;
 5: len 12; hex 757365723031202020202020; asc user01      ;;

------------------
---TRANSACTION 20746, ACTIVE 14 sec
2 lock struct(s), heap size 360, 5 row lock(s)
MySQL thread id 5, OS thread handle 0x7fe8b1edf700, query id 31 localhost root cleaning up
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29

1.3 行锁的三种算法

InnoDB存储引擎有三种行锁算法：

• Record Lock：单个行记录上锁
• Gap Lock：间隙锁，锁定一个范围，但不包含记录本身。GAP锁的目的，是为了防止同一事务的两次当前读，出现幻读的情况。该锁只会在隔离级别是RR或者以上的级别内存在。间隙锁的目的是为了让其他事务无法在间隙中新增数据
• Next-key Lock：Gap Lock + Record Lock，锁定一个范围，并且锁定记录本身。对于行的查询，都是采用该方法，主要目的是解决幻读的问题。next-key锁是InnoDB默认的锁

1.3.1 Next-key lock

Next-Key lock是结合了Gap Lock和Record Lock的锁机制，采用的是左开右闭规则，假如一个索引有10、11、13和20这四个值，那么该索引可能被Next-Key Locking的区间为：

(-∞,10]、(10,11]、(11,13]、(13,20]、(20,+∞]
1

以下为例，向表T1(name primary key,id key)插入id=10，因为next-key lock是左开右闭，id=6本身没有加锁、id=10本身加锁了，所以加锁区间为(6,10]

1.3.2 幻读问题

幻读问题是指在同一事务下，连续执行两次相同的SQL语句可能导致不同的结果，第二次执行的SQL语句可能返回之前不存在的行。在默认的事务隔离级别Repeatable READ下，InnoDB存储引擎采用Next-Key locking机制来避免幻读问题。在隔离级别READ Committed下，采用的是record lock，可以在应用层面指定share mode实现next-key lock机制：

SELECT * FROM table WHERE col=xxx LOCK IN SHARE MODE;
1

如果通过索引查询一个值，并对该行加上一个S-LOCK，即使查询的值不存在，锁定的也是一个范围。因此如果没有返回任何行，新插入的值一定是唯一的。如果在SELECT … LOCK IN SHARE MODE时候有多个并发操作会导致死锁，只有一个事务的插入操作会成功，其余的事务会抛出死锁的错误。如下所示：


1.4 死锁

死锁是指两个或两个以上的事务在执行过程过程中，因争夺锁资源而造成的一种互相等待的现象。解决死锁问题的最简单的一种方法是超时，即当两个事务互相等待时，当其中一个等待时间超过设定的阈值时会进行回滚，另一个等待的事务就能继续执行，在innodb存储引擎中，通过innodb_lock_wait_timeout参数设置超时时间。超时机制简单粗暴，但是如果超时的事务所占的权重较大，执行了很多更新操作，回滚将占用很长时间。除了超时机制，数据库还普遍采用waits-for graph的方式进行死锁检测，waits-for graph机制要求数据库保存两种信息：锁的信息链表和事务等待链表，通过上述链表构造出一张图，如果存在回路，则说明存在死锁。

如图所示，在事务等待列表中有4个事务T1/T2/T3/T4，事务T2对row1占用X-lock、事务T1对row2占用S-lock。事务T1需要等待事务T2中的row1资源，事务T2需要等待T1和T4占用的row2资源，最终waits-for graph图如上图所示。从图中可以发现存在回路(T1,T2)，表示存在死锁。waits-for graph是主动的死锁检测机制，在每个事务请求锁并发生等待时都会判断是否存在回路，若存在则有死锁，一般情况下InnoDB存储引擎会选择undo log最小的事务进行回滚。如果发生了死锁，可以使用show engine innodb status命令来确定最后一个死锁产生的原因。

2、多并发更新单条记录引起的死锁

上面介绍了InnoDB存储引擎中的锁类型以及死锁检测机制，下面来看下在实际开发过程中遇到的多并发更新单条记录引发的死锁问题。

2.1 场景重现

1）表结构

CREATE TABLE t2 (
 `a` int(11) NOT NULL DEFAULT 0,
 `b` int(11) DEFAULT NULL,
 `c` int(11) DEFAULT NULL,
 `d` int(11) DEFAULT NULL,
 PRIMARY KEY (`a`),
 UNIQUE KEY `uk_bc` (`b`,`c`)
);
1
2
3
4
5
6
7
8

2）并发执行三个session

2.2 死锁分析

2.2.1 死锁日志

上述场景出现的deadlock日志如下：

------------------------
LATEST DETECTED DEADLOCK
------------------------
2022-03-19 22:14:44 7f229eae0700
*** (1) TRANSACTION:
TRANSACTION 21771, ACTIVE 129 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 4 lock struct(s), heap size 1184, 2 row lock(s)
MySQL thread id 2, OS thread handle 0x7f229eb22700, query id 49 localhost root update
INSERT INTO t2 VALUES(123,22,12,11)
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 21 page no 3 n bits 72 index `PRIMARY` of table `test`.`t2` trx id 21771 lock_mode X insert intention waiting
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len 8; hex 73757072656d756d; asc supremum;;

*** (2) TRANSACTION:
TRANSACTION 21772, ACTIVE 112 sec inserting
mysql tables in use 1, locked 1
4 lock struct(s), heap size 1184, 2 row lock(s)
MySQL thread id 3, OS thread handle 0x7f229eae0700, query id 50 localhost root update
INSERT INTO t2 VALUES(123,22,12,11)
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 21 page no 3 n bits 72 index `PRIMARY` of table `test`.`t2` trx id 21772 lock mode S
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len 8; hex 73757072656d756d; asc supremum;;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 21 page no 3 n bits 72 index `PRIMARY` of table `test`.`t2` trx id 21772 lock_mode X insert intention waiting
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len 8; hex 73757072656d756d; asc supremum;;

*** WE ROLL BACK TRANSACTION (2)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32

• 事务21771想获取资源“space id 21 page no 3”上的lock_mode X insert intention waiting
• 事务21772已经拥有资源“space id 21 page no 3”上的S-lock，同时也想获得lock_mode X insert intention waiting

2.2.2 死锁分析

• 该场景中出现三个事务：事务1（21770）、事务2（21771）和事务3（21772），分别执行相同的操作，插入同一条记录
• 三个事务依次执行insert操作，由于(b,c)是唯一索引，所以后两个事务会出现唯一键冲突，但此时要注意的是事务一还没有提交，所以并不会立即报错，insert语句本来加的是隐式锁，在出现唯一键冲突时，事务1的隐式锁升级为显示锁(LOCK_REC_NOT_GAP->LOCK_REC->LOCK_X)。事务2和事务3为了判断是否出现唯一键冲突，必须进行一次当前读，加的锁是Next-key锁，所以进入锁等待（LOCK_REC_GAP->LOCK_REC->LOCK_S->LOCK_WAIT）

MySQL [test]> select * from information_schema.innodb_locks;
+--------------+-------------+-----------+-----------+-------------+------------+------------+-----------+----------+-----------+
| lock_id      | lock_trx_id | lock_mode | lock_type | lock_table  | lock_index | lock_space | lock_page | lock_rec | lock_data |
+--------------+-------------+-----------+-----------+-------------+------------+------------+-----------+----------+-----------+
| 21772:21:3:2 | 21772       | S         | RECORD    | `test`.`t2` | PRIMARY    |         21 |         3 |        2 | 123       |
| 21770:21:3:2 | 21770       | X         | RECORD    | `test`.`t2` | PRIMARY    |         21 |         3 |        2 | 123       |
| 21771:21:3:2 | 21771       | S         | RECORD    | `test`.`t2` | PRIMARY    |         21 |         3 |        2 | 123       |
+--------------+-------------+-----------+-----------+-------------+------------+------------+-----------+----------+-----------+
3 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9

• 事务1回滚，此时事务2和事务3成功获取记录上的S-LOCK（LOCK_REC_GAP->LOCK_REC->LOCK_S）
• 事务2和事务3继续执行插入操作，需要依次请求记录上的插入意向锁（LOCK_INSERT_INTENTION->LOCK_GAP->LOCK_X），插入意向锁和S锁冲突，所以事务2等待事务3，事务3等待事务2，形成死锁。

3、总结

多并发场景下更新单条记录引发的死锁问题，背后的处理机制是LOCK_INSERT_INTENTION和LOCK_S之间的出现的锁资源冲突。本文基于基哥分享的死锁场景和死锁分析过程总结，这是一个很有意思的死锁场景。

---

参考资料

1. 《MySQL技术内幕：InnoDB存储引擎》，姜承尧著
2. https://www.cnblogs.com/drizzle-xu/p/9713018.html
3. https://blog.csdn.net/cy973071263/article/details/105188519
4. https://breachdirectory.com/blog/mysql-deadlocks-101/

转载请注明原文地址：https://blog.csdn.net/solihawk/article/details/123676871
文章会同步在公众号“牧羊人的方向”更新，感兴趣的可以关注公众号，谢谢！