MySQL死锁的产生和解决方法?

本文总结整理转载自：
https://blog.csdn.net/qq_34107571/article/details/78001309
https://blog.csdn.net/qq_16681169/article/details/74784193
https://blog.csdn.net/qq_16681169/article/details/73359670

什么时死锁？

死锁是2+个线程在执行过程中， 因争夺资源而造成的相互等待的现象，若无外力作用，它们将无法推进下去。

死锁产生的4个必要条件

互斥条件

指进程对所分配的资源进行排他性使用，即一段时间内某资源只有一个进程占用，其他的进程请求资源只能等待，直至被占有资源的进程得到释放。

请求和保留条件

指进程至少保持占用一个资源，但又提出新的资源请求，而该资源正被其他进程占用，此时请求进程阻塞，但对以获得的其他资源保持不放。

不剥夺条件

指进程已获得的资源，在未使用完之前，不能剥夺，只能使用完时由自己释放。

环路等待条件

值发生死锁时，必然存在一个进程占用资源的环形链，即进程集合（P0，P1，P2, … Pn），P0等待P1资源释放，P1等待P2资源释放，P3等待 … Pn等待P0资源释放。

如何处理死锁

1）锁模式

1. 共享锁（S）

由读操作创建的锁，防止在读取数据的过程中，其它事务对数据进行更新；其它事务可以并发读取数据。共享锁可以加在表、页、索引键或者数据行上。在SQL SERVER默认隔离级别下数据读取完毕后就会释放共享锁，但可以通过锁提示或设置更高的事务隔离级别改变共享锁的释放时间。

2. 独占锁/排它锁（X）

对资源独占的锁，一个进程独占地锁定了请求的数据源，那么别的进程无法在此数据源上获得任何类型的锁。独占锁一致持有到事务结束。

3. 更新锁（U）

更新锁实际上并不是一种独立的锁，而是共享锁与独占锁的混合。当SQL SERVER执行数据修改操作却首先需要搜索表以找到需要修改的资源时，会获得更新锁。

更新锁与共享锁兼容，但只有一个进程可以获取当前数据源上的更新锁，

其它进程无法获取该资源的更新锁或独占锁，更新锁的作用就好像一个序列化阀门（serialization gate），将后续申请独占锁的请求压入队列中。持有更新锁的进程能够将其转换成该资源上的独占锁。更新锁不足以用于更新数据—实际的数据修改仍需要用到独占锁。对于独占锁的序列化访问可以避免转换死锁的发生，更新锁会保留到事务结束或者当它们转换成独占锁时为止。

3）如何解决死锁

针对如上死锁案例，分析其对应语句执行计划如下：

通过执行计划可以看出，在查找需要更新的数据时使用的是索引扫描，比较耗费性能，这样就造成锁定资源时间过长，增加了语句并发执行时产生死锁的概率。

处理方式：

1． 在表上建立一个聚集索引。

2． 对语句更新的相关字段建立包含索引。

优化后该语句执行计划如下：

优化后的执行计划使用了索引查找，将大幅提升该查询语句的性能，降低了锁定资源的时间，同时也减少了锁定资源的范围，这样就降低了锁资源循环等待事件发生的概率，对于预防死锁的发生会有一定的作用。

死锁是无法完全避免的，但如果应用程序适当处理死锁，对涉及的任何用户及系统其余部分的影响可降至最低（适当处理是指发生错误1205时，应用程序重新提交批处理，第二次尝试大多能成功。一个进程被杀死，它的事务被取消，它的锁被释放，死锁中涉及到的另一个进程就可以完成它的工作并释放锁，所以就不具备产生另一个死锁的条件了。）

如何预防死锁

阻止死锁的途径就是避免满足死锁条件的情况发生，为此我们在开发的过程中需要遵循如下原则：

1.尽量避免并发的执行涉及到修改数据的语句。

2.要求每一个事务一次就将所有要使用到的数据全部加锁，否则就不允许执行。

3.预先规定一个加锁顺序，所有的事务都必须按照这个顺序对数据执行封锁。如不同的过程在事务内部对对象的更新执行顺序应尽量保证一致。

