mysql len hex asc_MYSQL死锁分析案例一（唯一键冲突）

Create Table: CREATE TABLE `t1` (

`id` int(11) NOT NULL,

`b` int(11) DEFAULT NULL,

`c` int(11) DEFAULT NULL,

`d` int(11) DEFAULT NULL,

PRIMARY KEY (`id`),

UNIQUE KEY `b` (`b`,`c`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8

mysql> insert into t1 select 1,1,2,3;

mysql> insert into t1 select 5,2,2,3;

mysql> select * from t1;

+----+------+------+------+

| id | b | c | d |

+----+------+------+------+

| 1 | 1 | 2 | 3 |

| 5 | 2 | 2 | 3 |

+----+------+------+------+

session A:begin; select * from t1 where b=1 and c=2 for update;

session B:begin; select * from t1 where b=1 and c=2 for update;

sessionA :insert into t1 select 3,1,2,3;

报错顺序是会话B先报错，会话A再报错，下面会讲。

会话B报错：ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

会话A报错：ERROR 1062 (23000): Duplicate entry '1-2' for key 'b'

输出下死锁信息：show engine innodb status\G

------------------------

LATEST DETECTED DEADLOCK

------------------------

2020-01-21 16:25:00 0x7fd5deeb2700

*** (1) TRANSACTION:

TRANSACTION 15117731, ACTIVE 28 sec starting index read

mysql tables in use 1, locked 1

LOCK WAIT 2 lock struct(s), heap size 1136, 1 row lock(s)

MySQL thread id 233174, OS thread handle 140556482070272, query id 5674894 106.1

1.34.12 root statistics

select * from t1 where b=1 and c=2 for update

*** (1) WAITING FOR THIS LOCK TO BE GRANTED:

RECORD LOCKS space id 21542 page no 4 n bits 72 index b of table `ceshi`.`t1` tr

x id 15117731 lock_mode X locks rec but not gap waiting

Record lock, heap no 2 PHYSICAL RECORD: n_fields 3; compact format; info bits 0

0: len 4; hex 80000001; asc ;;

1: len 4; hex 80000002; asc ;;

2: len 4; hex 80000001; asc ;;

*** (2) TRANSACTION:

TRANSACTION 15117718, ACTIVE 49 sec inserting

mysql tables in use 1, locked 1

4 lock struct(s), heap size 1136, 3 row lock(s), undo log entries 1

MySQL thread id 208385, OS thread handle 140556544714496, query id 5675592 106.1

1.34.12 root executing

insert into t1 select 3,1,2,3

*** (2) HOLDS THE LOCK(S):

RECORD LOCKS space id 21542 page no 4 n bits 72 index b of table `ceshi`.`t1` tr

x id 15117718 lock_mode X locks rec but not gap

Record lock, heap no 2 PHYSICAL RECORD: n_fields 3; compact format; info bits 0

0: len 4; hex 80000001; asc ;;

1: len 4; hex 80000002; asc ;;

2: len 4; hex 80000001; asc ;;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:

RECORD LOCKS space id 21542 page no 4 n bits 72 index b of table `ceshi`.`t1` tr

x id 15117718 lock mode S waiting

Record lock, heap no 2 PHYSICAL RECORD: n_fields 3; compact format; info bits 0

0: len 4; hex 80000001; asc ;;

1: len 4; hex 80000002; asc ;;

2: len 4; hex 80000001; asc ;;

*** WE ROLL BACK TRANSACTION (1)

下面分析下加锁流程：

会话A：对唯一索引(1,2)这条记录加X锁。

会话B：对唯一索引(1,2)这条记录加X锁，会话A持有锁，会话B只能等待。

会话A：写入一条(3,1,2,3)记录，可以看到1,2会造成唯一键冲突，

数据库会在主键冲突的记录上加S锁，这时就会有环路出现，

B------->A：B在等A的X锁。

A------->B：A要拿记录的S锁，但要等B的X锁。

1、数据库检测到有死锁，会自动KILL掉其中一个事务，根据日志信息，可以看到KILL掉了B会话，

2、B会话KILL掉之后，死锁消失，此时A会继续执行insert into t1 select 3,1,2,3;这条写入记录，会报唯一键冲突报错。