MySQL锁机制

MySQL 有多种存储引擎，每种存储引擎有各自的优缺点，较常出现的是 MyISAM 和 InnoDB，在 MySQL 5.1 之前的版本默认存储引擎是 MyISAM，之后的版本是 InnoDB。由于 MyISAM 存储引擎不支持数据库事务以及随着版本升级后默认存储引擎的切换，存储引擎的选择逐渐转向了 InnoDB。MySQL 在不同的存储引擎下有不一样的锁机制，如无特殊说明，本文的内容主要介绍 MySQL InnoDB 存储引擎下基于事务的锁机制。

执行事务相关的锁

按锁的范围分类

  • 行级锁：只持有指定行数据的锁。是颗粒度最细的一种锁，在并发情况下可减少不同数据的读写等待时间。缺点是加锁的开销较大。

  • 表级锁：对全表数据加锁。存在读写串行问题，并发情况下即使是不同数据仍需要读写等待时间。优点是实现简单，消耗的资源较少，大多数存储引擎支持。

在 MyISAM 存储引擎下，开始事务并加锁时，锁的等级是表级锁。而 InnoDB 提供了表级锁和行级锁，在可以确定扫描的范围的情况下是行级锁，不会影响同表其他数据的并发读写。但如果执行的锁的 SQL 语句无法确定要扫描的范(如没有WHERE/使用LIKE、IN等使索引失效的方法)，MySQL会将锁等级升级为表级锁。

按锁级别分类

所谓按锁级别分类，是在事务操作的过程中使用SQL语句主动加锁的类别。

  • 共享锁

    共享锁又称读锁，是执行读取操作创建的锁。当没有任何连接持有查询的数据集的排他锁时，就可以申请共享锁，否则会被阻塞。在加锁成功后，其他连接仍可以并发读取数据，但任何事务都不能对该数据集进行修改以及申请排他锁，直到所有的共享锁释放为止。

    1
    SELECT ... LOCK IN SHARE MODE;

  • 排他锁

    排他锁又称写锁，如果一个事务持有了数据 A 的排他锁，那么任何其他事务都不能再对数据 A 加任何类型的锁，持有该排他锁的事务可以读取数据 A，也可以修改数据 A。

    1
    SELECT ... FOR UPDATE;

    MySQL 会对查询命中的所有数据加上排他锁，在明确查询范围的情况下（如使用索引），申请锁的范围是行级锁，否则锁的等级会升级到表级锁（例如语句中没有 WHERE 限定范围，或者 WHERE 语句中包含 LIKE '%data%' 导致索引失效查询全表等）。

行级锁锁定的内容

InnoDB的行锁是依赖索引实现的，对于SQL语句中有唯一搜索条件时(如主键、唯一索引)，InnoDB 在加行级锁时会使用记录锁来锁定找到的索引记录。而对于其他搜索条件，InnoDB 会锁定扫描的索引范围，使用间隙锁或者临键锁来阻止其他事务插入范围覆盖的间隙。

  • 记录锁(Record Lock)

    锁定某行数据的本质是锁住行数据对应在聚簇索引的索引记录。所以记录锁锁定的是索引记录，执行以下SQL语句时，会对 id=1 的这条记录的索引记录上加锁，阻止其他事务对该行数据进行操作。

    SELECT id, name FROM table WHERE id = 1 FOR UPDATE;

  • 间隙锁(Gap Lock)

    间隙锁是索引记录之间间隙的锁，至少在RR的事务隔离级别才会生效。间隙锁的作用是不让其他事务在索引记录之间插入数据，其锁定的是一个不包含索引本身的开区间范围(index1, index2)，支持加锁的索引的范围从 -∞ 到 +∞。

    例如执行 SELECT ... FROM table WHERE id BETWEEN 10 and 20 FOR UPDATE 的SQL语句，当其他事务想要往表中写入id在 10 到 20 之间的值时，就会被这个间隙锁阻塞；此时写入或更新id=15的数据就会被阻塞，而 id=10 因为不在开区间的范围内并不会被锁定阻塞。

    多个间隙锁之间是可以共存的，可以存在重合区或者是完全重合，此时多个事务都想阻止其他事务往该索引间隙之间写入数据。

  • 临键锁(Next-key Lock)

    临键锁是记录锁和间隙锁的组合，因此临键锁和间隙锁一样至少在RR的事务隔离级别才会生效。其既锁住了索引本身，也锁住了索引记录之前间隙，组合起来构成了一个半开闭区间的锁范围，通常是左开右必，除了最后一个锁区间全部都为开区间。

    假设某张表有4行数据，主键id分别为10、11、13、20，那么该表可能存在临键锁锁住的聚簇索引区间如下：

    1
    2
    3
    4
    5
    (-∞,10]
    (10,11]
    (11,13]
    (13,20]
    (20,+∞)

    对于最后的间隙，+∞并不是真正的索引记录，只是为了锁定最大索引值之后的间隙。

