MySQL有哪些锁

<span style="margin: 5px">今天来说说MySQL中有哪些锁，在MySQL中的锁根据锁的粒度可以分为全局锁、表级锁和行级锁。</span>(PS:本文例子所使用的表及数据在文末提供。)

全局锁

全局锁开启和释放

下面SQL语句是操作全局锁

# 开启全局锁
flush tables with read lock
# 释放全局锁
unlock table

开局全局锁后整个数据库就变成了只读状态，对表数据的增删改操作和表结构的增删改操作都会阻塞。

全局锁的应用场景

全局锁适用于对整个数据库进行一致性操作或者维护任务的场景，比如数据备份、数据库迁移等。

全局锁的缺点

因为开启全局锁后整个数据库处于只读状态，所以会导致整个业务处于停滞状态。那么要如何避免这个问题呢，因为MySQL中的InnoDB支持可重复读的事务隔离级别，可以事先创建一个快照读，这样整个事务期间就可以重复利用这个快照读了，使用MySQL自带的逻辑备份工具mysqldump并且加上-single-transaction参数就可以实现了。

表级锁

> MySQL表级锁有哪些

• 表锁
• 意向锁
• 元数据锁
• AUTO-INC Locks

表锁

表锁可以使用以下命令来开启和释放

# 开启共享锁 即读锁
lock tables table_name read;
# 开启独占锁 即写锁
lock tables table_name write;
# 解锁
unlock tables;

共享锁可以同时被多个事务持有，但是写锁不行，并且不支持读锁升级成写锁。也就是说当事务A持有读锁，此时再想获得写锁，会被阻塞。

lock tables user read;
# 获取写锁时会被阻塞
lock tables user write;

元数据锁

元数据锁用于保证数据的一致性，对表操作时有两种加锁情况：

• 对表做CRUD操作时会加元数据的读锁
• 对表结构进行修改会加元数据写锁

元数据锁不需要显示的调用，也不需要显示的释放，在事务提交之后会自动提交

元数据锁的优先级中，写锁会比读锁的优先级更高，引用官方文档的原话：Write lock requests have higher priority than read lock requests.，对于同样都是写锁会按照字典序的排名来获取元数据锁的顺序，从而达到减少死锁发生的目的。以官方文档中的例子为例：

# 1
rename table tbla to tbld, tblc to tbla;
# 2
rename table tbla to tblb, tblc to tbla;

对于第一条SQL，获取锁的顺序是 tbla->tblc->tbld，因为a > c > d，同理可得第二条SQL获取锁顺序tbla -> tblb -> tblc。因为锁的顺序是固定的，所以减少了死锁的可能性。

上面的例子写锁和写锁，如果是读锁和写锁的话，在长事务中某些情况会导致数据库线程阻塞的情况。比如下面这种场景：

1. 事务A(关闭自动提交)中执行一条select语句，这个时候会加上MDL读锁
2. 事务B执行了一模一样的select语句，这个时候因为读读共存可以正常查询
3. 事务C对表结构做了修改，但是因为事务A没有提交，MDL读锁不释放，事务C会一直阻塞，并且其他事务的select语句也会一直被阻塞

因为申请MDL锁会优先申请写锁，事务C无法获得写锁一直阻塞，导致整个表的CRUD操作都被阻塞了。

验证不同语句下MetaData Lock状态

begin;
select * from user where id = 1;
SELECT * FROM performance_schema.metadata_locks;

select
l.object_schema 数据库名,
l.object_type 对象类型,
l.object_name 对象名称,
l.lock_type 锁类型,
l.lock_duration 持续类型,
l.lock_status 锁状态,
l.owner_thread_id 线程ID,
t.processlist_id 会话ID,
s.sql_text
from performance_schema.metadata_locks l
join performance_schema.threads t on t.thread_id=l.owner_thread_id
join performance_schema.events_statements_current s on s.thread_id=l.owner_thread_id
where l.object_schema='learn'and l.object_name='user';

意向锁

意向锁是MySQL InnoDB用来协调行级锁和表级锁的，意向锁分为意向排他锁和意向共享锁

• 当一个事务准备在某些行上加共享锁时会先在表级别上加一个意向共享锁
• 当一个事务准备在某些行上加独占锁时会先在表级别上加一个意向排他锁

增删改操作会先对表加上意向排他锁，然后再对记录加独占锁。select语句需要加上lock in shard mode或者for update才会加上意向锁。意向锁不会和行级锁的共享锁和独占锁冲突，但是会和共享表锁和独占表锁冲突。贴一下MySQL8.4官方文档的一段话

Intention locks do not block anything except full table requests (for example, LOCK TABLES ... WRITE). The main purpose of intention locks is to show that someone is locking a row, or going to lock a row in the table.

以下是意向锁与其他锁之间的兼容性矩阵：