4.每个事务的执行时间不可太长，对程序段的事务可考虑将其分割为几个事务。在事务中不要求输入，应该在事务之前得到输入，然后快速执行事务。

5.使用尽可能低的隔离级别。

6.数据存储空间离散法。该方法是指采用各种手段，将逻辑上在一个表中的数据分散的若干离散的空间上去，以便改善对表的访问性能。主要通过将大表按行或者列分解为若干小表，或者按照不同的用户群两种方法实现。

7.编写应用程序，让进程持有锁的时间尽可能短，这样其它进程就不必花太长的时间等待锁被释放。

8.保持事务简短并在一个批处理中

死锁案例

案例一：事物之间对资源访问顺序的交替

1. 出现原因：A用户问A资源锁住A时请求B资源，B用户问B资源锁住B时请求A资源，产生死锁。

一个用户A 访问表A（锁住了表A），然后又访问表B；另一个用户B 访问表B（锁住了表B），然后企图访问表A；这时用户A由于用户B已经锁住表B，它必须等待用户B释放表B才能继续，同样用户B要等用户A释放表A才能继续，这就死锁就产生了。

2. 解决方法：多用户操作多表资源时，按照相同资源访问顺序进行处理。

这种死锁比较常见，是由于程序的BUG产生的，除了调整的程序的逻辑没有其它的办法。仔细分析程序的逻辑，对于数据库的多表操作时，尽量按照相同的顺序进行处理，尽量避免同时锁定两个资源，如操作A和B两张表时，总是按先A后B的顺序处理， 必须同时锁定两个资源时，要保证在任何时刻都应该按照相同的顺序来锁定资源。

案例二：并发修改同一记录

1. 出现原因：用户A拿到共享锁访问资源A时企图修改资源A，此时用户B拿到独占锁修改资源A时企图访问B

用户A查询一条纪录，然后修改该条纪录；这时用户B修改该条纪录，这时用户A的事务里锁的性质由查询的共享锁企图上升到独占锁，而用户B里的独占锁由于A有共享锁存在所以必须等A释放掉共享锁，而A由于B的独占锁而无法上升的独占锁也就不可能释放共享锁，于是出现了死锁。这种死锁由于比较隐蔽，但在稍大点的项目中经常发生。
　一般更新模式由一个事务组成，此事务读取记录，获取资源（页或行）的共享 (S) 锁，然后修改行，此操作要求锁转换为排它 (X) 锁。如果两个事务获得了资源上的共享模式锁，然后试图同时更新数据，则一个事务尝试将锁转换为排它 (X) 锁。共享模式到排它锁的转换必须等待一段时间，因为一个事务的排它锁与其它事务的共享模式锁不兼容；发生锁等待。第二个事务试图获取排它 (X) 锁以进行更新。由于两个事务都要转换为排它 (X) 锁，并且每个事务都等待另一个事务释放共享模式锁，因此发生死锁。

2. 解决方法：

a. 使用乐观锁进行控制。

乐观锁大多是基于数据版本（Version）记录机制实现。即为数据增加一个版本标识，在基于数据库表的版本解决方案中，一般是通过为数据库表增加一个“version”字段来实现。读取出数据时，将此版本号一同读出，之后更新时，对此版本号加一。此时，将提交数据的版本数据与数据库表对应记录的当前版本信息进行比对，如果提交的数据版本号大于数据库表当前版本号，则予以更新，否则认为是过期数据。乐观锁机制避免了长事务中的数据库加锁开销（用户A和用户B操作过程中，都没有对数据库数据加锁），大大提升了大并发量下的系统整体性能表现。Hibernate 在其数据访问引擎中内置了乐观锁实现。需要注意的是，由于乐观锁机制是在我们的系统中实现，来自外部系统的用户更新操作不受我们系统的控制，因此可能会造成脏数据被更新到数据库中。

b. 使用悲观锁进行控制。

