MySQL 锁相关

MySQL 有两个常见的引擎: MyISAM 和 InnoDB. 两者主要的区别有:

所以通常情况下我们都会选择 InnoDB 引擎, 除非你的数据库是大量的读而很少写才会选择 MyISAM 引擎.

InnoDB 锁

共享锁 (Share) 排它锁 (Exclusive)

InnoDB 支持两种常见的 行级锁: 共享锁和排它锁

• 共享锁 (S): 拿到共享锁的事务可以去读取一行数据
• 排它锁 (X): 拿到排它锁的事务可以更新和删除一行数据

假如事务 T1 在 row r 上有一个共享锁 (S), 现在事务 T2 想要在 r 上获取锁, 那么:

• T2 可以马上获得 S 锁, 最终 T1 和 T2 在 r 上各有一个 S 锁
• T2 无法立即获得一个 X 锁

如果 T1 在 r 上有一个排它锁 (X), 那么 T2 在 r 上无论是 S 锁还是 X 锁都无法立即获得.

SELECT ... FOR UPDATE 可以在相应的行上获得 X 锁, SELECT ... LOCK IN SHARE MODE 可以在相应的行上获得 S 锁.

需要注意的是, InnoDB 是锁在索引上的, 所以如果你的 SELECT 语句中的 WHERE 没有用到加索引的列, 那么 InnoDB 就会在所有行上加上锁, 相当于在表上加锁了.

意向锁

InnoDB 的意向锁是 表级锁 , 意向锁有两种: Intention shared (IS), Intention exclusive (IX).

当一个事务想要获得某行的 S 锁时, 它必须先获得表上 IS 锁, 当一个事务想要获得某行的 X 锁时, 它必须先获得表上 IX 锁. 所以 SELECT ... FOR UPDAGTE 和 SELECT ... LOCK IN SHARE MODE 除了在相应行上获得相应的锁, 在表上也会分别获得 IX 锁和 IS 锁.

在表级别粒度下, 锁之间的互斥关系如下:

上表中的 X 锁和 S 锁都是指表级别的锁 (InnoDB 的 X 锁和 S 锁都是行级别的, 所以不会冲突). 可以通过 LOCK TABLES ... READ | WRITE 获得表级别的 S 和 X 锁.

记录锁 (Record Locks)

InnoDB 对于行级锁的实现都是锁在索引上的. 如果一个表没有设置任何索引, InnoDB 会给表添加一个隐藏的索引. 加在这个索引上的锁就叫记录锁.

虽然共享锁, 排它锁, 意向锁, 记录锁以及下面要介绍的锁都属于锁的模式 (Lock Mode), 但我的理解是 共享锁, 排它锁和意向锁更偏是锁的策略, 其他的锁更偏是锁的对象.

间隙锁 (Gap Locks)

间隙锁是锁在间隙上的锁. 间隙可以位于记录与记录之间, 最小的索引之前或者最大的索引之后.

需要注意的是, 间隙锁与间隙锁之间是不会冲突的. 间隙锁只和之后会介绍的 插入意向锁 冲突. 这里的冲突是指如果间隙上已经有了间隙锁, 那么间隙上无法加上 插入意向锁. 但是反过来, 如果间隙上有 插入意向锁, 间隙锁是可以加上的.

所以间隙锁只在需要防止其它事务插入时使用, 比如在 RR 隔离级别执行 SELECT ... FOR UPDATE 时会锁住符合条件的所有间隙.

Next-Key Locks

如果同时锁住了记录锁和该记录锁之前的间隙, 那么就把这两个锁合起来称作 Next-Key Lock.

插入意向锁 (Insert Intention Locks)

该锁也是锁在间隙上的. 当执行插入语句的时候, 会在插入记录之前检查待插入记录的索引前的间隙上是否有间隙锁, 如果有就在间隙上等待插入 插入意向锁. 需要注意的事, InnoDB 在具体实现上为了效率, 并不会每次插入都加 插入意向锁, 而只会在检测到有锁冲突的时候加 插入意向锁. 如果没有冲突, 就直接插入记录. 那么 InnoDB 是怎么保证隔离性的呢? 答案就是, 隐式锁. 其他事务想要去给未提交的记录加锁, 会看记录上的事务 id 对应的事务是否活跃, 如果活跃就帮该事务加锁.

