资讯专栏INFORMATION COLUMN

MySQL 死锁套路:唯一索引 S 锁与 X 锁的爱恨情仇

Darkgel / 1415人阅读

摘要:上一篇文章介绍了使用调试源码的方式来查看死锁的过程,这篇文章来讲讲一个常见的案例。毫不夸张的说,有一半以上的死锁问题由唯一索引贡献,后面介绍的很多死锁的问题都跟唯一索引有关。

上一篇文章介绍了使用调试 MySQL 源码的方式来查看死锁的过程,这篇文章来讲讲一个常见的案例。

毫不夸张的说,有一半以上的死锁问题由唯一索引贡献,后面介绍的很多死锁的问题都跟唯一索引有关。这次我们讲一段唯一索引 S 锁与 X 锁的爱恨情仇

我们来看一个简化过的例子

# 构造数据
CREATE TABLE `t1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(10),
  `level` int(11),
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_name` (`name`)
);
# 注意这里有插入一条初始化的数据,否则不会出现死锁
INSERT INTO `t1` (`name`, `level`) VALUES ("A",0);

# 出现问题的sql语句如下,并发情况下就会出现死锁
INSERT ignore INTO `t1` (`name`, `level`) VALUES ("A",0);
update t1 set level = 1 where name = "A";

我们用之前介绍过的源码分析方式,先来看下这两条语句分别加什么锁,然后分析死锁形成的过程。

第一条语句

INSERT ignore INTO t1 (name, level) VALUES ("A",0);

在调试中得到的结果如下

可以看到这条语句对唯一键 uk_name 加共享锁(S锁),而且成功。

第二条语句

update t1 set level = 1 where name = "A"; 通过唯一键更新数据库字段。

这种情况在之前的文章已经介绍过,会对唯一索引加 X 锁,然后对主键索引加 X 锁

这样就可以非常轻松的复现死锁的问题了,步骤如下

    开启两个 session,分别 begin

    session1 执行INSERT ignore INTO t1 (name, level) VALUES ("A",0);

    session2 执行INSERT ignore INTO t1 (name, level) VALUES ("A",0);

    session1 执行update t1 set level = 1 where name = "A"; 进入等待状态

    session2 执行update t1 set level = 1 where name = "A";,死锁产生,被回滚,同时事务 1 执行成功

详细的锁状态变化如下

t1 t2 备注
INSERT IGNORE INTO - t1成功获得uk的S锁 DB_SUCCESS
- INSERT IGNORE INTO t2成功获得uk的S锁 DB_SUCCESS
UPDATE - t1尝试获得uk的X锁,但没有成功,处于等待状态 DB_LOCK_WAIT
- UPDATE t2尝试获得uk的X锁,发现死锁产生 DB_DEADLOCK
- Deadlock t2释放S锁
成功 - -

死锁日志如下:

LATEST DETECTED DEADLOCK
------------------------
181208 23:00:52
*** (1) TRANSACTION:
TRANSACTION 53A7, ACTIVE 162 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 376, 2 row lock(s)
MySQL thread id 12, OS thread handle 0x700010522000, query id 1424 localhost root Updating
update t1 set level = 1 where name = "A"
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 89 page no 4 n bits 72 index `uk_name` of table `lock_demo2`.`t1` trx id 53A7 lock_mode X locks rec but not gap waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 1; hex 41; asc A;;
 1: len 4; hex 80000001; asc     ;;

*** (2) TRANSACTION:
TRANSACTION 53A8, ACTIVE 8 sec starting index read
mysql tables in use 1, locked 1
3 lock struct(s), heap size 376, 2 row lock(s)
MySQL thread id 96, OS thread handle 0x70001062e000, query id 1425 localhost root Updating
update t1 set level = 1 where name = "A"
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 89 page no 4 n bits 72 index `uk_name` of table `lock_demo2`.`t1` trx id 53A8 lock mode S
Record lock, heap no 2 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 1; hex 41; asc A;;
 1: len 4; hex 80000001; asc     ;;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 89 page no 4 n bits 72 index `uk_name` of table `lock_demo2`.`t1` trx id 53A8 lock_mode X locks rec but not gap waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 1; hex 41; asc A;;
 1: len 4; hex 80000001; asc     ;;