行级锁加锁的原则：

  • 加锁的基本单位是临键锁，是一个前开后闭区间。
  • 查找过程中访问到的记录才会加锁。
  • 使用索引上的等值查询，给主键/唯一索引加锁时，临键锁退化为记录锁。
  • 索引上的等值查询，向右遍历时最后一条记录不满足等值条件的时候，临键锁退化为间隙锁。
  • 主键/唯一索引上的范围查询会扫描到不满足条件的第一个值为止。

举例：假设一张表中有主键id为0、5、10、15、20、25的记录

  1. UPDATE table SET a=a+1 WHERE id =7
    根据第一条原则，加锁单位是临键锁，加锁范围是 (5,10]，而 id=7 的等值查询遍历查询到间隙的最后一条记录后不满足等值条件，此时临键锁退化为间隙锁，最终加锁范围是 临键锁 (5,10)。

  2. SELECT ... FROM table WHERE id>=10 AND id<11 FOR UPDATE
    根据第一条原则，加锁单位是临键锁，会给 (5,10] 这个范围的数据加上临键锁，然后根据第五条原则继续范围扫描到 id=15 的记录停止。
    根据第三条原则，id>=10 的条件触发主键/唯一索引上的等值条件加锁，(5,10] 的临键锁退化为记录锁，只给满足条件的 id=10 的记录加上记录锁。
    根据第二条原则，访问到的记录都要加锁，因此范围扫描的 (10,15] 会被加上临键锁。
    最终加锁的范围是 记录锁 id=10 和 临键锁 (10,15]。

  3. SELECT ... FROM table WHERE id>10 AND id<=15 FOR UPDATE
    根据第一条原则，加锁单位是临键锁，会给 (10,15] 这个范围的数据加上临键锁。
    根据第五条原则，会继续进行范围扫描到 id=20 的记录才停止，因此 (15,20] 的范围也会被加上临键锁。
    最终加锁的范围是 临键锁(10,15]、(15,20]。

意向锁

在 InnoDB 存储引擎中，支持行级锁和表级锁。大部分情况下，行级锁和表级锁之间是冲突的无法同时获取，如果行级锁和表级锁同时直接请求获取，可能会导致相互阻塞死锁。但有时行锁和表锁又是可以同时获取的（例如行级共享锁+表级共享锁），为了快速获取锁以及防止直接获取锁导致冲突，实现行级锁和表级锁共存，MySQL引入了意向锁机制。

意向锁是一种不与行级锁冲突的特殊的表级锁，主要目的是为了展示某事务已对表加过锁，或者有即将对表进行加锁的意向，这是一种较弱的锁，意向锁之间是不会互斥的。意向锁的维护是由MySQL存储引擎隐式管理执行的，用户不需要也无法主动操作意向锁。

  • 意向共享锁

    表示事务即将/已经在数据上设置共享锁，不希望在事务执行期间有其他事务来获取排他锁。

  • 意向排他锁

    表示事务即将/已经在数据上设置排他锁，不希望在事务执行期间有其他事务来获取共享锁和排他锁。