意向锁的主要作用是提高MySQL的并发性能和锁的管理，比如说事务A给某张表的某条记录添加排他锁，假设没有意向锁，事务B想要给这张表加上一个表级别的共享锁，这个时候需要遍历所有的记录来判断是否有锁冲突，如果有意向锁，事务A在给记录上锁时就先加了意向锁，这个时候事务B只需要判断这张表是否有意向独占锁就行了。

总的来说意向锁的作用就是提供了一种提示的机制，为并发控制提供更好的信息，告诉其他事务当前事务会在表中某些行执行一些锁操作，防止其他事务对这些行进行锁操作，减少死锁的可能性！

AUTO-INC Locks

AUTO-INC Locks是一种针对表中设置为自增的列的特殊表级锁，下文中就称为自增锁。

在一个事务中，对一个包含了AUTO_INCREMENT列的表中新增数据时就会持有自增锁。假设事务A正在插入数据，那么事务A就会持有自增锁，这个时候如果事务B尝试执行insert语句，事务B就会阻塞住，直到事务A释放自增锁。这样可以保证自增字段是连续的，但是也损失了大量插入数据的性能。所以MySQL提供了一种轻量级锁来实现自增。

Innodb提供了一个innodb_autoinc_lock_mode的变量来控制自增的实现，不同的值代表不同的模式。

传统模式

当innodb_autoinc_lock_mode为0时就代表传统模式，这个时候对包含AUTO_INCREMENT列的表插入数据时就会持有特殊的表级锁自增锁，在插入语句执行完毕之后释放。这样可以保证自增字段是连续的，但是也损失了大量插入数据的性能。

连续模式

当innodb_autoinc_lock_mode为1时就代表连续模式，这种模式下对于事先能够确定的插入行数的SQL语句就不需要获取自增锁，在需要自增的列上先预先分配好自增值，分配好就可以执行下一个语句了。

一般来说普通的insert into语句和不嵌套子查询的replace into语句可以事先确定插入的行数。

CREATE TABLE products (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    price DECIMAL(10,2)
);
# 可以事先确定插入的行数
REPLACE INTO products (id, name, price)
VALUES
(1, 'Product A', 10.99),
(2, 'Product B', 15.99),
(3, 'Product C', 20.99);

交叉模式

当innodb_autoinc_lock_mode为2时就代表交叉模式，这种模式下所有的insert语句都不会使用自增锁，这是三种模式中性能最好的一种，多条语句可以并发的执行，不过对于单个的insert语句可能会导致自增值不连续。除此之外，交叉模式还会带来数据不一致性的问题。

交叉模式的缺陷

在了解交叉模式的缺陷之前，需要先简单了解一下MySQL中的binlog文件的三种格式，这三种模式分别是：

• Statement：基于语句，只记录对数据做了修改的SQL语句，可以有效减少binlog的数据量
• ROW：只记录被修改的行，优点是记录了所有的数据变动，缺点是binlog数据量大，比如update语句中，可能改动了所有的数据。
• Mixes：前两种的结合，怎么结合后面再单独开一篇介绍吧，先挖个坑。

如果MySQL使用了Statement格式，那么MySQL的主从同步就是记录了一条一条的SQL语句，那么在并发情况下就会出现主从数据不一致的问题，举个例子：

对于binlog来说，只会记录SQL语句，如果先记录事务A，再记录事务B，那么记录的SQL是这样的

# 事务A
INSERT INTO employees (name, salary) VALUES ('John Doe', 50000.00);
INSERT INTO employees (name, salary) VALUES ('Jane Smith', 60000.00);
# 事务B
INSERT INTO employees (name, salary) VALUES ('Alice Brown', 70000.00);

最终从库重放binlog生成的数据就会变成这样

如果确定不需要通过binlog做主从同步，可以设置为交叉模式来提高性能，否者就要设置成其他模式了，毕竟鱼和熊掌不可兼得。

行级锁

> MySQL的行级锁有哪些

• 记录锁
• 间隙锁
• 邻键锁
• 插入意向锁

MySQL中Innodb支持行级锁，MyISAM不支持，行级锁是满足读读共享、读写互斥、写写互斥的，普通的select语句是使用快照读，所以不会上锁，如果想要在select语句上锁，可以使用下面两种方式：

# 共享锁
select ... lock in share mode;
# 独占锁
select ... for update;

记录锁

记录锁是在索引记录上上锁的，比如select c1 from t where c1 = 10 for update;，这条语句会防止其他事务插入、更新、删除t.c1 = 10的行。当事务提交后会释放记录锁。

间隙锁

在Innodb存储引擎中，间隙锁是一种特别的锁，主要用于防止幻读问题的发生，间隙锁只在可重复读的事务隔离级别下生效。

begin;
select * from t_user where id= 3 for update;

