深度剖析MySQL锁：解开数据库并发控制的神秘面纱

本文链接：https://blog.csdn.net/qq_39209927/article/details/137109641

MySQL 锁是 MySQL 数据库管理系统中为了实现并发控制和数据一致性的机制。在多用户并发访问数据库时，锁可以确保多个事务在对同一数据进行操作时不会相互干扰，以防止数据不一致的现象发生。

一、锁分类

MySQL支持多种类型的锁，主要包括全局锁、表锁、行锁、元数据锁、自增长锁等。

1.1 全局锁

全局锁顾名思义是对整个数据库加锁，MySQL提供了加全局锁的方法，命令是Flush tables read lock(FTRL)，整个库处于只读状态，之后其他线程的以下语句都会被阻塞：数据更新语句、数据定义语句和更新类事务提交语句。全局锁使用场景，做全库备份，不过让全库只读，听上去很危险。

在 MySQL 中，全局锁是对整个 MySQL 实例的锁定，而非对单个数据库或表的锁定，因此在高并发的生产环境中，除非必要，一般不建议长时间持有全局锁，因为它会严重影响数据库的并发性能。在 InnoDB 存储引擎中，全局锁主要用于 MyISAM 表，而对于 InnoDB 表，通常采用其他机制如行级锁和表级锁来管理并发控制。

1.2 表级锁

表级锁，MySQL的表级锁有两种，一种是表锁，一种是元数据锁。

表锁使用unlock tables主动释放锁，也可以在客户端断开时自动释放。

元数据锁(MDL)不需要显示的声明，在访问一个表时会自动加上，MDL 的作用是，保证读写的正确性。你可以想象一下，如果一个查询正在遍历一个表中的数据，而执行期间另一个线程对这个表结构做变更，删了一列，那么查询线程拿到的结果跟表结构对不上，肯定是不行的。

在 MySQL 5.5 版本中引入了 MDL，当对一个表做增删改查操作的时候，加 MDL 读锁；当要对表做结构变更操作的时候，加 MDL 写锁。

我们可以看到 session A 先启动，这时候会对表 t 加一个 MDL 读锁。由于 session B 需要的也是 MDL 读锁，因此可以正常执行。之后 session C 会被 blocked，是因为 session A 的 MDL 读锁还没有释放，而 session C 需要 MDL 写锁，因此只能被阻塞。

如果只有 session C 自己被阻塞还没什么关系，但是之后所有要在表 t 上新申请 MDL 读锁的请求也会被 session C 阻塞。前面我们说了，所有对表的增删改查操作都需要先申请 MDL 读锁，就都被锁住，等于这个表现在完全不可读写了。

如果某个表上的查询语句频繁，而且客户端有重试机制，也就是说超时后会再起一个新 session 再请求的话，这个库的线程很快就会爆满。你现在应该知道了，事务中的 MDL 锁，在语句执行开始时申请，但是语句结束后并不会马上释放，而会等到整个事务提交后再释放。

另外 MySQL 支持的 Online DDL 也是通过 MDL 锁来实现的。

Online DDL 执行流程

MDL 写锁获取：在执行 Online DDL 之初，MySQL会为待修改的表获取 MDL 写锁，这会阻止其他会话对同一张表进行 DDL 操作，同时在最开始阶段也会阻止 DML 操作，确保在结构变更开始时数据的一致性。
数据拷贝的 DML 降级：根据 DDL 的具体类型，MySQL 可能创建一个临时表或者在原地修改表结构。随后 MDL 写锁会被降级成 MDL 读锁或更低级别的锁，使得在大部分 DDL 执行过程中，对原表的读操作可以继续进行，部分兼容写操作也可以在限定条件下并发执行。
数据迁移或结构变更：对于In-place DDL（原地数据定义语言操作）操作，MySQL 会创建必要的辅助结构（如临时表空间或Row Log，用于记录DML操作），并在原表数据的基础上进行结构变更。对于 Copy-based DDL(基于拷贝的DDL)操作，MySQL 会创建一个新表结构并将旧表的数据逐步迁移到新表结构中，同时维护一份 Row Log 记录在拷贝过程中发生的 DML 操作。
并发控制与 Row log 应用：在数据迁移过程中，InnoDB 会使用行级锁和/或间隙锁来保证并发 DML 操作的正确性，并将这些操作记录在 Row Log 中。在 DDL 操作即将完成时，MySQL 会应用 Row Log 中的增量数据，确保所有并发的 DML 操作都被正确地反映在最终的新结构上。
切换与清理：完成数据迁移后，MySQL 会将旧表替换为新表（如果是copy-based操作）或完成原地结构变更（如果是in-place操作），并在所有 DML 操作均已完成的情况下，解除MDL 锁。
提交事务与释放锁：最终，整个 DDL 操作被视为一个事务，当所有步骤完成后提交事务，释放 MDL 锁，使得其他会话可以正常执行 DDL 和 DML 操作。

