MySQL锁那些事儿

本文转载自微信公众号「码虫甲」，作者码虫甲。转载本文请联系码虫甲公众号。

背景

当数据库中有多个操作需要修改同一数据时，不可避免的会产生数据的脏读。这时就需要数据库具有良好的并发控制能力，这一切在 MySQL
中都是由服务器和存储引擎来实现的。解决并发问题最有效的方案是引入了锁的机制，锁在功能上分为共享锁 (shared lock) 和排它锁 (exclusive
lock) 即通常说的读锁和写锁; 锁的粒度上分行锁和表锁，表级锁MySQL 里面表级别的锁有两种：一种是表锁，一种是元数据锁(meta data
lock，MDL)

行锁种类

Next-Key Lock：锁定一个范围，并且锁定记录本上;Gap Lock：间隙锁，锁定一个范围，但不包含记录本上;Record Lock：单个行记录上的锁;

加锁规则

虽然 MySQL 的锁各式各样，但是有些基本的加锁原则是保持不变的，譬如：快照读是不加锁的，更新语句肯定是加排它锁的，RC
隔离级别是没有间隙锁的等等。这些规则整理如下：

常见语句的加锁

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

表锁

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

行锁分析

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

锁冲突

S 锁和 S 锁兼容，X 锁和 X 锁冲突，X 锁和 S 锁冲突;

不同隔离级别下的锁

上面说 SELECT ... 语句正常情况下为快照读，不加锁;但是在 Serializable 隔离级别下为当前读，加 S 锁;RC 隔离级别下没有间隙锁和 Next-key 锁

SQL 的加锁分析

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

我们只分析最简单的一种 SQL，它只包含一个 WHERE 条件，等值查询或范围查询。虽然 SQL
非常简单，但是针对不同类型的列，我们还是会面对各种情况：

聚簇索引，查询命中：UPDATE students SET score = 100 WHERE id = 15;聚簇索引，查询未命中：UPDATE students SET score = 100 WHERE id = 16;二级非唯一索引，查询命中：UPDATE students SET score = 100 WHERE name = Tom;二级非唯一索引，查询未命中：UPDATE students SET score = 100 WHERE name = John;无索引：UPDATE students SET score = 100 WHERE score = 22;聚簇索引，范围查询：UPDATE students SET score = 100 WHERE id