8月份的时候简单写过MySQL的锁,最近在工作中又有了一些更细致的理解,在此记录
从是否共享进行分析
Shared Locks共享锁 S锁
作用于行,得到共享锁的事务允许读取一行数据
Exclusive Locks排他锁 X锁
作用于行,得到排他锁的事务允许更新或删除一行
Intention shared Locks意向共享锁 IS锁
表锁,获得意向共享锁的事务可以获得共享锁
Intention exclusive Locks意向排他锁 IX锁
表锁,获得意向排他锁的事务可以获得排他锁
相互之间的兼容关系为
X | IX | S | IS | ||
---|---|---|---|---|---|
X | 冲突 | 冲突 | 冲突 | 冲突 | |
IX | 冲突 | 兼容 | 冲突 | 兼容 | |
S | 冲突 | 冲突 | 兼容 | 兼容 | |
IS | 冲突 | 兼容 | 兼容 | 兼容 |
从锁的范围分析具体锁
GAP间隙锁
行锁,锁住一段索引范围(开区间),不包含数据行本身
Insert Intention Locks插入意向锁
行锁,锁住一段索引范围,再同一间隙锁下的插入互相不影响,但是对其他操作是排它锁
Record Locks记录锁
行锁,锁住索引而不是数据行本身,如果该表没有任何索引,则建立一个隐藏的聚集主键索引
Next-Key Locks间隙锁
行锁,锁住一段范围及数据行(左开右闭),可以认为范围是Record Locks+GAP
AUTO-INC Locks自增锁
表锁,在自增列插入的时候产生
相互之间的兼容关系为
Gap | Insert Intention | Record | Next-Key | ||
---|---|---|---|---|---|
Gap | 兼容 | 兼容 | 兼容 | 兼容 | |
Insert Intention | 冲突 | 兼容 | 兼容 | 冲突 | |
Record | 兼容 | 兼容 | 冲突 | 冲突 | |
Next-Key | 兼容 | 兼容 | 冲突 | 冲突 |
加深理解
- 如果SQL没有命中任何索引,则根据记录锁的特性,全部命中到隐藏的聚集主键索引,所有的操作在同一个索引中使用X锁,相当于表锁
- 默认情况使用Next-Key锁,出现唯一索引时降级到Record Locks,索引唯一索引除了在定位时效率之外,锁的范围也能变得更小
- 会出现冲突的情况
- GAP、NEXT_KEY之后的Insert(在同一间隙内先删除再插入,这也是本次生产问题产生的原因)
- Record和之后的Record、Next-key