深入剖析MySQL并发优化与InnoDB锁的设计原理

引言

我通过研究大量关于MySQL锁的资料，整理出以下内容，期望能为你提供助力。

锁是计算机协调多个进程或线程并发访问某一资源的机制。在数据库里，除了传统计算资源（CPU、RAM、I/0）的争用外，数据也是众多用户共享的资源。怎样保证数据并发访问的一致性、有效性是所有数据库都得解决的问题，而锁冲突是影响数据库并发访问性能的关键因素之一。从这个层面来讲，锁对于数据库至关重要且更为复杂。

MySQL中的锁，按锁的粒度划分，有以下三类：
1. 全局锁：对数据库中的所有表进行锁定
2. 表级锁：每次操作锁定整张表
3. 行级锁：每次操作锁定对应的行数据

不管是工作中还是学习时，大家都会涉及到MySQL的锁，本篇文章重点探讨MySQL的表级锁和行级锁。

1 全局锁

全局锁就是给整个数据库实例加锁，加锁后整个实例处于只读状态，后续的DML写语句、DDL语句以及更新操作的事务提交语句都会被阻塞。

它典型的应用场景是进行全库的逻辑备份，通过锁定所有表来获取一致性视图，保障数据的完整性。

基本操作：
使用全局锁：

FLUSH TABLES WITH READ LOCK;

释放全局锁：

UNLOCK TABLES;

备份数据库内容：

mysqldump -h -u -p... 数据库名 [表名1] [表名2] > 备份的位置  -- 不建议直接这么使用
-- 示例
mysqldump -h localhost -u root -p 123456 learn_mysql emp dept > G:/emp_copy.sql

不加锁的一致性备份，添加参数：--single-transaction

mysqldump --single-transaction  -h localhost -u root -p 123456 emp > G:/emp_copy.sql

演示图：


[外链图片转存中…(img-WTeWnbcN-1750691249184)]
在数据库中加全局锁是一个比较重的操作，存在以下问题：
1. 如果在主库上进行备份，那么备份期间无法执行更新操作，业务基本得暂停
2. 如果在从库上进行备份，那么备份期间从库不能执行主库同步过来的二进制日志（binlog），会导致主从延迟

解决办法：在InnoDB引擎中，备份时加上参数--single-transaction来实现不加锁的一致性数据备份（仅适用于支持「可重复读隔离级别的事务」的存储引擎）

mysqldump --single-transaction  -h localhost -u root -p 123456 emp > G:/emp_copy.sql

原理补充：通过添加这个参数，确保在备份开始时创建一个一致性的快照，通过启动一个新的事务来达成这一点。该事务的隔离级别是Repeatable Read级别，从而使得在该事务读取时一直读取的是创建时的数据，不影响其他事务的读写操作。要是不是RR级别，就会出现问题，比如要备份两个表，一个是余额表，一个是购买成功的订单表，当备份余额表时用户进行了购买操作，备份订单表时用户就好像不用花钱买东西了，造成数据不一致。

2 表级锁

每次操作锁定整张表，锁定粒度大，发生锁冲突的概率最高，并发度最低。所以全局锁的场景对于Java开发人员来说很少遇到，更多的是表级锁和行级锁。讲到这儿，就能明白为什么会有表级锁和行级锁（都是为了降低锁的粒度，提升并发度）。

接着讨论表级锁，表级锁包含表级读写锁、元数据锁、意向锁以及InnoDB引擎的AUTO-INC锁。其中表级读锁和表级写锁可以由用户手动加锁，元数据锁和意向锁用户无法自动加锁。

2.1 表级读锁 / 表级写锁

对于表锁，分为两类：
1. 表共享读锁（read lock），会阻塞其他客户端的写操作，不阻塞读操作

LOCK TABLES 表名 READ;

1. 表独占写锁（write lock），会阻塞其他客户端的写和读操作

LOCK TABLES 表名 WRITE;

读锁不会阻塞其他客户端的读，但会阻塞写。写锁既会阻塞其他客户端的读，又会阻塞其他客户端的写。

表锁不支持重入：无法在同一会话中多次获取相同的表锁

表锁不支持从写锁降级为读锁：必须先释放写锁，然后单独获取读锁

表锁不支持从读锁升级为写锁：必须先释放读锁，然后单独获取写锁

-- 表级别的共享锁，也就是读锁
-- 允许当前会话读取被锁定的表，但阻止其他会话对这些表进行写操作
LOCK TABLES emp READ;

