MySQL 系列 锁大全

• 锁是数据库系统区别于文件系统的一个关键特性。数据库系统使用锁是为了支持对共享资源进行并发访问，提供数据的完成性和一致性，实现事务的隔离性要求。
• 一般 lock 的对象仅在事务 commit 或 rollback 后释放。此外，lock 有死锁机制。

锁的种类

根据加锁范围，MySQL锁大致可以分成全局锁、表级锁和行锁三类。全局锁、表级锁是server层的锁。行锁是存储引擎层的锁。

InnoDB 支持多粒度锁定，即允许事务在行级上锁和表级上锁同时存在。

全局锁（不重要）

全局锁就是对整个数据库实例加锁。MySQL提供了一个加全局读锁的方法，命令是Flush tables with read lock (FTWRL)。当需要让整个库处于只读状态的时候，可以使用这个命令，之后其他线程的以下语句会被阻塞：数据更新语句（DML，数据的增删改）、数据定义语句（DDL，包括建表、修改表结构等）和更新类事务的提交语句。

全局锁的典型使用场景是做全库逻辑备份。也就是把整库每个表都select出来存成文本。为了保证数据的一致性，需要存储一个不能变更的视图。如果有的表使用了不支持事务的引擎，那么备份就只能通过FTWRL方法。这往往是 DBA 要求业务开发人员使用 InnoDB 替代 MyISAM 的原因之一。 在InnoDB中，官方自带的逻辑备份工具是mysqldump。

• 当mysqldump指定参数--master-data会使用全局锁，获取GLOBAL.GTID_EXECUTED以及SHOW MASTER STATUS信息。
• 当mysqldump使用参数–single-transaction的时候，导数据之前就会启动一个事务，来确保拿到一致性视图。而由于MVCC的支持，这个过程中数据是可以正常更新的。

全局锁加锁过后，DML、DDL 语句都会被阻塞，主库执行加锁操作容易导致活跃会话过高，引发 MySQL 告警，从库执行加锁操作无法执行主库同步过来的 binlog，容易导致主从延时

备份过程不加全局锁有什么问题？不加锁的话，备份系统备份的得到的库不是一个逻辑时间点，这个视图是逻辑不一致的，不能满足数据的一致性。

表级锁

MySQL 里面表级别的锁有三种：一种是表锁，一种是元数据锁（meta data lock，MDL），还有自增锁 （Auto-Inc）。

表锁

表锁的语法是 lock tables ... read（共享锁）/write（排他锁）。可以用unlock tables主动释放锁，也可以在客户端断开的时候自动释放。

例如线程 A 执行 lock tables t1 read， t2 write 命令，则其他线程写 t1、读写 t2 的语句都会被阻塞。同时，线程 A 在执行 unlock tables 之前，也只能执行读 t1、读写 t2 的操作。连写 t1 都不允许，自然也不能在 unlock tables 之前访问其他表。

锁的对象是数据库的整张表，最常使用的 MyISAM 与 InnoDB 都支持表级锁定。表锁使用目的是禁止其他线程对特定表的写操作或者读写操作，保证自己线程对表的独占。

在还没有出现更细粒度的锁的时候，表锁是最常用的处理并发的方式。而对于 InnoDB 这种支持行锁的引擎，一般不使用 lock tables 命令来控制并发，毕竟锁住整个表的影响面还是太大。

需要强调的是，线程 A 获取表锁之后，其他线程写 t1、读写 t2 是被阻塞，而不是报错返回。如果业务在做表迁移或者 DB 的迁移，需要长时间禁写某几张表的操作。使用锁表的方式会导致活跃会话高的问题（因为其他写 t1、读写 t2 的线程只是阻塞，仍然是活跃状态）。

MDL

MDL 全称为 metadata lock，即元数据锁。MDL 锁主要作用是维护表元数据的数据一致性，在表上有活动事务（显式或隐式）的时候，不可以对元数据进行写入操作。因此从 MySQL5.5 版本开始引入了 MDL 锁，来保护表的元数据信息，用于解决或者保证 DDL 操作与 DML 操作之间的一致性。

