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    导致锁等待的锁的唯一标识符。

热门相关:庶子风流   甜蜜隐婚:老公大人,宠上瘾   绝代疯少   盛华   妖夏