-- 表级锁的独占锁，也是写锁
-- 允许当前会话对表进行读写操作，但阻止其他会话对这些表进行任何操作（读或写）
LOCK TABLES emp WRITE;

释放所有锁（会话退出，也会释放所有锁）：

UNLOCK TABLES;

2.2 元数据锁（MDL）

元数据锁（Metadata Lock ）是server层的锁，表级锁，每执行一条DML、DDL语句时都会申请MDL锁，DML操作需要metadata的读锁，DDL操作需要metadata写锁，metadata加锁过程是系统自动控制，无法直接干预

MDL加锁过程由系统自动控制，无需显式使用，访问一张表时会自动加上。MDL锁主要作用是维护表元数据的数据一致性，在表上有活动事务时，不可以对元数据进行写入操作。为避免DML与DDL冲突，保证读写的正确性。
1. 对一张表进行CRUD操作时，加的是MDL读锁；
2. 对一张表做结构变更操作的时候，加的是MDL写锁；

查看元数据锁：

SELECT object_type,object_schema,object_name,lock_type,lock_duration FROM performance_schema.metadata_locks;

在没有元数据之前，MySQL存在一个bug：会话A执行多条更新语句期间，另一个会话B进行表结构变更且先提交，会导致slave在重做时先重做alter，再重做update出现复制错误。

又比如：当前事务级别为RR，session1有事务正在查询数据，此时另一个会话session2进行DDL语句修改表结构，那么在session1的事务中，即使是RR级别，两次查询结果也会不一致，无法满足可重复读要求。5.1之前对元数据的保护是语句级别的（语句执行完毕，无论事务是否回滚或提交，对其他会话可见），5.5之后对元数据的保护是事务级别的，MDL主要解决两个问题：
1. 事务隔离问题： 元数据的保护范围扩展到事务级别，解决RR级别的一个事务中可能出现两次查询不一致问题
2. 数据复制问题： 比如会话A执行多条更新语句期间，另一个会话B做表结构变更且先提交，导致slave重做时先重做alter，再重做update出现复制错误

2.3 意向锁（IS/IX）

为避免DML执行时行锁与表锁冲突，InnoDB引入意向锁，使表锁不用检查每行数据是否加锁，通过意向锁减少表锁检查。

意向共享锁和意向独占锁是表级锁，不会与行级的共享锁和独占锁冲突，且意向锁之间也不冲突，只会与共享表锁（lock tables … read)和独占表锁（lock tables … write）冲突。
1. 如果没有「意向锁」，加「独占表锁」时需遍历表里所有记录查看是否有记录存在独占锁，效率低
2. 有了「意向锁」，对记录加独占锁前先加表级别的意向独占锁，加独占表锁时查该表是否有意向独占锁，若有则知表里已有记录被加独占锁，不用遍历记录

意向锁目的是快速判断表里是否有记录被加锁

意向锁之间互相兼容

| IS 锁| IX 锁
---|---|---
IS 锁| 兼容| 兼容
IX 锁| 兼容| 兼容

意向锁和共享锁、排它锁互斥（指表级别的共享锁和排他锁，意向锁不与行级的共享锁和排他锁互斥）

加锁方式：
1. IS : 意向共享锁（先在表上加上意向共享锁，然后对读取的记录加共享锁）

SELECT ... LOCK IN SHARE MODE;

1. IX : 意向独占锁（先表上加上意向独占锁，然后对读取的记录加独占锁）

INSERT / UPDATE / DELETE / (SELECT ... FOR UPDATE)

-- 示例
START TRANSACTION;
-- 查找某账户的余额并加锁，以便后续更新
SELECT salary FROM emp WHERE id = 1 FOR UPDATE;
-- 执行操作，如修改余额
UPDATE emp SET salary = salary - 1000 WHERE id = 1;
COMMIT;

查看意向锁：

SELECT object_schema, object_name, index_name, lock_type, lock_mode, lock_data FROM performance_schema.data_locks;

2.4 AUTO-INC 锁

表中的主键值会自动增加，通过对主键字段加AUTO_INCREMENT属性实现。之前插入新记录可不指定主键值，数据库自动生成主键值增加的值，主要通过AUTO_INCREMENT锁实现。不仅是自增主键，AUTO_INCREMENT的列都会涉及自增锁。

AUTO_INCREMENT锁是特殊的锁，不是事务提交后才释放，而是执行完插入语句后立即释放。

插入数据时，会加一个表级别的AUTO_INCREMENT锁，然后释放AUTO_INCREMENT锁。 一个事务持有AUTO_INCREMENT锁期间，其他事务向表格插入操作会影响主键值自增进程。但AUTO_INCREMENT锁在大量数据插入时影响插入性能，因为其他事务插入会被阻塞。因此，MySQL 5.1.22版本以后，InnoDB存储引擎提供轻量级锁实现自增。

