闲谈常见SQL语句加锁

发表于 分类于 阅读次数： 本文字数： 1.7k 阅读时长 ≈ 6 分钟

原文：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 记录锁；