线上Mysql死锁问题分析和解决

益达的小当铺 08-02 198

前言：

今天朋友们对“mysql外键约束失败怎么解决”大体比较关怀，兄弟们都想要了解一些“mysql外键约束失败怎么解决”的相关文章。那么小编在网摘上网罗了一些关于“mysql外键约束失败怎么解决””的相关知识，希望姐妹们能喜欢，小伙伴们一起来学习一下吧！

之前对于Mysql死锁的认知仅仅停留在理论阶段，也很少有机会能在实际工作中遇到死锁的情况（偶发事件）。本文首先带大家回顾一下InnoDB事务隔离级别，不同类型的锁以及他们之间的关系，然后通过分析死锁日志结合生产环境的上下文来分析死锁产生的原因，最后阐述如何解决死锁问题。

InnoDB的四种事务隔离级别

事务隔离级别

脏读

不可重复读

幻读

读未提交（read uncommitted）

未解决

读已提交（read committed）

解决

未解决

可重复读（repeatable read）

解决

串行化（serializable）

解决

锁的三种实现Record Lock: 单个行记录上的锁。Gap Lock: 间隙锁，锁定一个范围，但不包括记录本身。Next-Key Lock: Gap Lock + Record Lock,锁定一个范围，并且锁住记录本身。

Next-Key Lock：如果索引有10，11，13，20四个值，可能被Next-Key Lock锁定的区间为：

(-\infty, 10],(10, 11], (11, 13], (13,+\infty)(−∞,10],(10,11],(11,13],(13,+∞)

Next-Key Lock优化：当查询的索引是唯一索引的情况下，InnoDB存储引擎会对Next-Key Lock进行优化，将其降级为Record Lock, 即仅锁住索引本身。而不是范围。我们来看下面的例子。

CREATE TABLE z ( a INT, b INT, PRIMARY KEY(a), KEY(b) );INSERT INTO z SELECT 3,1;INSERT INTO z SELECT 5,3;INSERT INTO z SELECT 7,6;INSERT INTO z SELECT 10,8;

表z中a列为聚集索引，b列为辅组索引，且当前的事务隔离级别为InnoDB默认的REPEATABLE READ。若在会话A中执行如下sql语句。

SELECT * FROM z WHERE b=3 FOR UPDATE

FOR UPDATE用于显式地对数据库读取操作加上X锁。由于通过b列进行查找，并且存在两个索引，查找过程需要分别进行加锁。对于聚集索引，仅对列a中等于5的索引加上了Record Lock。而对于辅助索引，使用了Next-Key Lock锁定了范围(1,3]，需要要特别注意的是，InnoDB存储引擎还会对辅助索引下一个键值加上Gap Lock, 即锁定范围(3, 6)。因此当此时会话B中运行如下sql语句时，都会被阻塞：

SELECT * FROM z WHERE a=5 LOCK IN SHARE MODE // 聚集索引中列a=5的值上存在X锁，因此请求S锁阻塞INSERT INTO z SELECT 4,2  //辅助索引的(1,3]上存在Next-Key Lock，因此b列中插入2的操作被阻塞INSERT INTO z SELECT 6,5  //辅助索引的(3,6)上存在Gap Lock，因此b列中插入5的操作被阻塞

事务隔离级别与锁算法READ UNCOMMITTED——可以读到其他事务未提交的修改READ COMMITTED——除了唯一性约束检查以及外键约束检查需要gap lock外，InnoDB只会使用Record LockREPEATABLE READ——使用Next-Key LockSERIALIZABLE——对每个SELECT语句后自动加上LOCK IN SHARE MODE，对一致性的非锁定读不再支持一致性非锁定读（Multi Version Concurrency Control, MVCC）

为了提升并发性能，InnoDB存储引擎在不显示加锁的情况下的读取操作都是不加锁的。即如果读取的行正在执行DELETE或UPDATE操作（即该行上被加上了X锁），这时读取操作不会去等待行上锁的释放，而是去读取行的一个快照信息，即该行上之前版本的数据，该实现是通过undo段来实现的。对于不同的事务隔离级别，读取方式也不同，需要注意的是并不是每个事务隔离级别下都支持一致性非锁定读。

READ COMMITTED——总是读取最新的快照信息。读取最新的版本，会产生幻读。REPEATABLE READ——读取事务开始时的快照信息，由于总是读取事务开始时的快照版本，因此不会产生幻读。

假如parent表中有一行id=1的数据。

时间

会话A

会话B

BEGIN

UPDATE parent SET id=3 WHERE id=1;

SELECT*FROM parent WHERE id=1;

COMMIT;

SELECT*FROM parent WHERE id=1;

COMMIT

