谈 MySQL 加锁机制

首先来看这个面试题： 已知表 t 是 innodb 引擘，有主键：id（int 类型) ，下面 3 条语句是否加锁？加锁的话，是什么锁？

1. select _ from t where id=X;

2. begin;select _ from t where id=X;

3. begin;select * from t where id=X for update;

这里其实有很多依赖条件，并不能一开始就给出一个很确定的答复。我们一层层展开来分析。

1 MySQL 有哪些锁？

首先要知道 MySQL 有哪些锁，如上图所示，至少有 12 类锁（其中自增锁是事务向包含了 AUTO_INCREMENT 列的表中新增数据时会持有，predicate locks for spatial index 为空间索引专用，本文不讨论这 2 类锁）。

锁按粒度可分为分为全局，表级，行级3 类。

1.1 全局锁

对整个数据库实例加锁。 加锁表现：数据库处于只读状态，阻塞对数据的所有 DML/DDL； 加锁方式：Flush tables with read lock 释放锁：unlock tables(发生异常时会自动释放)； 作用场景：全局锁主要用于做数据库实例的逻辑备份，与设置数据库只读命令set global readonly=true相比，全局锁在发生异常时会自动释放。

1.2 表锁

对操作的整张表加锁， 锁定颗粒度大，资源消耗少，不会出现死锁，但会导致写入并发度低。具体又分为 3 类： 1）显式表锁：分为共享锁（S）和排他锁（X） 显示加锁方式：lock tables ... read/write 释放锁：unlock tables(连接中断也会自动释放)

2）Metadata-Lock（元数据锁）：MySQL5.5 版本开始引入，主要功能是并发条件下，防止 session1 的查询事务未结束的情况下，session2 对表结构进行修改，保护元数据的一致性。在 session1 持有 metadata-lock 的情况下，session2 处于等待状态：show processlist 可见Waiting for table metadata lock。其具体加锁机制如下：

• DML->先加 MDL 读锁（SHARED_READ，SHARED_WRITE）
• DDL->先加 MDL 写锁（EXCLUSIVE）
• 读锁之间兼容
• 读写锁之间、写锁之间互斥

3）Intention Locks（意向锁）：意向锁为表锁（表示为 IS 或者 IX），由存储引擎自己维护，用户无法干预。下面举一个例子说明其功能：

假设有 2 个事务：T1 和 T2 T1: 锁住表中的一行，只能读不能写（行级读锁）。 T2: 申请整个表的写锁（表级写锁）。 如 T2 申请成功，则能任意修改表中的一行，但这与 T1 持有的行锁是冲突的。故数据库应识别这种冲突，让 T2 的锁申请被阻塞，直到 T1 释放行锁。

有 2 种方法可以实现冲突检测： \1. 判断表是否已被其它事务用表锁锁住。 \2. 判断表中的每一行是否已被行锁锁住。

其中 2 需要遍历整个表，效率太低。因此 innodb 使用意向锁来解决这个问题： T1 需要先申请表的意向共享锁（IS），成功后再申请某一行的记录锁 S。 在意向锁存在的情况下，上面的判断可以改为： T2 发现表上有意向共享锁 IS，因此申请表的写锁被阻塞。

1.3 行锁

InnoDB 引擘支持行级别锁，行锁粒度小，并发度高，但加锁开销大，也可能会出现死锁。

加锁机制：innodb 行锁锁住的是索引页，回表时，主键的聚簇索引也会加上锁。

行锁具体类别如上图所示，包括：Record lock/Gap Locks/Next-Key Locks，每类又可分为共享锁（S）或者排它锁（X），一共 23=6 类，最后还有 1 类插入意向锁：

Record lock（记录锁）：最简单的行锁，仅仅锁住一行。记录锁永远都是加在索引上的，即使一个表没有索引，InnoDB 也会隐式的创建一个索引，并使用这个索引实施记录锁。