• 当对一个表做增删改查操作的时候，加 MDL 读锁；读锁之间不互斥，因此可以有多个线程同时对一张表增删改查。
• 当要对表做结构变更操作的时候，加 MDL 写锁；读写锁之间、写锁之间是互斥的，用来保证变更表结构操作的安全性。因此，如果有两个线程要同时给一个表加字段，其中一个要等另一个执行完才能开始执行。

对于引入 MDL，其主要解决了 2 个问题，一个是事务隔离问题，比如在可重复隔离级别下，会话A在2次查询期间，会话B对表结构做了修改，两次查询结果就会不一致，无法满足可重复读的要求；另外一个是数据复制的问题，比如会话A执行了多条更新语句期间，另外一个会话B做了表结构变更并且先提交，就会导致slave在重做时，先重做alter，再重做update时就会出现复制错误的现象。

元数据锁是 server 层的锁，表级锁，每执行一条 DML、DDL 语句时都会申请 MDL 锁，DML 操作需要 MDL 读锁，DDL 操作需要 MDL 写锁（MDL 加锁过程是系统自动控制，无法直接干预，读读共享，读写互斥，写写互斥），申请 MDL 锁的操作会形成一个队列，队列中写锁获取优先级高于读锁。一旦出现写锁等待，不但写锁操作会被阻塞，同时还会阻塞后续该表的所有操作。事务一旦申请到 MDL 锁后，直到事务执行完才会将锁释放。由于客户端的重试，阻塞会话会越来越多。且 MDL 锁没有超时时间，活跃会话会一直飙高，以至于数据库卡死。

支持事务的 InnoDB 引擎表和不支持事务的 MyISAM 引擎表，都会出现 Metadata Lock Wait 等待现象。一旦出现 Metadata Lock Wait 等待现象，后续所有对该表的访问都会阻塞在该等待上，导致连接堆积，业务受影响。解决方案是事务尽快提交，减少对 MDL 锁的占用。DDL 尽量在低峰期操作，避免 MDL 锁冲突。

自增锁

在事务向包含了自增列的表中新增数据时就会去持有自增锁。使用目的是自增列不会重复。

行锁

MySQL 行锁是在引擎层由各个引擎自己实现的。但并不是所有的引擎都支持行锁，比如 MyISAM 引擎就不支持行锁。不支持行锁意味着并发控制只能使用表锁，对于这种引擎的表，同一张表上任何时刻只能有一个更新在执行，这就会影响到业务并发度。InnoDB 是支持行锁的，这也是 MyISAM 被 InnoDB 替代的重要原因之一。

根据两阶段提交协议，在 InnoDB 事务中，行锁是在需要的时候才加上的，但并不是不需要了就立刻释放，而是要等到事务结束时才释放。所以如果事务中需要锁多个行，要把最可能造成锁冲突、最可能影响并发度的锁尽量往后放。

锁的对象：InnoDB 行锁是通过给索引上的索引项加锁来实现的，这一点 MySQL 与 Oracle 不同，后者是通过在数据块中对相应数据行加锁来实现的。由于 MySQL 的行锁是针对索引加的锁，不是针对记录加的锁，所以虽然多个 session 是访问不同行的记录，但是如果是使用相同的索引键，是会出现锁冲突的（使用这些索引的 session 需要等待先使用索引的 session 释放锁后，才能获取锁）

执行计划使用了索引（主键索引或是二级索引）的增删查改都会产生行级锁。避免其他会话对某些记录进行修改，保证本会话事务的一致性。

行级锁是粒度最低的锁，发生锁冲突的概率也最低、并发度最高。但是加锁慢、开销大，容易发生死锁现象。

死锁是指两个或两个以上的事务在执行过程中，由于竞争资源而造成的一种阻塞的现象，若无外力作用，它们都将无法推进下去。根本原因就是事务间在持有部分资源的锁的同时，又要对对方持有的资源加锁，导致相互等待，MySQL 将主动检测这种情况并回滚其中一个事务，被回滚的事务将抛出异常：Deadlock found when trying to get lock; try restarting transaction。

InnoDB 存储引擎会在行级别上对表数据上锁。行级锁没有相关额外的开销，并可以同时得到并发性和一致性。

锁实现

共享锁（S）

共享锁，又称之为读锁，简称 S 锁，当事务 A 对数据加上读锁后，其他事务只能对该数据加读锁，不能做任何修改操作，也就是不能添加写锁。只有当事务 A 上的读锁被释放后，其他事务才能对其添加写锁。