1.3 行锁

行锁，存储引擎层由各个引擎实现的，并不是所有的引擎都支持的。行锁就是针对数据表中行记录的锁。在 InnoDB 事务中，行锁是在需要时才加上的，但并不是不需要了就立即释放，而要等到事务结束了才释放。

知道了这个设定，我们在日常工作中，如果你的事务需要锁多个行，要把最可能造成冲突、最可能影响并发度的锁尽量往后放。

InnoDB 存储引擎有两种标准的行级锁：

共享锁（S Lock），允许事务读一行数据
排它锁（X Lock），允许事务删除或更新一行数据

如果一个事务 T1 已经获得了行 r 的共享锁，那么另外的事务 T2 可以立即获得行r的共享锁，因为读取并没有改变行 r 的数据，称这种情况为锁兼容。但若其他的事务 T3 想获取行r的排他锁，则必须等待事务 T1、T2 释放行r的共享锁，这种情况称为锁不兼容。

1.4 自增长锁

在 InnoDB 存储引擎的内存结构中，对每个含有自增值的表都有一个自增长计数器。插入操作会依据这个自增长器加1赋值自增长列。这个实现方式称为 AUTO-INC Locking。这种锁其实采用一种特殊的表锁机制，为了提高插入性能，锁不是在一个事务完成后才释放，而是在完成对自增长值插入的 sql 语句后立即释放。

二、锁升级问题

情况一：不走索引

MySQL 行锁只能加在索引上，如果操作不走索引，就会升级为表锁。因为 InnoDB 的行锁是加在索引上的，如果不走索引，自然就没法使用行锁了，原因是 InnoDB 是将 primary key index 和相关的行数据共同放在 B+ 树的叶节点。InnoDB 一定会有一个 primary key，secondary index 查找的时候，也是通过找到对应的 primary，再找对应的数据行。

情况二：普通非唯一索引区分度太低

当非唯一索引上记录数超过一定数量时，行锁也会升级为表锁。测试发现当非唯一索引相同的内容不少于整个表记录的二分之一时会升级为表锁。因为当非唯一索引相同的内容达到整个记录的二分之一时，索引需要的性能比全文检索还要大，查询语句优化时会选择不走索引，造成索引失效，行锁自然就会升级为表锁。

三、MVCC

MySQL 的多版本并发控制（MVCC, Multi-Version Concurrency Control）是一种为了解决并发读写问题，提高数据库并发性能的机制。在支持 MVCC 的存储引擎中，最典型的是InnoDB。

3.1 一致性非锁定读

指 InnoDB 存储引擎通过多版本控制的方式读取当前执行时数据库中的行数据。如果读的时候正在执行delete或update操作，这时读取操作不会因此去等待行上的锁释放。相反的，InnoDB存储引擎会读取行的一个快照数据。

可以看到，非锁定读机制极大的提高了数据库的并发性。在 InnoDB 存储引擎的默认设置下，这是默认的读取方式，即读取不会占用和等待表上的锁。

一个行记录可能不止一个快照数据，一般称这种技术为行多版本技术。由此带来的并发控制，称之为多版本并发控制（Multi Version Concurrency Control, MVCC）。

不仅 MySQL，其他数据库都实现了MVCC，只是实现的方式不同，MVCC 没有统一的实现标准。MVCC 避免了加锁操作，开销较低。

在事务隔离级别 READ COMMITTED 和 REPEATABLE READ 下，InnoDB 存储引擎使用非锁定的一致性读。在 READ COMMITTED 隔离级别下，对于快照，非一致性读总是读取被锁定行的最新一份快照。而在 REPEATABLE READ 隔离级别下，对于快照，非一致性读总是读取事务开始时的行数据版本。