产生死锁的必要条件
多个并发事务 (2 个或者以上)
每个事物都持有了锁 (或者是已经在等待锁)
每个事务都需要再继续持有锁 (为了完成事务逻辑, 还必须更新更多的行)
事物之间产生加锁的循环等待, 形成死锁
常规锁模式
- LOCK_S(读锁, 共享锁)
- LOCK_X(写锁, 排它锁)
锁的属性
- LOCK _REC_NOT_GAP(锁记录)
- LOCK_GAP(锁记录前的 GAP)
- LOCK_ORDINARY(同时锁记录 + 记录前的 GAP,Next key 锁)
- LOCK_INSERT_INTETION(插入意向锁)
锁组合 (属性 + 模式)
可以任意组合
锁冲突矩阵
锁是加在那里的?
根据主键查找 - 锁加在主键上
如 begin;select * from tt_copy where id=4 for update;
加锁情况
index PRIMARY of table test.tt_copy trx id 1101588 lock_mode X locks rec but not gap
根据普通索引查找 - 锁加在普通索引和主键上
如 begin;select * from tt_copy force index(idx_a) where a=4 for update;
加锁情况
- index idx_a of table test.tt_copy trx id 1101590 lock_mode X locks rec but not gap
- index PRIMARY of table test.tt_copy trx id 1101590 lock_mode X locks rec but not gap
操作与加锁的对照关系
以下没特殊说明都为 RC 隔离级别
Insert
无 Unique key, 插入后 : 无论 RC 或 RR 隔离级别都是对主键加 LOCK_X+LOCK_REC_NOT_GAP
有 Unique key
插入前, 唯一约束检查: LOCK_S+LOCK_ORDINARY
插入前, 插入的位置有 GAP 锁: LOCK_INSERT_INTETION
插入后, 新数据插入: LOCK_X+LOCK_REC_NOT_GAP
Delete
满足删除条件的所有记录: LOCK_X+LOCK_REC_NOT_GAP
Update
Update 操作分解
Step 1: 定位到 下一条满足查询条件的记录 (查询过程, 类似于 Select/Delete)
Step 2: 删除当前定位到的记录 (标记为删除状态)
Step 3: 拼装更新后项, 根据更新后项定位到 新的插入位置
Step 4: 在新的插入位置, 判断是否存在 Unique 冲突 ( 存在 Unique Key 时)
Step 5: 插入更新后项 (不存在 Unique 冲突时)
Step 6: 重复 Step 1 到 Step 5 的操作, 直至扫描完整个查询范围
Update 操作分析
- Step 1,Step 2:Delete
- Step 3,Step 4,Step 5:Insert
- Update
无 Unique key:
查询范围中的所有记录, LOCK_X + LOCK_REC_NOT_GAP
有 Unique key:
查找满足条件的记录: 查询范围内的所有记录, LOCK_X + LOCK_REC_NOT_GAP
更新后项存在唯一性冲突: 冲突项上的加锁, LOCK_S + LOCK_ORDINARY
更新后项不存在唯一性冲突: 更新位置后项加锁, LOCK_S + LOCK_GAP (省略)
实际更新操作: 可看做插入了一条新纪录, LOCK_X + LOCK_REC_NOT_GAP
GAP 锁
那些操作会加 GAP 锁?
Read Committed (RC) ) :Unique Key 唯一约束检查; Purge 操作;
Repeatable Read (RC ):RC 的基础上, 所有需要加锁的索引范围扫描和索引查找 (Update/Delete...)
还有一种会加 GAP 锁: RR 隔离级别下, 对有唯一索引的表执行 insert on duplicate update 操作, 除了会对新插入的记录加 x not gap 外, 还会对相邻记录加 x gap
如何去掉 GAP 锁?
change the transaction isolation level to READ COMMITTED or enable the innodb_locks_unsafe_for_binlog system variable (which is now deprecated)
什么时候加 next-key lock?
- By default, InnoDB operates in REPEATABLE READ transaction isolation level. In this case, InnoDB uses next-key locks for searches and index scans, which prevents phantom rows
- Insert Intention Lock
- An insert intention lock is a type of gap lock set by INSERT operations prior to(在... 之前) row insertion.
总结
• 原则之一
要分析一个死锁, 必须深入业务, 了解整个事务的逻辑 (闭门无法造车)
• 原则之二 `
GAP 锁很复杂, 为了减少 GAP 锁, 减少 GAP 导致的死锁, 尽量选择 Read Committed 隔离级别 (RC + row based binlog, 基本上能够解决所有问题, 无需使用 Repeatable Read)
适当的 减少 Unique 索引, 能够减少 GAP 锁导致的死锁 (根据业务情况而定)
• 原则之三
在 MySQL 中, 以不同索引的过滤条件, 来操作相同的记录 (Update/Delete ), 很容易产生死
锁.
• 原则之四
RC 隔离级别下, 如果死锁中出现 Next Key(Gap 锁), 说明表中一定存在 unique 索引
多语句事务产生的死锁, 确保每条语句操作记录的顺序性, 能够极大减少死锁
本文大多数都整理自《死锁 - 何登成 - 管中窥豹 --MySQL(InnoDB) 死锁分析之道》
来源: http://www.linuxidc.com/Linux/2019-07/159527.htm