MySQL 锁相关

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

  MyISAM InnoDB
事务 不支持 支持
表级锁 不支持 支持
全文搜索 支持 5.6.4 以上支持
外键 不支持 支持

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

InnoDB 锁

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

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

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

如果 T1r 上有一个排它锁 (X), 那么 T2r 上无论是 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 UPDAGTESELECT ... LOCK IN SHARE MODE 除了在相应行上获得相应的锁, 在表上也会分别获得 IX 锁和 IS 锁.

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

  X IX S IS
X 互斥 互斥 互斥 互斥
IX 互斥 兼容 互斥 兼容
S 互斥 互斥 兼容 兼容
IS 互斥 兼容 兼容 兼容

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

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

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

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

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

事务隔离级别

Isolation Level Dirty Read Nonrepeatable Read Phantom Read
Read uncommitted Possible Possible Possible
Read committed Not possible Possible Possible
Repeatable read Not possible Not possible Possible
Serializable Not possible Not possible Not possible

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

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