闲谈常见SQL语句加锁

原文:https://zhuanlan.zhihu.com/p/250666652

锁是使用数据库时避不开的问题,MySQL 中的锁可以分成两个粒度:表锁和行锁。

表锁:表级读锁,表级写锁,读意向锁,写意向锁,自增锁

行锁:读记录锁,写记录锁,间隙锁,Next-key 锁,插入意向锁。

这些锁一旦冲突就会导致死锁问题的产生。

S锁和X锁

  • S锁:共享锁,加了S锁的记录,允许其他事务再加S锁,不允许其他事务再加X锁
  • X锁:排他锁,加了X锁的记录,不允许其他事务再加S锁或者X锁

意向锁

意向锁的存在是为了协调行锁和表锁的关系,表明某个事务持有了锁、或准备去持有锁,支持多粒度(表锁与行锁)的锁并存。

  • 意向共享锁(IS锁):事务在请求S锁前,要先获得IS锁
  • 意向排他锁(IX锁):事务在请求X锁前,要先获得IX锁

表锁

  • 表锁(分 S 锁和 X 锁)
  • 意向锁(分 IS 锁和 IX 锁)
  • 自增锁(一般见不到,只有在 innodb_autoinc_lock_mode = 0 或者 Bulk inserts 时才可能有)

行锁

行锁都是加在索引上的,最终都会落在聚簇索引上,加行锁的过程是一条一条记录加的。

  • 记录锁(分 S 锁和 X 锁)
  • 间隙锁(分 S 锁和 X 锁)
  • Next-key 锁(分 S 锁和 X 锁)
  • 插入意向锁

锁冲突

  • S 锁和 S 锁兼容,X 锁和 X 锁冲突,X 锁和 S 锁冲突
  • 表锁和行锁的冲突矩阵

GAP锁

间隙锁实质上是对索引前后的间隙上锁,不对索引本身上锁。

根据检索条件向左寻找最靠近检索条件的记录值A,作为左区间,向右寻找最靠近检索条件的记录值B作为右区间,即锁定的间隙为(A,B)。间隙锁的目的是为了防止幻读,防止间隙内有新数据被插入,防止已存在的数据,更新成间隙内的数。

sql加锁的规则

select加锁规则

  • SELECT ... 语句正常情况下为快照读,不加锁。
  • SELECT ... LOCK IN SHARE MODE 语句为当前读,加 S 锁。
  • SELECT ... FOR UPDATE 语句为当前读,加 X 锁。
  • 常见的 DML 语句(如 INSERT、DELETE、UPDATE)为当前读,加 X 锁。
  • 常见的 DDL 语句(如 ALTER、CREATE 等)加表级锁,且这些语句为隐式提交,不能回滚。

不同隔离级别下的锁

  • SELECT ... 语句正常情况下为快照读,不加锁;但是在 Serializable 隔离级别下为当前读,加 S 锁
  • RC 隔离级别下没有间隙锁和 Next-key 锁(特殊情况下也会有:purge + unique key)

update加锁分析

使用下面这张 students 表作为实例,其中 id 为主键,no(学号)为二级唯一索引,name(姓名)和 age(年龄)为二级非唯一索引,score(学分)无索引。

img

聚簇索引,查询命中

UPDATE students SET score = 100 WHERE id = 15

在 RC (读已提交)和 RR (可重复读)隔离级别下加锁情况一样,都是对 id 这个聚簇索引加 X 锁

聚簇索引,查询未命中

UPDATE students SET score = 100 WHERE id = 16

RC 不加锁,RR 有 GAP 锁,id15到18间隙锁

二级唯一索引,查询命中

UPDATE students SET score = 100 WHERE no = 'S0003'

命中二级唯一索引,因为二级索引的叶子节点中保存了主键索引的位置,在给二级索引加锁的时候,主键索引也会一并加X锁。

二级唯一索引,查询未命中

RC 无锁,RR 隔离级别会加 GAP 锁。

二级非唯一索引,查询命中

RC 无锁,RR 隔离级别会加 GAP 锁。

二级非唯一索引,查询未命中

RC 无锁,RR 隔离级别会加 GAP 锁。

为什么非唯一索引会加 GAP 锁,而唯一索引不用加 GAP 锁?

GAP 锁的作用是为了解决幻读,防止其他事务插入相同索引值的记录,而唯一索引和主键约束都已经保证了该索引值肯定只有一条记录,所以无需加 GAP 锁。

无索引

UPDATE students SET score = 100 WHERE score = 22

满足条件的虽然只有 1 条记录,但是聚簇索引上所有的记录,都被加上了 X 锁。

在没有索引的时候,只能走聚簇索引,对表中的记录进行全表扫描。在 RC 隔离级别下会给所有记录加行锁,在 RR 隔离级别下,不仅会给所有记录加行锁,所有聚簇索引和聚簇索引之间还会加上 GAP 锁。

聚簇索引,范围查询

UPDATE students SET score = 100 WHERE id <= 20

RC把id = 20、18、15 三条记录锁住, RR 把 id = 30 这条记录以及 (20, 30] 之间的间隙锁住

对于范围查询,如果 WHERE 条件是 id <= N,那么 N 后一条记录也会被加上 Next-key 锁。如果条件是 id < N,那么 N 这条记录会被加上 Next-key 锁。如果 WHERE 条件是 id >= N,只会给 N 加上记录锁,以及给比 N 大的记录加锁,不会给 N 前一条记录加锁;如果条件是 id > N,也不会锁前一条记录,连 N 这条记录都不会锁。

二级索引,范围查询

UPDATE students SET score = 100 WHERE age <= 23

和聚簇索引的范围查询一样,除了 WHERE 条件范围内的记录加锁之外,后面一条记录也会加上 Next-key 锁

insert加锁

一般加锁都是对表中已有的记录进行加锁,而 INSERT 语句是插入一条新的纪录,这条记录表中本来就没有,但是并不是不需要加锁。

  • 为了防止幻读,如果记录之间加有 GAP 锁,此时不能 INSERT
  • 如果 INSERT 的记录和已有记录造成唯一键冲突,此时不能 INSERT

对插入的间隙加插入意向锁(Insert Intension Locks),如果该间隙已被加上了 GAP 锁或 Next-Key 锁,则加锁失败进入等待。如果没有,则加锁成功,表示可以插入。

判断插入记录是否有唯一键,如果有,则进行唯一性约束检查

  • 如果不存在相同键值,则完成插入
  • 如果存在相同键值,则判断该键值是否加锁
  • 如果没有锁, 判断该记录是否被标记为删除
  • 如果标记为删除,说明事务已经提交,还没来得及 purge,这时加 S 锁等待;
  • 如果没有标记删除,则报 1062 duplicate key 错误;
  • 如果有锁,说明该记录正在处理(新增、删除或更新),且事务还未提交,加 S 锁等待;
  • 插入记录并对记录加 X 记录锁;