mysql死锁和索引的关系_MySQL 死锁套路：惟一索引 S 锁与 X 锁的爱恨情仇

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

绝不夸张的说，有一半以上的死锁问题由惟一索引贡献，后面介绍的不少死锁的问题都跟惟一索引有关。此次咱们讲一段惟一索引 S 锁与 X 锁的爱恨情仇sql

咱们来看一个简化过的例子数据库

# 构造数据

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";

复制代码

咱们用以前介绍过的源码分析方式，先来看下这两条语句分别加什么锁，而后分析死锁造成的过程。bash

第一条语句

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

在调试中获得的结果以下并发

能够看到这条语句对惟一键 uk_name 加共享锁(S锁)，并且成功。

第二条语句

update t1 set level = 1 where name = "A"; 经过惟一键更新数据库字段。源码分析

这种状况在以前的文章已经介绍过，会对惟一索引加 X 锁，而后对主键索引加 X 锁ui

这样就能够很是轻松的复现死锁的问题了，步骤以下

开启两个 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 执行成功

详细的锁状态变化以下spa

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锁

成功

-

-

死锁日志以下：3d

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 锁的记录锁)

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