具体的 Insert 执行流程可以参考这篇文章, 写的很好: 读 MySQL 源码再看 INSERT 加锁流程

AUTO-INC Locks

这是个表级锁, 当表有自增 id 的时候会使用. 该锁的具体加锁策略和 innodb_autoinc_lock_mode 配置项有关. 该锁和一般的锁不一样, 它不是在事务结束的时候才释放的, 具体参考 AUTO_INCREMENT Handling in InnoDB

悲观锁 (Pessimistii Locking) 乐观锁 (Optimistic Locking)

因为翻译的原因, 很容易让人误以为悲观锁和乐观锁与之前提到的共享锁和排它锁是一类东西. 但英文里共享锁和排它锁的锁是名词 (Lock), 悲观锁和乐观锁的锁是动词 (Locking).

悲观锁和乐观锁是用锁的策略 (strategy).

假如我现在要先读取一个数据, 然后再修改它. 悲观锁的用锁方式是, 读取数据时就让数据库给数据加上锁 SELECT ... FOR UPDATE, 最后调用 UPDATE 修改数据. 而乐观锁的方式是先正常的读取数据 SELECT ..., 最后修改的时候判断下数据的时间戳和之前读取的时间戳一致不一致 UPDATE ... WHERE (这里的时间戳充当了锁的角色), 不一致则说明数据读取之后被修改过 (用时间戳并不是唯一的方式, 也可以用版本号).

锁的观察

虽然上面描述了很多的锁, 但是理论和实现肯定是有区别的, 具体实现的时候肯定会有很多现实面临的问题需要考虑. 所以有时候发现 InnoDB 加锁的行为出乎意料的时候, 最好有办法直接看 InnoDB 加了什么锁. MySQL 8.0 我们可以通过下面的表查看当前加的锁:

select * from performance_schema.data_locks

大部分字段都比较直白, 主要讲下 LOCK_MODE. 如果值为 X 或者 S 表示这是一个 Next-Key Lock, 如果锁住的是最后一个索引之后的间隙, 那么 LOCK_DATA 的值是 supremum pseudo-record, 你可以理解为这是一个虚拟的最大的索引. 如果是记录锁那么 LOCK_MODE 的值会是 X,REC_NOT_GAP, 间隙锁的话是 X,GAP.

事务隔离级别

• Dirty Read (脏读): 读到其他事务未提交的数据
• Nonrepeatable Read (不可重复读): 可不可以重复读是指, 多次读取, 同一行数据中的列数据会不会发生变化. (读到新的行不算)
• Phantom Read (幻读): 多次读取, 会不会出现新增的行.

需要注意的是, SQL 标准中只规定了相应的隔离级别中哪些现象不可以发生, 并没有说相应的级别中这些现象一定会发生. 比如在 PostgreSQL 中, 事务在 Read Uncommitted 隔离级别下是不会出现脏读的, 同时 PostgreSQL 中事务是不会出现幻读的.

传统的隔离级别是基于锁实现的, 这种方式叫做 基于锁的并发控制 (Lock-Based Concurrent Control, 简写 LBCC). 虽然数据库的四种隔离级别通过 LBCC 技术都可以实现, 但是它最大的问题是它只实现了并发的读读, 对于并发的读写还是冲突的. 针对这种场景, MVCC (Multi-Version Concurrent Control) 技术应运而生. 具体就不多说了, 这文章写的很详细了 解决死锁之路 - 学习事务与隔离级别

InnoDB Locking

MySQL · 引擎特性 · InnoDB 事务锁系统简介

Transaction Isolation

Optimistic locking in MySQL

Lock (database)

X row locks do not prevent an IX table lock, contrary to documentation

Why doesn’t MySQL’s MyISAM engine support Foreign keys?

MyISAM versus InnoDB

解决死锁之路 - 了解常见的锁类型

解决死锁之路 - 常见 SQL 语句的加锁分析