MySQL锁机制深入理解

共计 3893 个字符，预计需要花费 10 分钟才能阅读完成。

本文参考自 MySQL 官网 5.6 版本参考手册的 14.5.1, 此小节说明 MySQL 的锁分类，此外还有 14.5.2 小节和 14.5.3 小节详述事务隔离级别和各 SQL 语句的加锁模式，后两节将单独写 2 篇笔记。

• https://dev.mysql.com/doc/refman/5.6/en/innodb-locking.html
• https://dev.mysql.com/doc/refman/5.6/en/innodb-transaction-isolation-levels.html
• https://dev.mysql.com/doc/refman/5.6/en/innodb-locks-set.html 

第一部分：概述

Myisam 的锁比较容易理解，无论是读还是写都只会加表锁，表锁又分为 read 锁和 write 锁，可以使用如下方式手动加锁：

– 加表锁语句（同样适用于 InnoDB）：
lock tables
tbl_name [[AS] alias] lock_type
[, tbl_name [[AS] alias] lock_type] …
lock_type:
READ [LOCAL]
| [LOW_PRIORITY] WRITE
– 解表锁语句：
unlock tables
– 如何观察 InnoDB 锁：
set @@global.innodb_status_output_locks=on;
– 这样 show engine innodb status\G 可以显示额外的锁信息，标准情况下只显示锁数目。

由于 Myisam 这样的锁机制，导致 Myisam 是一款读性能较好，并发写性能较差的存储引擎，本文主要讨论如今的 MySQL 默认存储引擎 InnoDB 的锁机制。

第二部分：InnoDB 锁分类

InnoDB 存储引擎在使用到索引时会使用行锁，否则使用表锁。InnoDB 没有页锁，只有表锁和行锁。

一、InnoDB 表锁有以下几种：

S：就是在概述部分描述的表级 read 锁。

X：就是在概述部分描述的表级 write 锁。

IS：表级意向共享锁，即表示事务有向底层资源加共享行锁的意向。如 select … lock in share mode 语句，在加行锁之前会在表上现加 IS 锁，这样可以提高锁冲突检测的效率，同时也可以避免事务在表级添加会使其他事务行锁失效的表级锁。

IX：表级意向独占锁，即表示事务有向底层资源加独占行锁的意向。一般来说 delete、update 语句和 select … for update 语句都会在加行锁之前先加表级 IX 锁，除非未用到索引（此时直接加表级 X 锁）。

表锁的兼容性图：

此外表级锁还有一种比较特殊的锁：AUTO-INC Locks

这种锁只在向自增主键中插入记录时出现，由于自增主键在 MySQL 中较为常见，因此也算是经常会遇到的锁，这种锁是为自增主键设计的，无需和以上 4 钟锁检测冲突。

AUTO-INC Locks 的锁机制：

在向自增主键中插入记录时，其他 insert 事务都需要等待直到本事务的插入完成才能继续插入自增记录，注意是插入完成而不是本事务完成。这很好理解，因为需要保证自增主键的连贯性。但是如果你有超高的插入并发，那么肯定会带来性能问题。

因此 InnoDB 也提供了折中的方案，innodb_autoinc_lock_mode 参数可以控制你是否使用这种锁，如果你的自增主键不需要严格连贯而且需要更高的 insert 并发，那么可以禁用掉这种锁。

但是如果你做了主从复制，而且使用的是 statement 模式的 binlog，那么禁用 innodb_autoinc_lock_mode 后可能造成主从自增主键不一致，尤其是遇到 insert … select … from table_name; 这种语句。此时需要改为 row 模式或 mixed 模式的 binlog 主从复制，因为 row 模式对 SQL 执行顺序不敏感，而 mixed 模式也会将可能影响主从复制的 statement 改为 row 模式传输。

那么最后还有个问题就是既需要超高插入并发又需要连贯自增，那该怎么办？

凉拌~

二、InnoDB 行锁有以下四种：

1.Record lock

即在索引上加的锁，lock_mode 分为 S 和 X 两种模式。

例如 SELECT c1 FROM t WHERE c1 = 10 FOR UPDATE; 就会 c1 列的索引上添加 S 类型的 Record lock。

Record lock 一定是加在索引记录上的，即便是一个没有定义任何索引的表，InnoDB 也会创建一个隐式的聚集索引，在用到此索引时加 Record lock。

2.Gap lock

即间隙锁，锁定不存在的索引记录，官方定义是：Gap lock 用于锁定 2 个索引记录之间、或第一个索引记录之前、或最后一个索引记录之后的范围。