共享锁主要是为了支持并发的读取数据而出现的，读取数据时，不允许其他事务对当前数据进行修改操作，从而避免”不可重读”的问题的出现。

使用方式：

1
2
3
lock table ... read 锁定某张表

select …lock in share mode  锁定某行

排他锁（X Lock）

排它锁，又称之为写锁，简称 X 锁，当事务对数据加上写锁后，其他事务既不能对该数据添加读写，也不能对该数据添加写锁，写锁与其他锁都是互斥的。只有当前数据写锁被释放后，其他事务才能对其添加写锁或者是读锁。

X 锁与任何锁都不兼容，S 锁仅与 S 锁兼容

使用方式：

1
2
3
lock table ... write 锁定某张表

select … for update  显示锁定某行

MySQL InnoDB 引擎默认 update，delete，insert 都会自动给涉及到的数据加上排他锁，select 语句默认不会加任何锁类型。写锁主要是为了解决在修改数据时，不允许其他事务对当前数据进行修改和读取操作，从而可以有效避免”脏读”问题的产生。

记录锁（Record）

记录锁是封锁记录，记录锁也叫行锁

使用方式：

• 记录排他锁 select *from t where `id`=1 for update
• 记录共享锁 select *from t where `id`=1 lock in share mode

使用目的：它会在 id=1 的记录上加上记录锁，以阻止其他事务更新，删除 id=1 这一行。

间隙锁(Gap)

间隙锁基于非唯一索引，锁的对象为记录行之间的间隙。使用间隙锁锁住的是一个索引区间。

跟间隙锁存在冲突关系的，是“往这个间隙中插入一个记录”这个操作。间隙锁之间都不存在冲突关系。

这里 session B 并不会被堵住。因为表 t 里并没有 c=7 这个记录，因此 session A 加的是间隙锁(5,10)。而 session B 也是在这个间隙加的间隙锁。它们有共同的目标，即：保护这个间隙，不允许插入值。但它们之间是不冲突的。

可重复读（RR）隔离级别下，比较容易产生间隙锁，增加锁等待以及死锁的冲突。

使用目的：避免幻读。简单的说，幻读指当用户读取某一范围的数据行时，另一个事务又在该范围内插入了新行，当用户再读取该范围的数据行时，会发现有新的“幻影” 行。 “可重读”这种隔离级别解决了上面例子中的问题，保证了同一事务内，多次查询的结果是一致的。

临键锁（next-key lock）

间隙锁和行锁组合合称 next-key lock，它的封锁范围，既包含索引记录，又包含索引区间，是一个左开右闭区间。next-key lock 实际上是由间隙锁加行锁实现的。

select * from x for update 与 select * from x lock in share mode 都会加上 next-key lock 锁，但分为排他临键锁和共享临键锁，区别在于行锁不同，for update会加 x lock，in share mode 会加 s 锁；

加锁规则: 原则1：加锁的基本单位是 next-key lock。next-key lock是前开后闭区间。 原则2：查找过程中访问到的对象都会加锁。

特殊优化: 优化1：索引上的等值查询，给唯一索引加锁的时候，next-key lock退化为行锁。 优化2：索引上的等值查询，向右遍历时且最后一个值不满足等值条件的时候，next-key lock退化为间隙锁。

一个bug：唯一索引上的范围查询会访问到不满足条件的第一个值为止。

可重复读（RR）隔离级别下，比较容易产生临键锁，增加锁等待以及死锁的冲突。如果把事务的隔离级别降级为 RC，临键锁则也会失效。

意向锁

意向锁分两种，一种是表锁，另一种是插入意向锁（不是表锁，类似于间隙锁）。

意向表锁

锁的对象：表

使用方式：表上有行锁时，表自动会被加意向锁。共享行锁 （S）对应的共享意向锁 （IS），排他行锁（X）对应的排他意向锁（IX）

使用目的：当有事务 A 有行锁时，MySQL 会自动为该表添加意向锁，事务 B 如果想申请整个表的写锁，那么不需要遍历每一行判断是否存在行锁，而直接判断是否存在意向锁，增强性能。

其支持两种意向锁，意向锁之间是互相兼容的:

• 意向共享锁（IS）: 事务有意向对表中的某些行加共享锁（S锁）
• 意向排它锁（IX）: 事务有意向对表中的某些行加排他锁（X锁）