悲观锁大多数情况下依靠数据库的锁机制实现，如Oracle的Select … for update语句，以保证操作最大程度的独占性。但随之而来的就是数据库性能的大量开销，特别是对长事务而言，这样的开销往往无法承受。如一个金融系统，当某个操作员读取用户的数据，并在读出的用户数据的基础上进行修改时（如更改用户账户余额），如果采用悲观锁机制，也就意味着整个操作过程中（从操作员读出数据、开始修改直至提交修改结果的全过程，甚至还包括操作员中途去煮咖啡的时间），数据库记录始终处于加锁状态，可以想见，如果面对成百上千个并发，这样的情况将导致灾难性的后果。所以，采用悲观锁进行控制时一定要考虑清楚。

c. SqlServer可支持更新锁。

为解决死锁，SqlServer引入更新锁,它有如下特征：

(1) 加锁的条件：当一个事务执行update语句时，数据库系统会先为事务分配一把更新锁。

(2) 解锁的条件：当读取数据完毕，执行更新操作时，会把更新锁升级为独占锁。

(3) 与其他锁的兼容性：更新锁与共享锁是兼容的，也就是说，一个资源可以同时放置更新锁和共享锁，但是最多放置一把更新锁。这样，当多个事务更新相同的数据时，只有一个事务能获得更新锁，然后再把更新锁升级为独占锁，其他事务必须等到前一个事务结束后，才能获取得更新锁，这就避免了死锁。

(4) 并发性能：允许多个事务同时读锁定的资源，但不允许其他事务修改它。
例子如下:

T1:
begin tran
select * from table(updlock) (加更新锁)
update table set column1='hello'
T2:
begin tran
select * from table(updlock)
update table set column1='world'

更新锁的意思是：“我现在只想读，你们别人也可以读，但我将来可能会做更新操作，我已经获取了从共享锁（用来读）到排他锁（用来更新）的资格”。一个事物只能有一个更新锁获此资格。
T1执行select，加更新锁。
T2运行，准备加更新锁，但发现已经有一个更新锁在那儿了，只好等。
当后来有user3、user4…需要查询table表中的数据时，并不会因为T1的select在执行就被阻塞，照样能查询,提高了效率。

案例三：索引不当导致全表扫描

1. 出现原因：

如果在事务中执行了一条不满足条件的语句，执行全表扫描，把行级锁上升为表级锁，多个这样的事务执行后，就很容易产生死锁和阻塞。类似的情况还有当表中的数据量非常庞大而索引建的过少或不合适的时候，使得经常发生全表扫描，最终应用系统会越来越慢，最终发生阻塞或死锁。

2. 解决方法：

SQL语句中不要使用太复杂的关联多表的查询；使用“执行计划”对SQL语句进行分析，对于有全表扫描的SQL语句，建立相应的索引进行优化。

案例四：事务封锁范围大且相互等待

一.业务背景

我们现在的业务是一款数据产品，有不少实时计算和爬取来的数据都汇总到大数据仓库、数据挖掘平台ODPS上。然后应用在读取这些数据时，这些数据会先导入到并发读能力更强，适合结构查询的mysql上。数据端开发的同学在跑定时任务时, tps比较高，于是出现了一些线上问题:在开发过程中发现对某一包含unique key(联合的唯一索引)的表进行并发插入的时候，出现大量的死锁，使得插入几乎无法进行。于是为了排查问题，请教了DBA以及数据库事业部的同学，最后发现了问题的所在，特此记录下来

二.死锁现场

1.表结构

CREATE TABLE tkn_tb_cinema_show_data (
……
cinema_id bigint(20) DEFAULT NULL COMMENT ‘影院ID’,
show_id bigint(20) DEFAULT NULL COMMENT ‘影片ID’,
now_date varchar(32) DEFAULT NULL COMMENT ‘当日时间’,
……
PRIMARY KEY (id),
UNIQUE KEY uid_cinema_show_date (cinema_id,show_id,now_date),
……
) ENGINE=InnoDB AUTO_INCREMENT=2162973490 DEFAULT CHARSET=utf8 COMMENT=’淘宝电影订单影院影片数据统计’

2.问题状况