Gap Locks（间隙锁）：加在两个索引值之间的锁，或者加在第一个索引值之前，或最后一个索引值之后的间隙。使用间隙锁锁住的是一个区间，而不仅仅是这个区间中的每一条数据。间隙锁只阻止其他事务插入到间隙中，不阻止其它事务在同一个间隙上获得间隙锁，所以 gap x lock 和 gap s lock 有相同的作用。它是一个左开右开区间：如（1，3）

Next-Key Locks：记录锁和间隙锁的组合，它指的是加在某条记录以及这条记录前面间隙上的锁。它是一个左开右闭区间：如（1，3】

Insert Intention（插入意向锁）：该锁只会出现在 insert 操作执行前（并不是所有 insert 操作都会出现），目的是为了提高并发插入能力。它在插入一行记录操作之前设置一种特殊的间隙锁，多个事务在相同的索引间隙插入时，如果不是插入间隙中相同的位置就不需要互相等待。

TIPS:

1.不存在 unlock tables … read/write，只有 unlock tables 2.If a session begins a transaction, an implicit UNLOCK TABLES is performed

2 锁的兼容情况

引入意向锁后，表锁之间的兼容性情况如下表：

总结：

1. 意向锁之间都兼容
2. X,IX 和其它都不兼容（除了 1）
3. S,IS 和其它都兼容（除了 1,2）

3 锁信息查看方式

• MySQL 5.6.16 版本之前，需要建立一张特殊表 innodb_lock_monitor，然后使用show engine innodb status查看

CREATE TABLE innodb_lock_monitor (a INT) ENGINE=INNODB;

DROP TABLE innodb_lock_monitor;

• MySQL 5.6.16 版本之后，修改系统参数 innodb_status_output 后，使用show engine innodb status查看

set GLOBAL innodb_status_output=ON;

set GLOBAL innodb_status_output_locks=ON;

每 15 秒输出一次 INNODB 运行状态信息到错误日志。

• MySQL5.7 版本之后

可以通过 information_schema.innodb_locks 查看事务的锁情况，但只能看到阻塞事务的锁；如果事务并未被阻塞，则在该表中看不到该事务的锁情况

• MySQL8.0

删除 information_schema.innodb_locks，添加 performance_schema.data_locks，即使事务并未被阻塞，依然可以看到事务所持有的锁，同时通过 performance_schema.table_handles、performance_schema.metadata_locks 可以非常方便的看到元数据锁等表锁。

4 测试环境搭建

4.1 建立测试表

该表包含一个主键，一个唯一键和一个非唯一键：

CREATE TABLE t (

id int(11) NOT NULL,

a int(11) DEFAULT NULL,

b int(11) DEFAULT NULL,

c varchar(10),

PRIMARY KEY (id),

unique KEY a (a),

key b(b))

ENGINE=InnoDB;

4.2 写入测试数据

insert into t values(1,10,100,'a'),(3,30,300,'c'),(5,50,500,'e');

5 记录存在时的加锁

对于 innodb 引擘来说，加锁的 2 个决定因素：

1）当前的事务隔离级别 2）当前记录是否存在

假设 id 为 3 的记录存在，则在不同的 4 个隔离级别下 3 个语句的加锁情况汇总如下表(select 3 表示 select * from t where id=3)：

分析：

1. 使用以下语句在 4 种隔离级别之间切换： set global transaction_isolation='READ-UNCOMMITTED'; set global transaction_isolation='READ-COMMITTED'; set global transaction_isolation='REPEATABLE-READ'; set global transaction_isolation='Serializable';
2. 对于 auto commit=true，select 没有显式开启事务（begin）的语句，元数据锁和行锁都不加，是真的“读不加锁”
3. 对于 begin; select ... where id=3 这种只读事务，会加元数据锁 SHARED_READ，防止事务执行期间表结构变化，查询performance_schema.metadata_locks表可见此锁：