*** WE ROLL BACK TRANSACTION (2)

来详细看一下这个死锁日志

*** (1) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 89 page no 4 n bits 72 index uk_name of table lock_demo2.t1 trx id 53A7 lock_mode X locks rec but not gap waiting

事务 1 想获取 uk_name 唯一索引上的 X 锁 (非 gap 锁的记录锁)

*** (2) HOLDS THE LOCK(S): RECORD LOCKS space id 89 page no 4 n bits 72 index uk_name of table lock_demo2.t1 trx id 53A8 lock mode S

事务 2 持有uk_name 唯一索引上的 S 锁(共享锁)

*** (2) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 89 page no 4 n bits 72 index uk_name of table lock_demo2.t1 trx id 53A8 lock_mode X locks rec but not gap waiting

事务 2 想获得 uk_name 唯一索引上的 X 锁(非 gap 锁的记录锁)

跟之前理论上推断的结论是一致的

文章版权归作者所有,未经允许请勿转载,若此文章存在违规行为,您可以联系管理员删除。

转载请注明本文地址:https://www.ucloud.cn/yun/7006.html

相关文章

  • MySQL常见的两种存储引擎:MyISAM与InnoDB的爱恨情仇

    摘要:虽然性能极佳,而且提供了大量的特性,包括全文索引压缩空间函数等,但不支持事务和行级锁,而且最大的缺陷就是崩溃后无法安全恢复。 一 MyISAM 1.1 MyISAM简介 MyISAM是MySQL的默认数据库引擎(5.5版之前),由早期的 ISAM (Indexed Sequential Access Method:有索引的顺序访问方法)所改良。虽然性能极佳,而且提供了大量的特性,包括全...

    BWrong 评论0 收藏0
  • MySQL常见的两种存储引擎:MyISAM与InnoDB的爱恨情仇

    摘要:虽然性能极佳,而且提供了大量的特性,包括全文索引压缩空间函数等,但不支持事务和行级锁,而且最大的缺陷就是崩溃后无法安全恢复。 Java面试通关手册(Java学习指南,欢迎Star,会一直完善下去,欢迎建议和指导):https://github.com/Snailclimb/Java_Guide 一 MyISAM 1.1 MyISAM简介 MyISAM是MySQL的默认数据库引擎(5.5...

    gougoujiang 评论0 收藏0
  • MySQL常见的两种存储引擎:MyISAM与InnoDB的爱恨情仇

    摘要:虽然性能极佳,而且提供了大量的特性,包括全文索引压缩空间函数等,但不支持事务和行级锁,而且最大的缺陷就是崩溃后无法安全恢复。 Java面试通关手册(Java学习指南,欢迎Star,会一直完善下去,欢迎建议和指导):https://github.com/Snailclimb/Java_Guide 一 MyISAM 1.1 MyISAM简介 MyISAM是MySQL的默认数据库引擎(5.5...

    zzzmh 评论0 收藏0
  • MySQL 死锁套路唯一索引下批量插入顺序不一致

    摘要:死锁的本质是资源竞争,批量插入如果顺序不一致很容易导致死锁,我们来分析一下这个情况。为了方便演示,把批量插入改写为了多条。死锁的本质是资源竞争,批量插入如果顺序不一致很容易导致死锁,我们来分析一下这个情况。为了方便演示,把批量插入改写为了多条 insert。 先来做几个小实验,简化的表结构如下 CREATE TABLE `t1` ( `id` int(11) NOT NULL AUTO_...

    toddmark 评论0 收藏0
  • 一文详解MySQL的锁机制

    摘要:表级锁表级锁表级别的锁定是各存储引擎中最大颗粒度的锁定机制。当前没有其他事务持有表中任意一行的排他锁。为了检测是否满足第二个条件,事务必须在确保表不存在任何排他锁的前提下,去检测表中的每一行是否存在排他锁。一、表级锁、行级锁、页级锁数据库锁定机制简单来说,就是数据库为了保证数据的一致性,而使各种共享资源在被并发访问变得有序所设计的一种规则。MySQL数据库由于其自身架构的特点,存在多种数据存...

    番茄西红柿 评论0 收藏2637

发表评论

0条评论

最新活动
阅读需要支付1元查看
<