可以看到出现死锁的原因是因为批量插入的时候,该事务
持有锁
index uid_cinema_show_date of table tkn.tkn_tb_cinema_show_data trx id 73278630826 lock_mode X
等待锁
index uid_cinema_show_date of table tkn.tkn_tb_cinema_show_data trx id 73278630826 lock_mode X locks gap before rec insert intention waiting
这样一看确实奇怪，怎么批量插入不同行怎么会有死锁，再看看死锁日志

（SHOW ENGINE INNODB STATUS;）

transactions deadlock detected, dumping detailed information.
2017-06-11 08:41:03 2ac742684700
*** (1) TRANSACTION:
TRANSACTION 73278630816, ACTIVE 1 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 622 lock struct(s), heap size 79400, 743 row lock(s), undo log entries 388
MySQL thread id 13824253, OS thread handle 0x2ac195786700, query id 53621728233 11.227.64.76 dwexp update
INSERT INTO tkn_tb_cinema_show_data ......
*** (1) HOLDS THE LOCK(S):
RECORD LOCKS space id 29500 page no 398903 n bits 336 index `uid_cinema_show_date` of table `tkn`.`tkn_tb_cinema_show_data` trx id 73278630816 lock_mode X
Record lock, heap no 253 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
0: len 8; hex 80000000000035cb; asc 5 ;;
1: len 8; hex 80000000000356f1; asc V ;;
2: len 10; hex 323031372d30362d3138; asc 2017-06-18;;
3: len 8; hex 80000000807f52b2; asc R ;;

*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 29500 page no 423377 n bits 304 index `uid_cinema_show_date` of table `tkn`.`tkn_tb_cinema_show_data` trx id 73278630816 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 228 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
0: len 8; hex 8000000000006262; asc bb;;
1: len 8; hex 8000000000035911; asc Y ;;
2: len 10; hex 323031372d30362d3138; asc 2017-06-18;;
3: len 8; hex 80000000807fdae4; asc ;;

*** (2) TRANSACTION:
TRANSACTION 73278630826, ACTIVE 1 sec inserting, thread declared inside InnoDB 4836
mysql tables in use 1, locked 1
2425 lock struct(s), heap size 292392, 3363 row lock(s), undo log entries 1804
MySQL thread id 13824252, OS thread handle 0x2ac742684700, query id 53621728249 11.227.64.76 dwexp update
INSERT INTO tkn_tb_cinema_show_data ......
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 29500 page no 423377 n bits 304 index `uid_cinema_show_date` of table `tkn`.`tkn_tb_cinema_show_data` trx id 73278630826 lock_mode X
Record lock, heap no 228 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
0: len 8; hex 8000000000006262; asc bb;;
1: len 8; hex 8000000000035911; asc Y ;;
2: len 10; hex 323031372d30362d3138; asc 2017-06-18;;
3: len 8; hex 80000000807fdae4; asc ;;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 29500 page no 398903 n bits 336 index `uid_cinema_show_date` of table `tkn`.`tkn_tb_cinema_show_data` trx id 73278630826 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 253 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
0: len 8; hex 80000000000035cb; asc 5 ;;
1: len 8; hex 80000000000356f1; asc V ;;
2: len 10; hex 323031372d30362d3138; asc 2017-06-18;;
3: len 8; hex 80000000807f52b2; asc R ;;

*** WE ROLL BACK TRANSACTION (1)

三.分析问题

1.阅读死锁日志

从日志中可以看到两个事务的持锁情况和等待锁情况：

# a.事务一
HOLDS THE LOCK(S) …… lock_mode X 持有X锁
WAITING FOR THIS LOCK TO BE GRANTED …… X locks gap before rec insert intention waiting 等待insert intention lock
# b.事务二
HOLDS THE LOCK(S) …… lock_mode X 持有X锁
WAITING FOR THIS LOCK TO BE GRANTED …… X locks gap before rec insert intention waiting 等待insert intention lock

2.补充关于一些锁方面的知识