-InnoDB 存储引擎支持意向锁设计比较简单，其意向锁即为表级别的锁。** -由于 InnoDB 存储引擎支持的是行级别的锁，因此意向锁其实不会阻塞除全表扫描以外的任何请求。因为全表扫描需要锁表** -意向锁不会与行级的共享/排他锁互斥** -意向锁是由存储引擎维护的，用户无法手动操作意向锁，在为数据行加共享/排他锁之前，InooDB 会先获取该数据行所在表对应的意向锁。

插入意向锁

插入意向锁是一种间隙锁形式的意向锁，在真正执行 insert 操作之前设置。当执行插入操作时，总会检查当前插入操作的下一条记录（已存在的主索引节点）上是否存在锁对象，判断是否锁住了 gap，如果锁住了，则判定和插入意向锁冲突，当前插入操作就需要等待；

锁的对象：记录行之间的间隙

使用方式：insert into t （…) values (…)

使用目的：配合上面的间隙锁或者临键锁一起防止了幻读操作。

插入意向锁

为什么会存在插入意向锁？

由于多个间隙锁可以共存，插入记录需要加锁时，如果直接使用间隙锁，一个事务锁住了某个间隙，其它事务执行 INSERT 语句还可以插入记录到该间隙中，也就违背了间隙锁用于实现可重复读这一特点了。为了解决这个问题，InnoDB 引入了插入意向锁。

插入意向锁和其它锁的关系

• 间隙锁会阻塞插入意向锁
• 插入意向锁不会阻塞间隙锁
• 插入意向锁相互之间不会阻塞

综合对比

可重复读 （RR） 隔离级别下 select* from t where id between 1 and 10 for update 会上临键锁，但是读已提交 （RC） 隔离级别下，该 SQL 只会上记录锁。

相关概念

阻塞

因为不同锁之间的兼容性关系，在有些时刻一个事务中的锁需要等待另一个事务中的锁释放它锁占用的资源，这就是阻塞。 在默认情况下，InnoDB 存储引擎不会回滚超时引发的错误异常，需要用户判断是否需要 COMMIT 或 ROLLBACK。

死锁

死锁是指两个或两个以上的事务在执行过程中，因争夺锁资源而造成的一种相互等待的现象。

解决死锁问题的方法

• 超时：当两个事务相互等待时，当一个等待时间超过设置的某一阈值时，其中一个事务进行回滚，另一个等待的事务就能继续进行。
• 主动死锁检测: 采用 wait-for graph 等待图的方式进行死锁检测，更为主动的进行死锁检查。在每个事务请求锁并发生等待时都会判断是否存在回路，如存在则有死锁。通常 InnoDB 存储引擎选择回滚 undo 量最小的事务。

InnoBD 不会回滚大部分的错误异常，但死锁除外

第一种策略中超时时间可以通过参数innodb_lock_wait_timeout来设置，默认值是50s。对于在线服务来说，这个等待时间往往是无法接受的。

第二种策略可以通过innodb_deadlock_detect参数进行开启，默认值本身就是 on。主动死锁检测在发生死锁的时候，是能够快速发现并进行处理的，但是它也是有额外负担的。

锁升级

将当前锁的粒度降低。

InnoDB 不存在锁升级的问题。因为其不是根据每个记录来产生行锁的，相反，其根据每个事务访问的每个页对锁进行管理的，采用了位图的方式。 因此不管一个事务锁住页中一个记录还是多个记录，其开销通常都是一致的。

命令

1
show engine innodb status                        // 查看当前锁请求的信息

1
select * from information_schema.INNODB_TRX\G    // 查询当前运行的InnoDB事务

1
select * from information_schema.INNODB_LOCKS\G  // 查看锁信息

1
select * from information_schema.INNODB_LOCK_WAITS\G // 查看当前事务的等待

1
show processlist; or show full processlist;         //  参看当前运行的事务

参考文献

介绍 Latch 锁 介绍 MySql InnoDB 中的三种行锁 介绍 MySql InnoDB 中意向锁的作用 抱歉，没早点把这么全面的InnoDB锁机制发给你 深入理解MDL元数据锁

• Previous
  MySQL 系列 事务概念篇
• Next
  分布式 系列 Gossip协议