情况1：当前的事务级别为READ COMMITTED。当会话A在时间点4访问id=1这一行的数据，由于会话B的更新操作还未提交，所以会话A从快照信息中读取到的是id=1；在时间点6时，由于会话B的事务已提交，因此更新了快照信息，因此当会话A在时间点6访问最新的快照信息时已经没有id=1的数据了，所以返回Empty。（出现了不可重复读问题）情况2：当前的事务级别为REPEATABLE READ。由于该事务隔离级别下总是访问事务开始时的快照版本，即时间点1的快照信息版本，所以无论会话B做出怎样的修改，会话A中的事务总是读取时间点1时（事务开始时）的快照信息，即id=1。（不会出现幻读问题和不可重复读）一致性锁定读

显式地为读取操作加锁：

SELECT...FOR UPDATE——加X锁（互斥锁）SELECT...LOCK IN SHARE MODE——加S锁（共享锁）

需要注意的点：X锁和X锁以及X锁和S锁是不兼容的，只有S锁和S锁是兼容的。

什么是幻读

在同一个事务下，连续执行两次同样的SQL语句可能导致不同的结果，即第二次的SQL语句可能会返回之前不存在的行。

如何解决幻读问题

innoDB存储引擎通过Next-Key Lock来解决幻读问题。假如表t中有a=2和a=5的行记录。

时间

会话A

会话B

BEGIN

SELECT * FROM t WHERE a>2 FOR UPDATE

BEGIN

INSERT INTO t SELECT 4;

COMMIT

SELECT * FROM t WHERE a>2 FOR UPDATE

COMMIT

情况1：当前的事务级别为READ COMMITTED，即使用的Record Lock，会话A中事务会将a=5这一行上X锁，并返回a=5的行记录，但这并不影响会话B中的事务插入a=4的行记录的操作，所以会话A在时间点6执行同样的读取操作时返回的是a=2和a=4的行记录。（出现了幻读问题）。情况2：当前的事务级别为REPEATABLE READ，会话A会使用Next-Key Lock对（2，+\infty）（2，+∞）这个范围加上了gap锁，因此会话B在时间点4对于这个范围的任何操作都会阻塞。（不会出现幻读问题）生产环境死锁解析与解决方案死锁日志

------------------------LATEST DETECTED DEADLOCK------------------------210816  9:20:19*** (1) TRANSACTION:TRANSACTION 191823DD3, ACTIVE 2 sec insertingmysql tables in use 1, locked 1LOCK WAIT 10 lock struct(s), heap size 3112, 7 row lock(s), undo log entries 3MySQL thread id 40912941, OS thread handle 0x7f858c8cf700, query id 6328575165 10.200.210.25 yanxuan_as updateinsert into TB_AS_EXPRESS_EVENT (applyId,carrierCode,trackingNum,eventTime,eventCode,valid,createTime,updateTime) values ('14688841','shunfeng','279310920959',0,40,1,1629076817147,1629076817147)*** (1) WAITING FOR THIS LOCK TO BE GRANTED:RECORD LOCKS space id 138 page no 10089 n bits 536 index `IDX_APPLYID` of table `yanxuan_as`.`TB_AS_EXPRESS_EVENT` trx id 191823DD3 **lock_mode X locks gap before rec insert intention waiting**Record lock, heap no 377 PHYSICAL RECORD: n_fields 2; compact format; info bits 0 0: len 8; hex 3134363838383630; asc 14688860;; 1: len 8; hex 00000000000ce834; asc        4;;*** (2) TRANSACTION:TRANSACTION 191823A55, ACTIVE 4 sec insertingmysql tables in use 1, locked 111 lock struct(s), heap size 3112, 8 row lock(s), undo log entries 3MySQL thread id 40953624, OS thread handle 0x7f858efa8700, query id 6328574135 10.200.210.25 yanxuan_as updateinsert into TB_AS_EXPRESS_EVENT (applyId,carrierCode,trackingNum,eventTime,eventCode,valid,createTime,updateTime) values ('14688843','shunfeng','194333982727',0,40,1,1629076815102,1629076815102)*** (2) HOLDS THE LOCK(S):RECORD LOCKS space id 138 page no 10089 n bits 536 index `IDX_APPLYID` of table `yanxuan_as`.`TB_AS_EXPRESS_EVENT` trx id 191823A55 **lock_mode X locks gap before rec**Record lock, heap no 377 PHYSICAL RECORD: n_fields 2; compact format; info bits 0 0: len 8; hex 3134363838383630; asc 14688860;; 1: len 8; hex 00000000000ce834; asc        4;;*** (2) WAITING FOR THIS LOCK TO BE GRANTED:RECORD LOCKS space id 138 page no 10089 n bits 536 index `IDX_APPLYID` of table `yanxuan_as`.`TB_AS_EXPRESS_EVENT` trx id 191823A55 **lock_mode X locks gap before rec insert intention waiting**Record lock, heap no 377 PHYSICAL RECORD: n_fields 2; compact format; info bits 0 0: len 8; hex 3134363838383630; asc 14688860;; 1: len 8; hex 00000000000ce834; asc        4;;*** WE ROLL BACK TRANSACTION (1)