• 当InnoDB在判断行锁是否冲突的时候，除了最基本的IS IX S X锁的冲突判断意外，还有一套更精确的判断逻辑。除了上面说到的锁类型，InnoDB还将锁细分为如下几种子类型：

  • record lock（RK）
    锁直接加在索引记录上面，锁住的是key

  • gap lock（GK）
    间隙锁，锁定一个范围，但不包括记录本身。GAP锁的目的，是为了防止同一事务的两次当前读，出现幻读的情况

  • next key lock（NK）
    行锁和间隙锁组合起来就叫Next-Key Lock

  • insert intention lock（IK）
    如果插入前，该间隙已经由gap锁，那么Insert会申请插入意向锁。因为了避免幻读，当其他事务持有该间隙的间隔锁，插入意向锁就会被阻塞（不用直接用gap锁，是因为gap锁不互斥）。
    下面画的就是“精确模式”锁兼容矩阵

    列相加\行已有	RK	GK	IK	NK
    RK	0	1	1	0
    GK	1	1	1	1
    IK	1	0	1	0

• insert中对唯一索引的加锁逻辑

  • 先做UK冲突检测，如果存在目标行，先对目标行加S NK（S lock中的next key lock，下同），这个锁如果最终插入成功（该记录在等待期间被其他事务删除，此锁被同时删除）
  • 如果1成功，对对应行加X IK
  • 如果2成功，插入记录，并对记录加X RK（有可能是隐式锁）

3.锁的细节

1. 前文已分析，一个insert SQL需要加的锁依次为 S NK, X IK, X RK、那么加XIK前需要GK或NK。而insert不需要加GK，因此两个事务X IK被申请等待的原因是在申请S NK的过程受到阻塞了。
2. insert完成之后，只会残留X RK锁，这就是两个事务都有X RK的原因，说明它们刚插入完某几条记录。
3. 由1,2可以推测，死锁是事务1 的S NK被事务2的 X RK所阻塞，说明事务2插入的记录在事务1 S NK的范围内。而事务2的 S NK被 事务1 阻塞的申请S NK给阻塞，说明事务1 S NK的范围要大于事务2 S NK的范围。
4. 由第3点推断，可以证明出事务2所有的记录范围 REC2 是要在 事务1所有的记录范围 REC1之后的,既REC2 < REC1
   而插入的业务场景的数据是:

# 事务1
('10076','150686','2017-06-11 08:39:15.866') ,
('10111','150686','2017-06-11 08:39:15.866') ,
('10133','214563','2017-06-11 08:39:15.866') ,
('10171','150686','2017-06-11 08:39:15.866')

# 事务2
('15186','150686','2017-06-11 08:39:15.866') ,
('15186','151509','2017-06-11 08:39:15.866') ,
('15186','207522','2017-06-11 08:39:15.866') ,
('15187','151509','2017-06-11 08:39:15.866')

实际的插入数据符合我们的预期
5.由上面的结论，我们可以得到一张死锁循环图

实际的插入数据符合我们的预期
5.由上面的结论，我们可以得到一张死锁循环图

四.预防死锁

死锁发生的条件：

1、资源不能共享，需要只能由一个进程或者线程使用

2、请求且保持，已经锁定的资源自给保持着不释放

3、不剥夺，自给申请到的资源不能被别人剥夺

4、循环等待
防止死锁的途径就是避免满足死锁条件的情况发生，适合这个问题解决的方案有:

1、保持事务简短并在一个批处理中

在同一数据库中并发执行多个需要长时间运行的事务时通常发生死锁。事务运行时间越长，其持有排它锁或更新锁的时间也就越长，从而堵塞了其它活动并可能导致死锁。保持事务在一个批处理中，可以最小化事务的网络通信往返量，减少完成事务可能的延迟并释放锁。

2、使用低隔离级别

确定事务是否能在更低的隔离级别上运行。执行提交读允许事务读取另一个事务已读取（未修改）的数据，而不必等待第一个事务完成。使用较低的隔离级别（例如提交读）而不使用较高的隔离级别（例如可串行读）可以缩短持有共享锁的时间，从而降低了锁定争夺（比如这次的S NK和X IK 是InnoDB引擎Repeatable Read级别才有的）。