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

先自我介绍一下，小编浙江大学毕业，去过华为、字节跳动等大厂，目前阿里P7

深知大多数程序员，想要提升技能，往往是自己摸索成长，但自己不成体系的自学效果低效又漫长，而且极易碰到天花板技术停滞不前！

因此收集整理了一份《2024年最新Java开发全套学习资料》，初衷也很简单，就是希望能够帮助到想自学提升又不知道该从何学起的朋友。

既有适合小白学习的零基础资料，也有适合3年以上经验的小伙伴深入学习提升的进阶课程，涵盖了95%以上Java开发知识点，真正体系化！

由于文件比较多，这里只是将部分目录截图出来，全套包含大厂面经、学习笔记、源码讲义、实战项目、大纲路线、讲解视频，并且后续会持续更新

如果你需要这些资料，可以添加V获取：vip1024b （备注Java）

正文

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

通过命令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.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,+∞]

以下为例，向表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;

如果通过索引查询一个值，并对该行加上一个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)
);

2）并发执行三个session

序号	Session A	Session B	Session C
1	BEGIN;INSERT INTO t2 VALUES(123,22,12,11);
2		BEGIN;INSERT INTO t2 VALUES(123,22,12,11);
3			BEGIN;INSERT INTO t2 VALUES(123,22,12,11);
4	ROLLBACK;
5			DEADLOCK;

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)

• 事务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）

写在最后

作为一名即将求职的程序员，面对一个可能跟近些年非常不同的 2019 年，你的就业机会和风口会出现在哪里？在这种新环境下，工作应该选择大厂还是小公司？已有几年工作经验的老兵，又应该如何保持和提升自身竞争力，转被动为主动？

就目前大环境来看，跳槽成功的难度比往年高很多。一个明显的感受：今年的面试，无论一面还是二面，都很考验Java程序员的技术功底。

最近我整理了一份复习用的面试题及面试高频的考点题及技术点梳理成一份“Java经典面试问题（含答案解析）.pdf和一份网上搜集的“Java程序员面试笔试真题库.pdf”（实际上比预期多花了不少精力），包含分布式架构、高可扩展、高性能、高并发、Jvm性能调优、Spring，MyBatis，Nginx源码分析，Redis，ActiveMQ、Mycat、Netty、Kafka、Mysql、Zookeeper、Tomcat、Docker、Dubbo、Nginx等多个知识点高级进阶干货！

由于篇幅有限，为了方便大家观看，这里以图片的形式给大家展示部分的目录和答案截图！

Java经典面试问题（含答案解析）

阿里巴巴技术笔试心得

网上学习资料一大堆，但如果学到的知识不成体系，遇到问题时只是浅尝辄止，不再深入研究，那么很难做到真正的技术提升。

需要这份系统化的资料的朋友，可以添加V获取：vip1024b （备注Java）

一个人可以走的很快，但一群人才能走的更远！不论你是正从事IT行业的老鸟或是对IT行业感兴趣的新人，都欢迎加入我们的的圈子（技术交流、学习资源、职场吐槽、大厂内推、面试辅导），让我们一起学习成长！

习资料一大堆，但如果学到的知识不成体系，遇到问题时只是浅尝辄止，不再深入研究，那么很难做到真正的技术提升。**

需要这份系统化的资料的朋友，可以添加V获取：vip1024b （备注Java）
[外链图片转存中…(img-0KMthyfP-1713640908185)]

一个人可以走的很快，但一群人才能走的更远！不论你是正从事IT行业的老鸟或是对IT行业感兴趣的新人，都欢迎加入我们的的圈子（技术交流、学习资源、职场吐槽、大厂内推、面试辅导），让我们一起学习成长！