就像插入数据时涉及AUTO_INCREMENT锁修改，但后续字段值自增值生成不需要等待整个插入语句执行完释放锁。

InnoDB存储引擎提供innodb_autoinc_lock_mode系统变量控制选择不同AUTO_INCREMENT锁，常用的锁：
1. innodb_autoinc_lock_mode = 0时，默认采用AUTO_INCREMENT锁，语句执行结束后释放锁；
2. innodb_autoinc_lock_mode = 1时，采用轻量级锁，需等待其他执行完后释放；
3. innodb_autoinc_lock_mode = 2时，采用混合模式
- 普通insert语句，自增锁申请后马上释放；
- 类似insert…select这样的批量插入数据语句，自增锁要等语句结束后才释放；

innodb_autoinc_lock_mode=2时性能最高，但搭配binlog日志格式为statement时，主从复制场景会发生数据不一致问题。

session A往表t插入4行数据，创建相同结构表t2后往t2插入数据。

若innodb_autoinc_lock_mode = 2，即“申请自增主键后就释放锁，不必等待整个插入语句执行完”，可能出现：
- session B先插入两条记录(1,1), (2,2)；
- session A申请id=3，插入(3,5)；
- session B继续执行，插入两条记录(4,3), (5,4)

可见sessionB的insert语句生成id不连续。主库发生这种情况，binlog记录sessionA和sessionB的insert语句，若binlog_format=statement，记录顺序可能先记sessionA或sessionB的语句。从库按顺序执行语句，不会出现主库同时执行向表t2插入数据场景，备库执行sessionB的insert语句生成结果id连续，主从库数据不一致。解决此问题，binlog日志格式设为row，binlog记录主库分配自增值，从库执行时主库自增值即从库自增值。所以innodb_autoinc_ock_mode=2且binlog_format=row，既能提升并发性，又无数据一致性问题。

3 行级锁

行级锁每次操作锁定对应行数据，锁定粒度最小，发生锁冲突概率最低，并发度最高，应用在InnoDB存储引擎中。
1. 记录锁(Record Lock): 锁定单个行记录，防止其他事务对该行进行update和delete，RC、RR隔离级别都支持，InnoDB数据基于索引组织（聚集索引），记录锁通过对索引上索引项加锁实现，非对记录加锁
2. 间隙锁(Gap Lock): 锁定索引记录间隙（不含该记录），确保索引记录间隙不变，防止其他事务在该间隙插入，产生幻读，RR隔离级别支持
3. 临键锁(Next-Key Lock): 行锁和间隙锁组合，同时锁数据和数据前面的间隙Gap，RR隔离级别支持

InnoDB三种行锁特点：
1. Record Lock（记录锁）：锁某一行记录
2. Gap Lock（间隙锁）：锁一段左开右开区间
3. Next-key Lock（临键锁）：锁一段左开右闭区间

加锁规则两条核心：
1. 查找过程中访问到的对象才会加锁（注意是基于B+树访问规则）
2. 加锁基本单位是Next-key Lock，按规则逐步退化

通过select * from performance_schema.data_locks;和操作实践，可见LOCK_MODE和LOCK_DATE关系：
LOCK_MODE| LOCK_DATA| 锁范围
---|---|---
X,REC_NOT_GAP| 15| 15 那条数据的行锁
X,GAP| 15| 15 那条数据之前的间隙，不包含 15
X| 15| 15 那条数据的间隙，包含 15

1. LOCK_MODE = X是前开后闭区间；
2. X,GAP是前开后开区间（间隙锁）；
3. X,REC_NOT_GAP行锁。

3.1 Record Lock（行锁）

Record Lock称记录锁，锁一条记录，有S锁和X锁之分。

InnoDB实现两种行锁：
1. 共享锁(S)：允许事务读一行，阻止其他事务获相同数据集排它锁
2. 排他锁(X)：允许获取排他锁事务更新数据，阻止其他事务获相同数据集共享锁和排他锁

| S(共享锁)| X(排他锁)
---|---|---
S(共享锁)| 兼容| 冲突
X(排他锁)| 冲突| 冲突

行锁类型：
SQL| 行锁类型| 说明
---|---|---
insert，update，delete …| 排他锁| 自动加锁
select| 不加任何锁|
select … lock in share mode| 共享锁| 需手动select后加lock in share mode
select … for update|