上面会在id索引上加上(1, 5)的锁，表数据如下

+----+--------+------+---------------+
| id | name   | age  | string_number |
+----+--------+------+---------------+
|  1 | 路飞   |   19 | 12            |
|  5 | test   |   21 | 12            |
| 10 | 山治   |   22 | 10            |
| 15 | 乌索普 |   20 | 15            |
| 20 | 香克斯 |   39 | 20            |
| 22 | test   |   12 | 22            |
+----+--------+------+---------------+
6 rows in set (0.00 sec)

此外，间隙锁可以在多个事务中共存，比如事务A和事务B都执行了select * from t_user where id= 3 for update;，那么事务A和B都会持有(1, 5)的间隙锁。<span style="color: #d90808">即两个事务可以持有相同范围的独占间隙锁。</span>

Next-Key Locks

即是邻键锁，相当于记录锁和间隙锁的组合。

begin;
select * from t_user where id > 20 for update;

这条SQL会对(20, 22]和(22, 正无穷大)加间隙锁。因为邻键锁是包括记录锁和间隙锁的，所以不能像间隙锁那样相同范围可以被多个事务共同持有锁。

插入意向锁

一个事务在向表中插入一条数据时，会先判断插入位置是否被其他事务持有间隙锁，如果有的话会被阻塞，直到持有锁的事务释放锁为止，此时该事务会先生成一个插入意向锁，但是此时处于等待状态。在MySQL中如果锁状态是等待状态，仅仅表示生成了一个锁结构，并不代表事务获得锁。贴一下官方文档中的一段话：

A transaction may have any number of lock requests for different rows or tables. At any given time, a transaction may request a lock that is held by another transaction, in which case it is blocked by that other transaction. The requesting transaction must wait for the transaction that holds the blocking lock to commit or roll back. If a transaction is not waiting for a lock, it is in a RUNNING state. If a transaction is waiting for a lock, it is in a LOCK WAIT state. (The INFORMATION_SCHEMA INNODB_TRX table indicates transaction state values.)

插入意向锁其实也可以归为间隙锁的一种，官方文档中的Prior to inserting the row, a type of gap lock called an insert intention gap lock is set.，翻译过来大概意思就是在插入一行之前，会设置一种被称为插入意向间隙锁的间隙锁。

以下面SQL为例

# 事务A
begin;
select * from t_user where id > 20 for update;
# 事务B
insert into t_user (id, name, age, string_number)
values (21, 'test', 11, '11');
select * from performance_schema.data_locks;

事务B在执行插入语句时会生成一个插入意向锁，并且锁状态为waiting，可以用select * from performance_schema.data_locks;查看锁情况。

参考资料

• MySQL元数据锁
• Innodb锁
• Innodb对auto-increment的处理
• InnoDB锁定和锁定等待信息

本文所用到的表及数据

CREATE TABLE `user`  (
  `id` int(11) NOT NULL,
  `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
  `age` int(11) NULL DEFAULT NULL,
  `city` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE,
  INDEX `idx_name`(`name` ASC) USING BTREE,
  INDEX `idx_age`(`age` ASC) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of user
-- ----------------------------
INSERT INTO `user` VALUES (1, 'Emma', 20, '深圳');
INSERT INTO `user` VALUES (2, 'Liam', 36, '北京');
INSERT INTO `user` VALUES (3, 'Noah', 25, '广州');
INSERT INTO `user` VALUES (4, 'Jackson', 15, '上海');
INSERT INTO `user` VALUES (5, 'Sophia', 41, '北京');
INSERT INTO `user` VALUES (6, 'Aiden', 47, '北京');
INSERT INTO `user` VALUES (7, 'Isabella', 23, '广州');
INSERT INTO `user` VALUES (8, 'Lucas', 17, '深圳');
INSERT INTO `user` VALUES (9, 'james', 11, '广州');

# 交叉模式缺陷所用表
CREATE TABLE employees (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100),
    salary DECIMAL(10, 2)
);

create table t_user
(
    id            int         not null primary key,
    name          varchar(16) null,
    age           int         null,
    string_number varchar(5)  null comment '测试字段，验证类型转换索引失效问题'
);

INSERT INTO learn.t_user (id, name, age, string_number) VALUES (1, '路飞', 19, '12');
INSERT INTO learn.t_user (id, name, age, string_number) VALUES (5, 'test', 21, '12');
INSERT INTO learn.t_user (id, name, age, string_number) VALUES (10, '山治', 22, '10');
INSERT INTO learn.t_user (id, name, age, string_number) VALUES (15, '乌索普', 20, '15');
INSERT INTO learn.t_user (id, name, age, string_number) VALUES (20, '香克斯', 39, '201');
INSERT INTO learn.t_user (id, name, age, string_number) VALUES (22, 'test', 12, '22');