乐观锁与悲观锁

  • 乐观锁

乐观锁适用于读多写少的情况，默认了事务对数据的操作不会有冲突，在执行更新事务操作时不主动显示的执行数据库的加锁操作，只在提交数据更新时才对数据是否变更冲突进行检测校验，发现冲突后返回更新失败再让用户决定该如何处理（重试或放弃）。通过CAS的机制来实现，通常会在列中定义定义如 version 版本号之类的额外字段，因为不主动显示的执行数据库的加锁操作，效率也稍高一些。

不过需要注意的是，乐观锁只是用户不主动显示的执行数据库加锁操作，实际上在执行 UPDATE 更新语句时，数据库会自动加上行级锁，如果没有主键/唯一索引，那么行级锁会升级为表级锁。相比起悲观锁，乐观锁最大的好处就是不需要提前加锁，只在数据更新的短暂瞬间加锁，极大的缩短持有锁的时间。

1
2
3
4
5
-- 先查询
SELECT ... FROM table WHERE id = 1;
-- 查询到数据 version=2021，执行业务逻辑，更新想要修改的目标列和 version=2022
-- 提交修改
UPDATE table SET ... ,version = 2022 WHERE id = 1 AND version = 2021;

如果这中间没有其他的事务来对这条数据进行，提交更新的时候，数据库通过对应的WHERE条件限制，如果更新失败了需要重新发起更新记录的请求或者放弃更新记录。如果写操作较多，更新失败的概率也会随之提升，如果不进行重试操作，返回业务失败会较频繁，在并发读写的情况下频繁进行重试操作也会对增加数据库的压力，此时乐观锁的效率反而会比悲观锁低。

  • 悲观锁

悲观锁依赖数据库锁，适用于写操作频繁且并发写入概率较高的场景。但处理加锁的机制会让数据库产生额外的开销，同时其他事务要等待该事务处理完才能处理该行数据，降低数据库的吞吐率。使用悲观锁需要手动开启事务，在查询、更新记录之前就先通过排他锁避免冲突，最后提交事务。

1
2
3
4
5
6
7
8
-- 手动开启事务
BEGIN
-- 查询数据后进行业务流程
SELECT ... FROM table WHERE id = 1;
-- 业务流程得到将要更新的数据
UPDATE table SET ... WHERE id = 1;
-- 提交事务
COMMIT;

锁定表

MySQL中提供了锁定表（lock tables）和解锁表（unlock tables）的功能，这种操作需要谨慎使用，会导致全表数据被锁定无法读写，稍有不慎就会演变成生产事故，通常是备份全表数据时为了保证数据一致性才使用。

1
2
3
4
5
6
-- 锁定表，对全部的连接仅允许读取数据，尝试更新会返回错误
LOCK TABLES table READ;
-- 锁定表，对当前连接允许读写，其他事务允许读但不允许写，尝试更新会返回错误
LOCK TABLES table WRITE;
-- 解锁表
UNLOCK TABLES;

字典锁

字典锁(MetaData Lock)是一种用于管理元数据的锁，而不是用于用户数据的锁，可以控制数据库元数据的并发访问。数据库在执行DDL操作时会加上字典锁，保护数据库元数据对象，如表、列、索引、视图等，确保在执行DDL操作期间不会出现数据一致性问题。

字典锁同样有共享锁、和排他锁两个级别，在DDL事务开始时，通常会以共享字典锁的方式访问元数据对象，在需要保证一致性的情况下才会升级为排他字典锁，阻止其他事务访问、修改相同的元数据对象。

触发字典锁的一些情况：

  1. 对表结构进行操作：创建表、修改表、删除表
  2. 对表的索引进行操作：创建索引、修改索引、删除索引
  3. 对表的列进行操作：改变数据类型、增加列、删除列、重命名列
  4. 对视图进行操作：创建视图、修改视图、删除视图
  5. 其他DDL操作：创建、修改、删除存储过程、自定义函数、触发器、事件