MySQL锁（读锁、共享锁、写锁、S锁、排它锁、独占锁、X锁、表锁、意向锁、自增锁、MDL锁、RL锁、GL锁、NKL锁、插入意向锁、间隙锁、页锁、悲观锁、乐观锁
本文说明的是MySQL锁，和操作系统或者编程语言的锁无关。
概念
作用：在并发情况下让数据正确的读写。 优点：并发情况下对数据读写可控，防止出错。 缺点：降低性能、增加难度。
分类
- 数据操作类型划分
- 读锁（共享锁、S锁）
- 写锁（排它锁、独占锁、X锁）
- 粒度划分
- 表级锁
- S锁、X锁
- 意向锁
- 自增锁
- 元数据锁
- 行级锁
- 记录锁
- 间隙锁
- 临键锁
- 插入意向锁
- 页级锁
- 表级锁
- 严格度划分
- 悲观锁
- 乐观锁
- 加锁方式
- 隐式锁
- 显示锁
- 其它
- 全局锁
- 死锁
测试用表
CREATE TABLE `cs` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`num1` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '数字列1',
`num2` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '数字列2',
`s1` varchar(2000) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '字符串列1',
`s2` varchar(2000) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '字符串列2',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
INSERT INTO `temp`.`cs` (`id`, `num1`, `num2`, `s1`, `s2`) VALUES (1, 1, 1, 'abc', 'xyz');
INSERT INTO `temp`.`cs` (`id`, `num1`, `num2`, `s1`, `s2`) VALUES (2, 2, 2, 'ABC', 'XYZ');
读锁、共享锁、S锁
读锁、共享锁、S锁（Share Lock）是一个东西。 锁的是：允许同时有多个事务，对数据只能读，不能写。 写法：
select ... lock in share mode;
select ... for share; #MySQL8
测试：
| 步骤 | 会话A | 会话B | 备注 |
|---|---|---|---|
| 1 | start transaction; | start transaction; | 双方开启事务 |
| 2 | select * from cs where id = 1 lock in share mode; | select * from cs where id = 1 lock in share mode; | 双方添加共享锁，都能成功添加 |
| 3 | commit; | commit; | 正常提交事务，无报错 |
写锁、排它锁、X锁、独占锁
写锁、排它锁、X锁（Exclusive Lock）、独占锁是同一个东西。 锁的是：仅允许同时有1个锁独占该事务，具有排它性，不允许其它任何类型的锁再占用该事务。
select ... for update;
测试： X锁排斥S锁1
| 步骤 | 会话A | 会话B | 备注 |
|---|---|---|---|
| 1 | start transaction; | start transaction; | 双方开启事务 |
| 2 | select * from cs where id = 1 lock in share mode; | select * from cs where id = 1 for update; | 会话A添加S锁，会话B添加X锁 |
| 3 | / | 阻塞 | 会话B添加X锁被阻塞，说明X锁有排它性 |
| 4 | commit; | / | 会话B X锁添加成功 |
| 5 | / | commit; | 会话B提交事务，结束流程 |
X锁排斥S锁2
| 步骤 | 会话A | 会话B | 备注 |
|---|---|---|---|
| 1 | start transaction; | start transaction; | 双方开启事务 |
| 2 | select * from cs where id = 1 for update; | select * from cs where id = 1 lock in share mode; | 会话A添加X锁，会话B添加S锁 |
| 3 | / | 阻塞 | 会话B添加S锁被阻塞，说明X锁有排它性 |
| 4 | commit; | / | 会话B S锁添加成功 |
| 5 | / | commit; | 会话B提交事务，结束流程 |
X锁排斥X锁
| 步骤 | 会话A | 会话B | 备注 |
|---|---|---|---|
| 1 | start transaction; | start transaction; | 双方开启事务 |
| 2 | select * from cs where id = 1 for update; | select * from cs where id = 1 for update; | 双方添加X锁 |
| 3 | / | 阻塞 | 会话B添加X锁阻塞，证明排它性 |
| 4 | commit; | / | 会话B X锁添加成功 |
| 5 | / | commit; | 会话B提交事务，结束流程 |
S锁、X锁常见误区
注意：在同一个事务里面，行级X锁或S锁，允许其它SQL语句对此行的写操作。 如下，每条SQL都能成功执行。 所以说锁，锁的是对外的事务，对内（当前会话）不做限制。
start transaction;
select * from cs where id = 1 for update;
update cs set num1 = 2 where id = 1;
commit;
start transaction;
select * from cs where id = 1 lock in share mode;
update cs set num1 = 2 where id = 1;
commit;
注意：在任意个事务里面，可以对同一条不存在的数据共同添加X和S锁，不会阻塞。
事务A
start transaction;
select * from cs where id = 1234 for update;
select * from cs where id = 1234 lock in share mode;
事务B
start transaction;
select * from cs where id = 1234 for update;
select * from cs where id = 1234 lock in share mode;
事务A
commit;
事务B
commit;
注意：事务A加了X锁，不影响事务B读这条数据。
事务A
start transaction;
select * from cs where id = 1 for update; #能正常读出数据
事务B
start transaction;
select * from cs where id = 1;
事务A
commit;
事务B
commit;
对阻塞超时时间的优化
遇到X锁会阻塞，默认阻塞50秒，50秒过后阻塞停止，但不会自动的回滚或者提交事务。MySQL提供了一些阻塞时间的优化方向 方式1：配置优化
查看阻塞时间，单位秒
show variables like 'innodb_lock_wait_timeout';
或
select @@innodb_lock_wait_timeout;
修改
set session innodb_lock_wait_timeout = 10; #当前会话生效
或
set global innodb_lock_wait_timeout = 10; #全局生效，重启后新配置丢失
注意global的修改对当前线程是不生效的，只有建立新的连接才生效
或者修改配置文件
vim /etc/my.cnf
[mysqld]
innodb_lock_wait_timeout = 10
方式2：SQL语句层面，MySQL8新特性 select ... nowait：会立即停止，但报错。 select ...skip locked：会立即停止，但不会报错，结果中不包含被锁定的行。
表锁、页锁、行锁
这是锁定的粒度，3个依次递减，粒度越小越好。力度小意味着更少的资源被锁定，可以提高并发性。
表锁
表锁就是锁住了整张表，所以表锁比行锁，更不容易发生死锁的情况。 注意：MyISAM引擎使用select，会给当前表加上S锁，在写操作时会加上X锁。
查看一些数据库的表，都加了那些锁
show open tables;
In_use: 是否正在被使用，如果为 1 则表示该表正在被某个线程使用。
Name_locked: 是否被上了表级锁，如果为 1 则表示该表被锁定。
+--------------------+------------------------------------------------------+--------+-------------+
| Database | Table | In_use | Name_locked |
+--------------------+------------------------------------------------------+--------+-------------+
| performance_schema | events_waits_summary_by_user_by_event_name | 0 | 0 |
| performance_schema | events_waits_summary_global_by_event_name | 0 | 0 |
+--------------------+------------------------------------------------------+--------+-------------+
为某个表添加X锁
lock table 表名 write;
为某个表添加S锁
lock table 表名 read;
取消表锁，部分XS
unlock tables;
表锁权限如下（MySQL5.7）
| 表锁类型 | 当前会话对当前表可读 | 当前会话对当前表可写 | 其它会话对当前表可读 | 其它会话对当前表可写 |
|---|---|---|---|---|
| S锁 | 是 | 否 | 是 | 阻塞 |
| X锁 | 阻塞 | 是 | 阻塞 | 阻塞 |
表级锁->意向锁、意向共享锁、意向排它锁、IS锁、IX锁
意向锁（Intention Shared Lock）是自动维护的锁，用于表级别上表明事务对表中某个行的操作意向，意思是在表级别上声明了某个行已经加锁，优化事务之间的并发访问。意向锁分为两种：意向共享锁（IS）和意向排它锁（IX）。 MyISAM引擎不支持，InnoDB引擎支持。 MySQL添加行级锁，都会向上声明添加了意向锁。 注意意向排它锁与意向排它锁不冲突，意向排它锁与意向共享锁都不冲突，如果冲突了，锁的灵活性将大大降低。距离说明：两个不同的行X锁各生成一个IX锁，如果IX锁冲突，则无法创建两个行X锁，这种巨大的bug不允许出现。
优点：
- 提高并发性：如果没有意向锁，在一个百万级的大表中加一个锁，可能需要逐行遍历，看看有没有加其它锁，会影响当前添加的锁，但是有了意向锁，在表的层面就可以获取先前的锁，提高性能。
- 避免死锁：通过意向锁，可以快速判断哪些锁请求是互斥的，减少锁任务堆积引起的复杂度增加，复杂度一上来，就容易有死锁。
演示：行X锁与表S锁
| 步骤 | 会话A | 会话B | 说明 |
|---|---|---|---|
| 1 | start transaction; | / | 会话A开启事务 |
| 2 | select * from cs where id = 1 for update; | / | 会话A加一个行X锁，相当于自动添加了一个IX锁 |
| 3 | / | lock table cs read; | 会话B添加表S锁 |
| 4 | / | 阻塞 | 这个过程就是表锁检测到了事先添加的IX锁，行级X锁排斥表级S锁，阻塞了 |
| 5 | commit; | 成功加上了表级S锁 | 会话A事务提交，锁资源释放 |
| 6 | / | unlock tables; | 释放表级S锁 |
表级锁->自增锁、AI锁
自增锁（Auto Increment Lock）是指对自增长列（一般是主键）确保唯一性的一种锁机，如果没有自增锁，多个事务并发的执行，该列的值就可能一致。 为了避免重复，自增锁可能会导致插入操作的串行化，降低并发性能，这是它的缺点。 演示：
| 步骤 | 会话A | 会话B | 说明 |
|---|---|---|---|
| 1 | start transaction; | start transaction; | 双方开启事务 |
| 2 | INSERT INTO cs (num1, num2, s1, s2) VALUES (2, 2, 'ABC', 'XYZ'); | INSERT INTO cs (num1, num2, s1, s2) VALUES (2, 2, 'ABC', 'XYZ'),(2, 2, 'ABC', 'XYZ'); | 双方新增3条记录 |
| 3 | commit | commit; | 提交事务 |
| 4 | select id from cs | select id from cs | 并未发现报错，或者id重复的现象 |
表级锁->元数据锁、MDL锁
MDL（Metadata Lock）听起来高大上，实际上就是DDL操作时自动加锁，所以不需要手动处理。 当要对表做增删改查操作时，会获取一个 MDL 读锁来阻止对表结构的修改。 更改表结构时，会加MDL写锁，阻塞其它线程的读写操作，直到结构变更操作完成。 演示 MDL读锁：
| 步骤 | 会话A | 会话B | 说明 |
|---|---|---|---|
| 1 | start transaction; | / | 会话A开启事务 |
| 2 | INSERT INTO cs (num1, num2, s1, s2) VALUES (2, 2, 'ABC', 'XYZ'); | alter table cs add index (num1); | 会话B新增一个普通索引 |
| 3 | / | 阻塞 | 会话B被阻塞 |
| 4 | commit; | 索引添加成功 | MDL锁释放 |
演示 MDL写锁：
| 步骤 | 会话A | 会话B | 会话C | 说明 |
|---|---|---|---|---|
| 1 | start transaction; | start transaction; | / | AB方开启事务 |
| 2 | INSERT INTO cs (num1, num2, s1, s2) VALUES (2, 2, 'ABC', 'XYZ'); | alter table cs add index (num2); | / | 会话B新增一个普通索引 |
| 3 | / | 阻塞 | / | 用会话A的MDL读锁去阻塞会话B |
| 4 | / | / | select * from cs | 用会话B的MDL写锁去阻塞会话C |
| 5 | / | 阻塞 | 阻塞 | MDL写锁阻塞了查询操作 |
| 6 | rollback; | rollback; | 成功查询出数据 | 事务回滚，锁资源释放。但undo log不记录DDL语句，所以索引被添加无法回滚。 |
行锁->记录锁、RL锁
记录锁（Record Lock）就是普通的对某一行上的X或者S锁。 注意InnoDB引擎下，支持行锁MyISAM不支持。 测试：
| 步骤 | 会话A | 会话B | 备注 |
|---|---|---|---|
| 1 | start transaction; | start transaction; | 双方开启事务 |
| 2 | select * from cs where id = 1 lock in share mode; | select * from cs where id = 1 lock in share mode; | 双方添加共享锁，都能成功添加 |
| 3 | commit; | commit; | 正常提交事务，无报错 |
行锁->间隙锁、GL锁
间隙锁（Gap Lock）就是在数据间隙加的锁，用于防止事务在一个范围内插入新的符合条件的行，以避免中间插入的幻读的问题（MySQL默认的RR隔离级别会产生幻读问题）。 例如一个一个不连续的id列，1,6,10，如果在1~6之间添加一个锁，就可以1~6防止幻读的插入操作，10后面间隙锁防止幻读就失效了，需要再次声明新的间隙锁。1~6之间没有具体的数据，所以X间隙锁，或者是S间隙锁，没有什么区别。
MySQL默认的RR级别会产生幻读，幻读是指在同一事务中，先后执行相同的查询范围，查询到的数量不一致（mysql的select机制测不出来，两个事务需要插入相同的id引起的报错才能测出来），在并发环境中，幻读通常是因为其他事务在同一范围内插入新数据导致的。以下的演示，不演示幻读，只演示间隙锁阻止幻读。
测试：
| 步骤 | 会话A | 会话B | 备注 |
|---|---|---|---|
| 1 | start transaction; | start transaction; | 双方开启事务 |
| 2 | select * from cs where id = 4 lock in share mode; | INSERT INTO cs (id, num1, num2, s1, s2) VALUES (3, 1, 1, 'abc', 'xyz'); | 会话A添加间隙锁，会话B插入数据这个区间的数据 |
| 3 | / | 阻塞 | 可见间隙锁可以阻止区间幻读 |
| 4 | commit; | commit; | 会话A关闭事务，会话B提交插入数据 |
当前id最大值为10，测试大于10的幻读区间
| 步骤 | 会话A | 会话B | 备注 |
|---|---|---|---|
| 1 | start transaction; | start transaction; | 双方开启事务 |
| 2 | sselect * from cs where id = 14 lock in share mode; | INSERT INTO cs (id, num1, num2, s1, s2) VALUES (10000, 1, 1, 'abc', 'xyz'); | 会话A添加间隙锁，会话B插入id为10000的数据 |
| 3 | / | 阻塞 | 可见间隙锁可以防止最大记录 ~ +∞之间的幻读 |
| 4 | commit; | commit; | 会话A关闭事务，会话B提交插入数据 |
当前id最大值为100，同样的方法测试小于100的幻读区间
| 步骤 | 会话A | 会话B | 备注 |
|---|---|---|---|
| 1 | start transaction; | start transaction; | 双方开启事务 |
| 2 | sselect * from cs where id = 10001 lock in share mode; | INSERT INTO cs (id, num1, num2, s1, s2) VALUES (9999, 1, 1, 'abc', 'xyz'); | 会话A添加间隙锁，会话B插入id为9999的数据 |
| 3 | / | 成功插入 | 间隙锁未能阻塞幻读插入 |
| 4 | commit; | commit; | 会话A关闭事务，会话B提交插入数据 |
行锁->临键锁、NKL锁
临键锁（next-key lock）相当于记录锁+间隙锁，用于防止幻读的锁。 测试：现表中有id为1,6,10三条数据。
| 步骤 | 会话A | 会话B | 备注 |
|---|---|---|---|
| 1 | start transaction; | start transaction; | 双方开启事务 |
| 2 | select * from cs where id between 1 and 6 lock in share mode; | INSERT INTO cs (id, num1, num2, s1, s2) VALUES (4, 1, 1, 'abc', 'xyz'); | 会话A添加临键锁，会话B插入id为4的数据 |
| 3 | / | 阻塞 | 临键锁阻塞幻读插入 |
| 4 | commit; | commit; | 双方提交事务，结束流程 |
行锁->插入意向锁、IIL锁
插入意向锁（Insert Intention Lock）是间隙锁的一种，由insert行插入前的操作。该锁表示插入的意图，即插入同一索引间隙的多个事务如果没有在间隙内的相同位置插入，则insert不需要相互等待。 通俗讲就是如果间隙锁得到了释放，则需要insert的多个事务，不会阻塞。 测试：现表中有id为1,6,10三条数据。
| 步骤 | 会话A | 会话B | 会话C | 备注 |
|---|---|---|---|---|
| 1 | start transaction; | start transaction; | start transaction; | 三方开启事务 |
| 2 | select * from cs where id = 5 lock in share mode; | INSERT INTO cs (id, num1, num2, s1, s2) VALUES (4, 1, 1, 'abc', 'xyz'); | INSERT INTO cs (id, num1, num2, s1, s2) VALUES (4, 1, 1, 'abc', 'xyz'); | 事务A上间隙锁，其余事务插入数据 |
| 3 | / | 阻塞 | 阻塞 | 间隙锁起作用阻塞B、C会话 |
| 4 | commit; | 成功插入 | 成功插入 | 插入意向锁起作用，让两个插入的事务不发生阻塞 |
| 5 | commit; | commit; | commit; | 提交事务，结束流程 |
页锁
页锁是一种锁定级别，用于控制对数据表中的页（MySQL对表数据读写的基本单位）的访问。 由于SQL语句层面，DDL、DML、DQL，都是针对库表或者表数据的读写操作，对于页极少操作，页锁粒度较大，其它锁范围使用场景够用，所以了解概念就行。
悲观锁
之前写过详细的文章可进行参考：MySQL乐观锁与悲观锁。
悲观锁比较悲观，假设数据一定会造成冲突，属于MySQL层面的锁。通过加锁阻塞其他事务，悲观锁可以保证在任何时刻，只有一个事务能够修改或访问共享资源，从而实现了强一致性。这意味着在悲观锁机制下，每个事务的读写操作都是有序、线性的。 需要事务的参与。
悲观锁，最经典的场景就是防超卖，一共10个库存，由于并发情况下，两个请求查询到的库存都是10，一个请求下单6个，一个请求下单8个，如果不加锁使其在事务中阻塞（阻塞可以理解为强制让事务串行执行），那就会有超卖的情况发生，即使是超卖，库存也不会显示为负，因为并发情况下两个请求检测到的库存都是10,大于8和6，最终扣库存的结果不是10-8-6=-4（这是串行请求的理论值），而是2或者4。
适用场景 写多写操作的前提，是保证数据不出错，悲观锁的机制很符合。
优点
- 强一致性：基于事务又加锁，一致性可以保证。
- 实现简单：在事务中for update即可，开发者不需要在这上面关注太多。
缺点
- 死锁风险：悲观锁在使用不当的情况下可能导致死锁。如果多个事务持有锁并相互等待对方释放锁的情况发生，就可能发生死锁。
- 性能较低：悲观锁通常需要在整个事务过程中锁定资源，这可能导致其他事务阻塞。
简单测试：模拟并发情况下防超卖，假设cs表的num1字段为库存字段
| 步骤 | 会话A | 会话B | 备注 |
|---|---|---|---|
| 1 | start transaction; | start transaction; | 双方开启事务 |
| 2 | select num1 from cs where id = 1 for update;update cs set num1 = num1 - 6 where id = 1 and num1 >= 6; | select num1 from cs where id = 1 for update;update cs set num1 = num1 - 8 where id = 1 and num1 >= 8; | 双方添加X行锁，并准备扣减库存 |
| 3 | / | 阻塞 | 此时必有一个事务阻塞，等这个事务扣完库存提交事务后，在执行另一个扣库存的事务 |
| 4 | commit; | commit; | 双方提交事务，双方代码执行成功，但会话B库存不足，最终剩余库存4，结束流程 |
注意，实际的开发逻辑，与以上示例况有偏差。 若前端检测到库存不足，直接拦截用户的下单动作并提示，就不会有后端的流程。 若前端认为库存充足，后端可能是下单时预扣库存，预扣库存的环节提前就检测当前其库存是否够当前的购买数量，若不够，则直接终止流程，返回用户结果。 若库存够用但未支付，若超时30分钟未支付则取消订单（这个延时队列可以用Redis Zset 或者RabbitMQ等其它消息中间件实现），若支付，则算一笔成功的交易。 这是MySQL能抗住的情况，如果抗不住，就需要Redis+Lua的单线程事务来阻止超卖的发生。
乐观锁
之前写过详细的文章可进行参考：MySQL乐观锁与悲观锁
乐观锁很乐观，假设数据一般情况不会造成冲突，属于程序层面的逻辑锁，在数据进行更新时，才进行锁的检测。是通过添加一个版本号的方式实现的，每当数据这一行所在的数据发生变化，则对应的版本号+1，更新数据时，将版本号作为查询条件。 至于是否要加事务，看写操作单条数据还是写操作多条数据。
注意：网上很多解决方案用时间戳来做version字段，我持反对意见，并发可能是一瞬间的事，不到一秒就有好多请求，用时间戳粒度太大，用随机字符串都比用这个强。
适用场景
- 读多写少：由于并发写操作较少，乐观锁的修改数据受影响行数为0概率也较低。 允许一定量的重试或不需要重试的场景：这个要根据业务，否则来回重试会降低性能。 优点
- 实现简单：乐观锁在代码上就可以实现，不需要额外对数据库额外操作。
- 无死锁风险：悲观锁有死锁风险，乐观锁没有。 无需重试情况下，性能较高：乐观锁机制在并发访问情况下，不需要像悲观锁那样阻塞其他事务，提供了更高的并发性能，前提当前业务需求能容忍写操作失败的情况。
缺点
- 并发冲突：多加了一个where条件，只能保证数据最终不会出错，不能保证每条写操作的SQL都执行成功（也就是受影响行数>0）。
- 不提供强一致性：强一致性要求数据的状态在任何时刻都保持一致，悲观锁是到写操作那一步才去验证，期间只是做了个where条件的过滤。
- ABA问题：一个字段的值在请求X中查询出来是A，后续代码实现乐观锁，因为并发量大，同时过来一个Y请求，将A值改成了B，因为一些业务原因又改成了A，整个过程虽然不影响请求X的结果，且能正常执行，但是联合其它数据，这个情况是否符合业务场景，不好说，所以最好的解决方案，就是专门做一个version字段，且不会与之前的version重复，即可，把这个version字段作为where条件，而不是存A或者B字段的所在字段作为where条件。
- 当前的请求中了上一个乐观锁的招，导致的版本号不一样，需要重试，反复的重试也降低性能，由于这个原因，所以用于读操作多的场景。
简单测试：模拟并发情况下防超卖，假设cs表的num1字段为库存字段，s1为version字段，一共10个库存，两个请求查询到的库存都是10，一个请求下单6个，一个请求下单8个。
| 步骤 | 会话A | 会话B | 备注 |
|---|---|---|---|
| 1 | select * from cs where id = 1 | select * from cs where id = 1 | 双方获取版本号和库存 |
| 2 | update cs set num1 = num1 - 6,s1 = 'version2' where id = 1 and s1 = 'version1' and num1 >=6; | update cs set num1 = num1 - 8,s1 = 'version1' where id = 1 and s1 = 'version1' and num1 >=8; | 乐观锁无需事务，但是需要多个原子性的业务场景，仍推荐加事务 |
| 3 | 扣库存成功 | 虽然是并发场景，但A执行略快于B，此时版本号已修改为version2 | 会话B执行成功，但库存扣减失败 |
| 4 | / | select * from cs where id = 1 | 会话B进行重试，并重新获取版本号和库存 |
| 5 | / | update cs set num1 = num1 - 8,s1 = 'version3' where id = 1 and s1 = 'version2' and num1 >=8; | 双方代码执行成功，但会话B库存不足，最终剩余库存4，结束流程 |
隐式锁
隐式锁不需要显式地编写锁定关键词，会自动加锁或解锁。 例如在未提交的事务中插入数据，就是通过隐式锁的方式避免其它事务对插入数据的读写，如果能读到了那就是脏读，如果能写那就是脏写。 隐式锁是一种延时加载的方式，在多个事务的情况下，在第二个事务访问时，才会被动的加锁，这样可以减少锁的数量。 由于不需要手动处理，了解机制就行。
| 步骤 | 会话A | 会话B | 会话C | 备注 |
|---|---|---|---|---|
| 1 | start transaction; | start transaction; | SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS; | AB开启事务，C检测锁 |
| 2 | insert into cs(num1, num2, s1, s2) values(1,2,3,4); | / | SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS; | A插入数据，此时C未检测到有锁 |
| 3 | / | select * from cs lock in share mode; | SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS; | insert操作会产生一个写锁，进而阻止事务B的读锁，此时事务C可以查看的到 |
| 4 | / | 阻塞 | / | 在第二个事务访问时，事务A被动的加锁，通过事务C可以查看的到 |
| 5 | commit; | commit; | / | 双方提交事务，会话A成功插入一条数据 |
显式锁
显式声明的锁，for update，lock in share mode这种的。
全局锁
全局锁（Global Lock）的作用是锁定整个数据库实例，让整个库只能读不能写，粒度很大。 flush tables with read lock：获取全局读锁，对所有事务只能读不能写。 unlock tables：释放全局锁。 应用场景：备份数据库或者导出数据时，为了保证数据的一致性而需要锁定整个数据库实例，确保备份过程中数据不会发生变化。全局锁会影响数据库的正常运行。 测试：
| 步骤 | 会话A | 会话B | 备注 |
|---|---|---|---|
| 1 | flush tables with read lock; | / | 会话A开启全局锁 |
| 2 | / | select * from cs;desc cs; | 读数据表和表数据都没问题 |
| 3 | / | insert into cs(num1, num2, s1, s2) values(1,2,3,4); | 会话B插入数据 |
| 4 | / | 阻塞 | 全局锁禁止写操作 |
| 5 | unlock tables; | 插入成功 | 释放全局锁。数据插入成功 |
| 6 | flush tables with read lock; | / | 会话A开启全局锁 |
| 7 | / | alter table cs add index(num1); | 会话B为表字段加索引 |
| 8 | / | 阻塞; | 全局锁阻塞DDL操作 |
| 9 | unlock tables; | 索引添加成功 | 释放全局锁，索引插入成功，结束会话 |
死锁
之前写过关于死锁的详细文章：MySQL死锁。 MySQL 中的死锁是指两个或多个事务相互等待对方释放锁资源，导致它们永远无法继续执行的情况。（不会耕田,怎能下地 不会下地,怎么耕田） 测试：先cs表中有id为1,6的两条数据
| 步骤 | 会话A | 会话B | 备注 |
|---|---|---|---|
| 1 | start transaction; | start transaction; | 双方开启事务 |
| 2 | update cs set num1 = 1 where id = 1; | update cs set num1 = 60 where id = 6; | 更新数据默认会产生X锁，这一步正常执行 |
| 3 | update cs set num1 = 6 where id = 6; | update cs set num1 = 10 where id = 1; | 更新数据默认会产生X锁 |
| 4 | 阻塞 | / | 事务B对于id=6的写锁并未释放，此时又新增了update请求，需要加X锁，所以阻塞 |
| 5 | / | Deadlock found when trying to get lock; try restarting transaction | 事务AB互相需要等待，导致死锁 |
| 6 | commit; | commit; | 双方提交事务，结束流程，事务A正确执行，事务B被回滚 |
排查方法1
show engine innodb status;
执行以上命令会得到大量日志，在LATEST DETECTED DEADLOCK与TRANSACTIONS之间寻找sql 语句，以此定位死锁源头。示例如下：
......
------------------------
LATEST DETECTED DEADLOCK
------------------------
2024-01-23 23:48:30 0x1f00
*** (1) TRANSACTION:
TRANSACTION 805714, ACTIVE 12 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 1136, 2 row lock(s)
MySQL thread id 3, OS thread handle 8076, query id 762 localhost ::1 root updating
update test set name = 'C++语言' where id = 2
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 2575 page no 3 n bits 72 index PRIMARY of table `temp`.`test` trx id 805714 lock_mode X locks rec but not gap waiting
Record lock, heap no 6 PHYSICAL RECORD: n_fields 4; compact format; info bits 32
0: len 4; hex 00000002; asc ;;
1: len 6; hex 0000000c4b53; asc KS;;
2: len 7; hex 34000002a80923; asc 4 #;;
3: len 3; hex 432b2b; asc C++;;
*** (2) TRANSACTION:
TRANSACTION 805715, ACTIVE 9 sec starting index read
mysql tables in use 1, locked 1
3 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 1
MySQL thread id 4, OS thread handle 7936, query id 766 localhost ::1 root updating
delete from test where id = 1
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 2575 page no 3 n bits 72 index PRIMARY of table `temp`.`test` trx id 805715 lock_mode X locks rec but not gap
Record lock, heap no 6 PHYSICAL RECORD: n_fields 4; compact format; info bits 32
0: len 4; hex 00000002; asc ;;
1: len 6; hex 0000000c4b53; asc KS;;
2: len 7; hex 34000002a80923; asc 4 #;;
3: len 3; hex 432b2b; asc C++;;
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 2575 page no 3 n bits 72 index PRIMARY of table `temp`.`test` trx id 805715 lock_mode X locks rec but not gap waiting
Record lock, heap no 4 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
0: len 4; hex 00000001; asc ;;
1: len 6; hex 0000000c4b46; asc KF;;
2: len 7; hex ad000002b10110; asc ;;
3: len 1; hex 43; asc C;;
*** WE ROLL BACK TRANSACTION (1)
------------
TRANSACTIONS
------------
Trx id counter 805727
Purge done for trx's n:o < 805727 undo n:o < 0 state: running but idle
History list length 36
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 283132143509864, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 283132143508992, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 283132143508120, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 283132143507248, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
......
排查方法2
show status like 'innodb_row_lock%'
执行以上命令后会得到一个表格，
Innodb_row_lock_current_waits 0 如果这个数字非零，表示有事务正在等待其他事务持有的行级锁。
Innodb_row_lock_time 18756 以毫秒为单位。它表示所有事务在竞争锁资源时，所消耗的总时间
Innodb_row_lock_time_avg 3126 平均每个InnoDB行级锁的持有时间，毫秒为单位。用总的锁持有时间除以锁请求次数来计算。
Innodb_row_lock_time_max 7921 单个InnoDB行级锁的最大持有时间，毫秒为单位。这个值表示所有事务竞争锁资源中的最长时间。
Innodb_row_lock_waits 6 这个指标表示有多少次事务在竞争锁资源时需要等待。
排查方法3 该查询是用来获取当前正在运行的事务（INNODB_TRX表）、空闲状态的线程（PROCESSLIST表，COMMAND为Sleep）、线程对应的进程信息（threads表）、线程对应的当前执行中的SQL语句（events_statements_current表）的一些相关信息。
SELECT a.trx_id, a.trx_state, a.trx_started, a.trx_query, b.ID, b.USER, b.DB, b.COMMAND, b.TIME, b.STATE, b.INFO, c.PROCESSLIST_USER, c.PROCESSLIST_HOST, c.PROCESSLIST_DB, d.SQL_TEXT
FROM information_schema.INNODB_TRX a
LEFT JOIN information_schema.PROCESSLIST b ON a.trx_mysql_thread_id = b.id AND b.COMMAND = 'Sleep'
LEFT JOIN performance_schema.threads c ON b.id = c.PROCESSLIST_ID
LEFT JOIN performance_schema.events_statements_current d ON d.THREAD_ID = c.THREAD_ID;
解决 MySQL会自动解决死锁问题，代价就是MySQL自行找到成本最低的事务，自动回滚。所以说解决方案不是解锁，而是避免。
避免
- 降低事务粒度：轻量级的事务，锁定更少的资源，不容易发生死锁。
- 尽快提交事务：锁能更快的释放。
- 合理的索引设计: 合理设计数据库表的索引可以减少锁竞争，提高查询效率。
- 一致的访问顺序: 当应用程序涉及多个表时，保持一致的访问顺序可以避免死锁。例如，如果事务A先锁定表X，再锁定表Y，那么事务B也应该按照相同的顺序锁定表X和表Y，从而避免死锁。
什么是最小成本回滚策略？ 在 MySQL 中，当发生死锁时，MySQL 使用一种叫做"最小成本回滚"（InnoDB 中称为"最小编号事务回滚"）的策略来选择一个事务作为牺牲者并进行回滚，最小成本回滚策略是 MySQL 的默认行为，它会自动选择牺牲者并回滚事务。 最小成本回滚策略的原理是选择最小成本的事务作为牺牲者。评估算法如下：
- 回滚事务所涉及的操作数量：回滚操作的数量越小，成本越低。
- 回滚事务所占用的系统资源：回滚事务占用的系统资源越少，成本越低。
- 回滚事务已执行的工作量：已执行的工作量越少，成本越低。
锁排查与监控
查询语句用于查看 InnoDB 存储引擎中关于行锁的相关统计信息
show status like 'innodb_row_lock%';
Innodb_row_lock_current_waits 如果这个数字非零，表示有事务正在等待其他事务持有的行级锁。
Innodb_row_lock_time 以毫秒为单位。它表示所有事务在竞争锁资源时，所消耗的总时间
Innodb_row_lock_time_avg 平均每个InnoDB行级锁的持有时间，毫秒为单位。用总的锁持有时间除以锁请求次数来计算。
Innodb_row_lock_time_max 单个InnoDB行级锁的最大持有时间，毫秒为单位。这个值表示所有事务竞争锁资源中的最长时间。
Innodb_row_lock_waits 这个指标表示有多少次事务在竞争锁资源时需要等待。
查看 InnoDB 存储引擎中当前活动的事务信息
SELECT * FROM information_schema.innodb_trx;
trx_id 事务的唯一标识符。
trx_state 事务的状态，如 RUNNING、LOCK WAIT、ROLLING BACK 等。
trx_started 事务启动的时间。
trx_requested_lock_id 请求的锁的标识符。
trx_wait_started 等待锁的开始时间。
trx_weight 事务的权重，用于死锁检测。
trx_mysql_thread_id MySQL 线程 ID。
trx_query 与事务相关的 SQL 查询语句。
trx_operation_state 事务内部操作的状态。
trx_tables_in_use 事务使用的表的数量。
trx_tables_locked 事务锁定的表的数量。
trx_lock_structs 事务内部使用的锁结构数量。
trx_lock_memory_bytes 用于事务锁定的内存字节数。
trx_rows_locked 事务锁定的行数。
trx_rows_modified 事务修改的行数。
trx_concurrency_tickets 用于事务并发控制的票数。
trx_isolation_level 事务的隔离级别。
trx_unique_checks 是否启用了唯一性检查。
trx_foreign_key_checks 是否启用了外键约束检查。
trx_last_foreign_key_error 最后一个外键错误信息。
trx_adaptive_hash_latched 是否适应性哈希被锁定。
trx_adaptive_hash_timeout 适应性哈希锁定超时次数。
trx_is_foreign_key_with_check 是否用于外键约束检查。
trx_is_foreign_key 是否用于外键约束。
查看 InnoDB 存储引擎中当前正在被锁定的对象（如表、行）的信息
SELECT * FROM information_schema.innodb_locks;
lock_id 锁的唯一标识符。
lock_trx_id 持有该锁的事务的唯一标识符。
lock_mode 锁的模式，如 S（共享锁）或 X（独占锁）等。
lock_type 锁的类型，如 RECORD（行级锁）或 TABLE（表级锁）等。
lock_table 被锁定的表名。
lock_index 被锁定的索引名。
lock_space 被锁定的表空间的标识符。
lock_page 被锁定的页码。
lock_rec 锁定的记录。
lock_data 与锁相关的其他数据。
查看 InnoDB 存储引擎中当前存在的锁等待情况
SELECT * FROM information_schema.innodb_lock_waits;
requesting_trx_id 请求锁的事务的唯一标识符。
requested_lock_id 请求的锁的唯一标识符。
blocking_trx_id 导致锁等待的阻塞事务的唯一标识符。
blocking_lock_id 导致锁等待的锁的唯一标识符。