MySQL 加锁和死锁解析

419次阅读

共计 2324 个字符，预计需要花费 6 分钟才能阅读完成。

多个并发事务（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 隔离级别

无 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

满足删除条件的所有记录：LOCK_X+LOCK_REC_NOT_GAP

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

Read Committed（RC））：Unique Key 唯一约束检查；Purge 操作；
Repeatable Read（RC）：RC 的基础上，所有需要加锁的索引范围扫描和索引查找（Update/Delete…)
还有一种会加 GAP 锁：RR 隔离级别下，对有唯一索引的表执行 insert on duplicate update 操作，除了会对新插入的记录加 x not gap 外，还会对相邻记录加 x gap

change the transaction isolation level to READ COMMITTED or enable the innodb_locks_unsafe_for_binlog system variable (which is now deprecated)

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

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 锁导致的死锁（根据业务情况而定）

• 原则之三