MySQL 加锁和死锁解析
目录
-
产生死锁的必要条件 -
常规锁模式 -
锁的属性 -
锁组合(属性+模式) -
锁冲突矩阵 -
锁是加在那里的? -
操作与加锁的对照关系 -
那些操作会加GAP锁? -
如何去掉GAP锁? -
Insert -
Delete -
Update -
GAP锁 -
什么时候加next-key lock? -
Insert Intention Lock -
总结
产生死锁的必要条件
-
多个并发事务(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 tabletest
.tt_copy
trx id 1101590 lock_mode X locks rec but not gap
操作与加锁的对照关系
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
Step 1:定位到 下一条满足查询条件的记录(查询过程,类似于Select/Delete) Step 2:删除当前定位到的记录(标记为删除状态) Step 3:拼装更新后项,根据更新后项定位到 新的插入位置 Step 4:在新的插入位置,判断是否存在 Unique 冲突( 存在Unique Key 时) Step 5:插入更新后项(不存在Unique冲突时) Step 6: 重复Step 1 到Step 5 的操作,直至扫描完整个查询范围
Step 1,Step 2:Delete Step 3,Step 4,Step 5:Insert
-
无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索引 多语句事务产生的死锁,确保每条语句操作记录的顺序性,能够极大减少死锁