通常我们会把 Record lock 和 Gap lock 合起来用，称为 Next-key lock，因此 Gap lock 就不多说了。

之所以设计 Gap lock 主要是为了解决幻读问题的，参考 SQL Server 的键范围锁。Gap 锁是可以禁用的，你可以将数据库的全局隔离级别设置为 read committed 或者将 innodb_locks_unsafe_for_binlog 参数设置为 1 来禁用 Gap lock，只是这样就会出现幻读，不过幻读一般并不是什么大问题，比如 Oracle 数据库的默认隔离级别下就无法避免幻读，不也大把人在用吗。

另外必须要说的一点是同一个 gap 上的 Gap lock 的 S 和 X 模式效果完全一样的，就算你加了一个 X 模式的 gap lock，其他事务也能在同一个 gap 上再加一个 X 模式的 gap lock，不会阻塞，当然仅限于同一个 gap。

3.Next-key lock

即 Record lock 和 Gap lock 的合体。例如 SELECT c1 FROM t WHERE c1 BETWEEN 10 and 20 FOR UPDATE; 会在 [10,20] 之间的 c1 index record 上加 lock_mode 为 X 的 next-key lock，也就是说会在 [10,20] 之间的所有存在的 index record 上加 X 模式的 record lock，同时也会用 X 模式的 gap 锁锁定不存在的 index record 防止幻读，这两种锁加起来就称作 next-key lock。

如果使用的索引是唯一索引，那么不加 next-key lock 的，只加 record lock。

再次提醒的是 next-key lock 其实并不存在而是 Record lock 和 Gap lock 的合体，show engine innodb status\G 显示的结果也都是用 Record lock 来展示的，不过展示出的数目比较诡异看不懂源码的话不建议深究，这点比 Oracle 和 Sqlserver 差太远。这里我就要顺带吐槽一下官网手册了，毕竟是开源 DB，一些前后矛盾和明显有歧义的解释也是让人很无奈。

4. 插入意向锁（Insert Intention Locks）

这个锁也是一个 InnoDB 的奇葩例子，不知道大家发现没 InnoDB 在谈 IX IS 还有行锁这些锁的时候基本不用 insert 语句来举例，这点如果是熟悉 Oracle 和 SQL Server 的人就会很困惑，因为增删改全都是 DML 语句，大家加锁机制基本相似的，无非就是表级意向锁 + 页级 or 行级锁的套路，但是 InnoDB 不是这样！！！insert 语句和 delete、update 完全不是一路人！！

这个锁用于表明：只要不是插入相同的 index record，多个事务向同一个 gap 插入记录是不会阻塞的。

插入意向锁其实是行级别的一种意向 gap 锁，既然有意向两字那么可以认定就是用于检测锁冲突的，是为在行级别获取 X 模式的 record lock 锁提前做检测。

用一个例子来解释更为明了：

– 会话 A 执行：
CREATE TABLE child (id int(11) NOT NULL, PRIMARY KEY(id)) ENGINE=InnoDB;
INSERT INTO child (id) values (90),(102);
START TRANSACTION;
SELECT * FROM child WHERE id > 100 FOR UPDATE;
– 会话 B 执行：
INSERT INTO child (id) VALUES (101);

可以看到会话 B 被阻塞了，而 show engine innodb status\G 看到的锁等待如下：

即 insert 语句想在 (90,102) 的 gap 上加个 lock_mode= X 的 gap 锁，也就是 Insert Intention Lock，但是会话 A 的 select for update 语句已经在 (100,102) 的 gap 上添加了 X 模式的 gap 锁，这是一个与 (90,102) 不同但被包含在内的 gap，于是被阻塞无法获取 X 模式的 Insert Intention Gap Lock。

三、总结

MySQL 的锁机制基本就如上所示了，但是了解 InnoDB 锁只是初步的，还必须结合事务隔离级别的概念去判断各种 SQL 的具体加锁机制，因为事务隔离级别会影响 SQL 的默认加锁模式。

MySQL 的事务隔离级别定义也是遵循 ANSI SQL92 标准的，不过但凡是家数据库厂商都会说自己遵循 SQL92 标准，而事实是早已加料加的面目全非。当然这全都是为了能够提供更好的并发性能。例如 Oracle 也说自己遵循 SQL92 标准，结果四大隔离级别只支持 2 个，SQL Server 也说自己支持，结果又多造出来 2 个事务隔离级别。

同样的 MySQL 也提供了 4 大基本的事务隔离级别，不同的隔离级别下加锁机制区别很大，将在另一篇笔记中详述。

本文永久更新链接地址：https://www.linuxidc.com/Linux/2018-04/151914.htm