日志解析

事务1：

hold：未知

waitting：等待获取某个插入意向间隙锁（lock_mode X locks gap before rec insert intention waiting）

事务2：

hold：持有某个间隙锁（lock_mode X locks gap before rec）

waitting：等待获取某个插入意向间隙锁（lock_mode X locks gap before rec insert intention waiting）

日志信息中并没有展示事务1持有的锁，但是可以肯定的是，事务1一定持有某个事务2等待的锁，才会循环等待并产生死锁，下面我们来结合生产环境代码来进一步分析产生死锁的原因。

出现死锁的代码

public void invalidOldAndInsertCareOfDeliveredEvent(ApplyBean applyBean) {        try {            //失效            expressEventDao.invalidExpressEventToClose(applyBean.getApplyId());// 将applyId对应的数据更新为失效            //插入签收            insertApplyTrackingEventCareOfDeliveredEvent(applyBean);// 插入applyId对应的数据        } catch (Exception e) {            logger.error("[op:invalidOldAndInsertCareOfCollectEvent] " +                "applyId={}, e", applyBean.getApplyId(), e);        }    }

复现出现死锁问题时的数据结合代码复现死锁产生过程

注意1：字段applyId上存在普通索引（非聚集索引）

注意2：间隙锁只阻止其他事务插入到间隙中，他们不阻止其他事务在同一个间隙上获得间隙锁。

时间线

事务1

事务2

begin

UPDATE TB_AS_EXPRESS_EVENT SET valid=-1, updateTime=1629076815102 WHERE applyId="14688843" // 得到间隙锁（14688835, 14688860）

UPDATE TB_AS_EXPRESS_EVENT SET valid=-1, updateTime=1629076817147 WHERE applyId="14688841";// 得到间隙锁（14688835, 14688860）

INSERT INTO TB_AS_EXPRESS_EVENT (applyId,carrierCode,trackingNum,eventTime,eventCode,valid,createTime,updateTime) VALUES ('14688843','shunfeng','194333982727',0,40,1,1629076815102,1629076815102) // 尝试获取插入意向间隙锁（14688835, 14688860）失败

INSERT INTO TB_AS_EXPRESS_EVENT (applyId,carrierCode,trackingNum,eventTime,eventCode,valid,createTime,updateTime) VALUES ('14688841','shunfeng','279310920959',0,40,1,1629076817147,1629076817147)// 尝试获取插入意向间隙锁（14688835, 14688860）失败

等待

检查到死锁并回滚当前事务

成功获取间隙锁（14688835, 14688860）并执行成功

commit

时间1：事务2更新applyId="14688843" 的数据，由于字段applyId上存在普通索引，索引事务2得到Next-Key Lock，即(14688835，14688843]和(14688843, 14688860)，因为applyId="14688843"的数据不存在，所以事务2获得间隙锁(14688835，14688860）。时间2：事务1更新applyId="14688841" 的数据，由于字段applyId上存在普通索引，索引事务1得到Next-Key Lock，即(14688835，14688841]和(14688843, 14688860)，因为applyId="14688841"的数据不存在，所以事务2获得间隙锁(14688835，14688860）。时间3：事务2插入applyId="14688843" 的数据，尝试获取(14688835，14688860)范围的插入意向间隙锁，由于事务1持有该间隙锁，因此需要等待事务1释放该间隙锁。时间4：事务1插入applyId="14688841" 的数据，尝试获取(14688835，14688860)范围的插入意向间隙锁，由于事务2持有该间隙锁，因此需要等待事务2释放该间隙锁。时间5：InnoDB检测到死锁后，使事务1释放持有的间隙锁(14688835，14688860）并回滚，事务2成功获取到间隙锁(14688835，14688860）并执行成功。解决方案

更新之前先用select语句搜索更新的数据是否存在，如果不存在，则直接插入。如果存在，则用主键id作为更新时的查询条件，因为主键id上的索引是聚集索引，因此innoDB会将Next-Key Lock降级为行锁，将锁的粒度降到最低，这样就能最大程度降低产生死锁的概率了。

本文地址：http://www.longkongtuishu.com/ca046BARsAVYBC1Y.html

标签： #mysql外键约束失败怎么解决