1 死锁产生和分析
2 死锁case
3 死锁总结

本文概览：在分析死锁时需要从以下三个方面考虑：

1、那些锁引起的死锁

死锁就是由不同事务的 Recored Lock / GAP lock /Insert Intention Lock依赖顺序形成闭环导致的，主要包括如下两种情况

不同事务Record Lock之间冲突。SelectForUpdate和Select in share mode、update 、delete、insert 都会产生ReocdLock

不同事务的GapLock和Insert Intention Locks之间冲突。Insert Intention Locks只有insert语句才有。

2、执行sql时，如何进行加锁

3、分析不同事务中锁的依赖关系，判断是否形成依赖闭环

附：本小节中用户到数据表结构结构和数据。

mysql> show create table student \G;
*************************** 1. row ***************************
       Table: student
Create Table: CREATE TABLE `student` (
  `id` int(11) NOT NULL,
  `name` varchar(45) NOT NULL,
  `score` int(11) DEFAULT NULL,
  `des` varchar(45) NOT NULL DEFAULT 'hello',
  PRIMARY KEY (`id`),
  UNIQUE KEY `unq_nam` (`name`),
  KEY `idx_score` (`score`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.01 sec)

ERROR: 
No query specified

mysql> select * from student ;
+----+-----------+-------+----------+
| id | name      | score | des      |
+----+-----------+-------+----------+
|  0 | vv        |    66 | hello    |
|  1 | success55 |    6 | hi       |
|  5 | ff10      |    10 | koko     |
|  7 | ffg       |    22 | tomorrow |
+----+-----------+-------+----------+
4 rows in set (0.01 sec)

mysql> show create table student \G;

*************************** 1. row ***************************

Table: student

Create Table: CREATE TABLE `student` (

`id` int(11) NOT NULL,

`name` varchar(45) NOT NULL,

`score` int(11) DEFAULT NULL,

`des` varchar(45) NOT NULL DEFAULT 'hello',

PRIMARY KEY (`id`),

UNIQUE KEY `unq_nam` (`name`),

KEY `idx_score` (`score`)

) ENGINE=InnoDB DEFAULT CHARSET=latin1

1 row in set (0.01 sec)

ERROR:

No query specified

mysql> select * from student ;

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

| id | name | score | des |

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

| 0 | vv | 66 | hello |

| 1 | success55 | 6 | hi |

| 5 | ff10 | 10 | koko |

| 7 | ffg | 22 | tomorrow |

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

4 rows in set (0.01 sec)

1 死锁产生和分析

1、死锁的产生

分为共享锁、互斥锁、意向锁三种类型，这三种类型的互斥兼容关系如下表，正因为有如下冲突关系才导致循环等待，从而触发死锁。

	共享锁	排他锁	意向共享锁	意向排他锁
共享锁	兼容	冲突	兼容	冲突
排他锁	冲突	冲突	冲突	冲突
意向共享锁	兼容	冲突	兼容	兼容
意向排他锁	冲突	冲突	兼容	兼容

因为意向类型锁只有在表锁的时候才会使用，所以我们经常遇到都是X和S两种类型的锁。X和S这两种锁类型对应具体的锁有 Recored Lock 和 GAP lock （分别包含X和S两种类型）；对于Next-key Lock，它也是由Record和Gap Lock的组合而成的。

X/S/IS/IX 和  RecordLock/GapLock 之间的关系？
（1）X、S、IS、IX是锁的类型，不是具体的锁。
（2）具体锁有Recored Lock和Gap Lock，每一种具体锁，都包含X、S、IS、IX类型，如共享类型的GapLock，互斥类型的GapLock。

X/S/IS/IX 和 RecordLock/GapLock 之间的关系？

（1）X、S、IS、IX是锁的类型，不是具体的锁。

（2）具体锁有Recored Lock和Gap Lock，每一种具体锁，都包含X、S、IS、IX类型，如共享类型的GapLock，互斥类型的GapLock。

GapLock只在执行insert时会被用到，GapLock可以保证不同事务在不能在同一个gap中插入数据，避免出现幻读。对于同一个Gap，GapLock和Record Lock、GapLock和GapLock都是兼容的，GapLock只会跟Insert Intention Locks互斥。如下图

	GapLock	InsertIntentionLock	RecordLock
GapLock	兼容	兼容	兼容
InsertIntentionLock	冲突	兼容	兼容
RecordLock	兼容	兼容	冲突

所以死锁就是由不同事务的 Recored Lock / GAP lock /Insert Intention Lock依赖顺序形成闭环导致的，主要包括如下两种情况

不同事务Record Lock之间冲突(RecordLock-X与RecordLock-S、RecordLock-X与Record-X)。SelectForUpdate和Select in share mode、update 、delete、insert 都会产生ReocdLock.
不同事务的GapLock和Insert Intention Locks之间冲突。Insert Intention Locks只有insert语句才有。

2、在分析死锁时，主要有两点

（1）查看执行的sql语句添加了哪些Recored Lock 和 Gap Lock；

可以参考如下

mysql的innodb锁

（2）根据依赖关系，判断不同事务中这些锁是否可以发生死锁。

2 死锁case

可以针对产生死锁两种情况，举例四个常见的case：

（1）不同事务Record Lock之间冲突(RecordLock-X与RecordLock-S、RecordLock-X与Record-X)

四个非空selectForUpdate
delte+两个insert。Recored共享锁和Recored互斥锁导致
三个insert。Recored共享锁和Recored互斥锁导致

（2）不同事务的GapLock和Insert Intention Locks之间冲突

两个空的selectForUpdate+ 两个insert

2.1 四个非空selectForUpdate

1、死锁机理

这种死锁是通过不同事务Record Lock之间冲突

2、死锁的复现

步骤	事务1	事务2
1	begin
2		begin
3	select * from student where id = 1 for update; 获取一行数据 where条件中是主键，此时只有Record Lock
4		select * from student where id = 5 for update; 获取一行数据。 where条件中是主键，此时只有Record Lock
5	select * from student where id = 5 for update; 此时hang住
6		select * from student where id = 1 for update; ERROR : Deadlock found when trying to get lock; try restarting transaction

通过“show engine innodb status ”查看死锁信息

------------------------
LATEST DETECTED DEADLOCK
------------------------
2018-06-06 01:01:34 700000a83000
*** (1) TRANSACTION:
TRANSACTION 50636, ACTIVE 9 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 360, 2 row lock(s)
MySQL thread id 269, OS thread handle 0x700000c5f000, query id 41132 localhost 127.0.0.1 root statistics
select * from student where id = 1 for update
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 1351 page no 3 n bits 80 index `PRIMARY` of table `test`.`student` trx id 50636 lock_mode X locks rec but not gap waiting
Record lock, heap no 3 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
 0: len 4; hex 80000001; asc     ;;
 1: len 6; hex 00000000c57f; asc       ;;
 2: len 7; hex 19000001cf2b21; asc      +!;;
 3: len 9; hex 737563636573733535; asc success55;;
 4: len 4; hex 80000006; asc     ;;
 5: len 2; hex 6869; asc hi;;

*** (2) TRANSACTION:
TRANSACTION 50635, ACTIVE 15 sec starting index read
mysql tables in use 1, locked 1
3 lock struct(s), heap size 360, 2 row lock(s)
MySQL thread id 267, OS thread handle 0x700000a83000, query id 41133 localhost 127.0.0.1 root statistics
select * from student where id = 5 for update
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 1351 page no 3 n bits 80 index `PRIMARY` of table `test`.`student` trx id 50635 lock_mode X locks rec but not gap
Record lock, heap no 3 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
 0: len 4; hex 80000001; asc     ;;
 1: len 6; hex 00000000c57f; asc       ;;
 2: len 7; hex 19000001cf2b21; asc      +!;;
 3: len 9; hex 737563636573733535; asc success55;;
 4: len 4; hex 80000006; asc     ;;
 5: len 2; hex 6869; asc hi;;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 1351 page no 3 n bits 80 index `PRIMARY` of table `test`.`student` trx id 50635 lock_mode X locks rec but not gap waiting
Record lock, heap no 11 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
 0: len 4; hex 80000005; asc     ;;
 1: len 6; hex 00000000c5c4; asc       ;;
 2: len 7; hex c0000001770110; asc     w  ;;
 3: len 4; hex 66663130; asc ff10;;
 4: len 4; hex 8000000a; asc     ;;
 5: len 4; hex 6b6f6b6f; asc koko;;

*** WE ROLL BACK TRANSACTION (2)

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

LATEST DETECTED DEADLOCK

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

2018-06-06 01:01:34 700000a83000

*** (1) TRANSACTION:

TRANSACTION 50636, ACTIVE 9 sec starting index read

mysql tables in use 1, locked 1

LOCK WAIT 3 lock struct(s), heap size 360, 2 row lock(s)

MySQL thread id 269, OS thread handle 0x700000c5f000, query id 41132 localhost 127.0.0.1 root statistics

select * from student where id = 1 for update

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

RECORD LOCKS space id 1351 page no 3 n bits 80 index `PRIMARY` of table `test`.`student` trx id 50636 lock_mode X locks rec but not gap waiting

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

0: len 4; hex 80000001; asc ;;

1: len 6; hex 00000000c57f; asc ;;

2: len 7; hex 19000001cf2b21; asc +!;;

3: len 9; hex 737563636573733535; asc success55;;

4: len 4; hex 80000006; asc ;;

5: len 2; hex 6869; asc hi;;

*** (2) TRANSACTION:

TRANSACTION 50635, ACTIVE 15 sec starting index read

mysql tables in use 1, locked 1

3 lock struct(s), heap size 360, 2 row lock(s)

MySQL thread id 267, OS thread handle 0x700000a83000, query id 41133 localhost 127.0.0.1 root statistics

select * from student where id = 5 for update

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

RECORD LOCKS space id 1351 page no 3 n bits 80 index `PRIMARY` of table `test`.`student` trx id 50635 lock_mode X locks rec but not gap

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

0: len 4; hex 80000001; asc ;;

1: len 6; hex 00000000c57f; asc ;;

2: len 7; hex 19000001cf2b21; asc +!;;

3: len 9; hex 737563636573733535; asc success55;;

4: len 4; hex 80000006; asc ;;

5: len 2; hex 6869; asc hi;;

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

RECORD LOCKS space id 1351 page no 3 n bits 80 index `PRIMARY` of table `test`.`student` trx id 50635 lock_mode X locks rec but not gap waiting

Record lock, heap no 11 PHYSICAL RECORD: n_fields 6; compact format; info bits 0

0: len 4; hex 80000005; asc ;;

1: len 6; hex 00000000c5c4; asc ;;

2: len 7; hex c0000001770110; asc w ;;

3: len 4; hex 66663130; asc ff10;;

4: len 4; hex 8000000a; asc ;;

5: len 4; hex 6b6f6b6f; asc koko;;

*** WE ROLL BACK TRANSACTION (2)

3、其他类似情况

除了上面情况，还可以是

selectForUpdarte的where中为普通索引和唯一索引时。
对于update/delete，类似selectForUpdate可以复现此种case。

2.2 两个空的selectForUpdate和两个insert

1、死锁机理

这个死锁产生其实就是因为GAP Lock和Insert IntentionLocks 互斥产生的。

2、下面以SelectForUpdate为例复现死锁

步骤	事务1	事务2
1	begin
2		begin
3	mysql> select * from student where id = 2 for update; Empty set (0.00 sec) where条件必须是主键，此时为空时，只会上锁GapLock(负无穷，6)
4		mysql> select * from student where id = 3 for update; Empty set (0.00 sec) where条件必须是主键，此时为空时，只会上锁GapLock(负无穷，6)
5	mysql> insert into student(id,name,score,des) values (2,’ff2′,10,’test2′); hang住了。事务1需要等待事务2的Gap锁（10，22），然后上锁Insert Intention Locks，再获取recoredLock，执行insert
6		mysql> insert into student(id,name,score,des) values (3,’ff3′,10,’test3′); 事务2需要等待事务1的Gap锁（10，22），然后上锁Insert Intention Locks，此时产生循环依赖触发死锁。 Deadlock found when trying to get lock; try restarting transaction

通过“show engine innodb status ”查看死锁信息

------------------------
LATEST DETECTED DEADLOCK
------------------------
2018-06-06 01:06:05 700000c5f000
*** (1) TRANSACTION:
TRANSACTION 50637, ACTIVE 60 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 360, 2 row lock(s)
MySQL thread id 267, OS thread handle 0x700000a83000, query id 41141 localhost 127.0.0.1 root update
insert into student(id,name,score,des) values (2,'ff2',10,'test2')
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 1351 page no 3 n bits 80 index `PRIMARY` of table `test`.`student` trx id 50637 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 11 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
 0: len 4; hex 80000005; asc     ;;
 1: len 6; hex 00000000c5c4; asc       ;;
 2: len 7; hex c0000001770110; asc     w  ;;
 3: len 4; hex 66663130; asc ff10;;
 4: len 4; hex 8000000a; asc     ;;
 5: len 4; hex 6b6f6b6f; asc koko;;

*** (2) TRANSACTION:
TRANSACTION 50638, ACTIVE 42 sec inserting
mysql tables in use 1, locked 1
3 lock struct(s), heap size 360, 2 row lock(s)
MySQL thread id 269, OS thread handle 0x700000c5f000, query id 41142 localhost 127.0.0.1 root update
insert into student(id,name,score,des) values (3,'ff3',10,'test3')
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 1351 page no 3 n bits 80 index `PRIMARY` of table `test`.`student` trx id 50638 lock_mode X locks gap before rec
Record lock, heap no 11 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
 0: len 4; hex 80000005; asc     ;;
 1: len 6; hex 00000000c5c4; asc       ;;
 2: len 7; hex c0000001770110; asc     w  ;;
 3: len 4; hex 66663130; asc ff10;;
 4: len 4; hex 8000000a; asc     ;;
 5: len 4; hex 6b6f6b6f; asc koko;;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 1351 page no 3 n bits 80 index `PRIMARY` of table `test`.`student` trx id 50638 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 11 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
 0: len 4; hex 80000005; asc     ;;
 1: len 6; hex 00000000c5c4; asc       ;;
 2: len 7; hex c0000001770110; asc     w  ;;
 3: len 4; hex 66663130; asc ff10;;
 4: len 4; hex 8000000a; asc     ;;
 5: len 4; hex 6b6f6b6f; asc koko;;

*** WE ROLL BACK TRANSACTION (2)

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

LATEST DETECTED DEADLOCK

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

2018-06-06 01:06:05 700000c5f000

*** (1) TRANSACTION:

TRANSACTION 50637, ACTIVE 60 sec inserting

mysql tables in use 1, locked 1

LOCK WAIT 3 lock struct(s), heap size 360, 2 row lock(s)

MySQL thread id 267, OS thread handle 0x700000a83000, query id 41141 localhost 127.0.0.1 root update

insert into student(id,name,score,des) values (2,'ff2',10,'test2')

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

RECORD LOCKS space id 1351 page no 3 n bits 80 index `PRIMARY` of table `test`.`student` trx id 50637 lock_mode X locks gap before rec insert intention waiting

Record lock, heap no 11 PHYSICAL RECORD: n_fields 6; compact format; info bits 0

0: len 4; hex 80000005; asc ;;

1: len 6; hex 00000000c5c4; asc ;;

2: len 7; hex c0000001770110; asc w ;;

3: len 4; hex 66663130; asc ff10;;

4: len 4; hex 8000000a; asc ;;

5: len 4; hex 6b6f6b6f; asc koko;;

*** (2) TRANSACTION:

TRANSACTION 50638, ACTIVE 42 sec inserting

mysql tables in use 1, locked 1

3 lock struct(s), heap size 360, 2 row lock(s)

MySQL thread id 269, OS thread handle 0x700000c5f000, query id 41142 localhost 127.0.0.1 root update

insert into student(id,name,score,des) values (3,'ff3',10,'test3')

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

RECORD LOCKS space id 1351 page no 3 n bits 80 index `PRIMARY` of table `test`.`student` trx id 50638 lock_mode X locks gap before rec

Record lock, heap no 11 PHYSICAL RECORD: n_fields 6; compact format; info bits 0

0: len 4; hex 80000005; asc ;;

1: len 6; hex 00000000c5c4; asc ;;

2: len 7; hex c0000001770110; asc w ;;

3: len 4; hex 66663130; asc ff10;;

4: len 4; hex 8000000a; asc ;;

5: len 4; hex 6b6f6b6f; asc koko;;

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

RECORD LOCKS space id 1351 page no 3 n bits 80 index `PRIMARY` of table `test`.`student` trx id 50638 lock_mode X locks gap before rec insert intention waiting

Record lock, heap no 11 PHYSICAL RECORD: n_fields 6; compact format; info bits 0

0: len 4; hex 80000005; asc ;;

1: len 6; hex 00000000c5c4; asc ;;

2: len 7; hex c0000001770110; asc w ;;

3: len 4; hex 66663130; asc ff10;;

4: len 4; hex 8000000a; asc ;;

5: len 4; hex 6b6f6b6f; asc koko;;

*** WE ROLL BACK TRANSACTION (2)

当两个selectForUpdate不再同一个Gap时，此时就不会出现死锁，如下

步骤	事务1	事务2
1	begin
2		begin
3	mysql> select * from student where id = 5 for update; Empty set (0.00 sec) 上锁GapLock(负无穷，6)
4		mysql> select * from student where id = 8 for update; Empty set (0.00 sec) 上锁GapLock(6，10)
5	mysql> insert into student(id,name,score,des) values (8,’ff5′,5,’test5′); 可以获取Insert Intention Locks，再获取recoredLock，所以不会阻塞 Query OK, 1 row affected (0.00 sec)
6		mysql> insert into student(id,name,score,des) values (9,’ff8′,8,’test8′); 可以获取Insert Intention Locks，再获取recoredLock，所以不会阻塞 Query OK, 1 row affected (0.00 sec)

3、其他case

除了SelectForUpdate，Update、delete都是可以复现的，关键是对于SelectForUpdate/update/delete的where中条件为主键而且查询结果为空，此时才只会上GapLock。

2.3 三个insert

1、死锁机理

这个死锁产生其实就是因为GAP Lock和Insert IntentionLocks 互斥产生的。

2、复现死锁

步骤	事务1	事务2	事务3
1	begin
2		begin
3			begin
4	insert into student(id,name,score) values (3,’ggg’,12); 获取RecoredLock 排他锁
5		insert into student(id,name,score) values (3,’ggg’,12); 此时hang住了。尝试获取RecordLock共享锁
6			insert into student(id,name,score) values (3,’ggg’,12); 此时hang住了尝试获取RecordLock共享锁
7	rollback
8			Deadlock found when trying to get lock; try restarting transaction
		执行成功

通过“show engine innodb status ”查看死锁信息

------------------------
LATEST DETECTED DEADLOCK
------------------------
2018-06-04 15:53:51 700000a83000
*** (1) TRANSACTION:
TRANSACTION 50298, ACTIVE 9 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 4 lock struct(s), heap size 1184, 2 row lock(s)
MySQL thread id 235, OS thread handle 0x700000ac7000, query id 40496 localhost 127.0.0.1 root update
insert into student(id,name,score) values (3,'ggg',12)
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 1350 page no 3 n bits 80 index `PRIMARY` of table `test`.`student` trx id 50298 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 4 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
 0: len 4; hex 80000005; asc     ;;
 1: len 6; hex 00000000c45e; asc      ^;;
 2: len 7; hex 6e0000022514e4; asc n   %  ;;
 3: len 3; hex 666666; asc fff;;
 4: len 4; hex 8000000b; asc     ;;

*** (2) TRANSACTION:
TRANSACTION 50299, ACTIVE 4 sec inserting
mysql tables in use 1, locked 1
4 lock struct(s), heap size 1184, 2 row lock(s)
MySQL thread id 236, OS thread handle 0x700000a83000, query id 40498 localhost 127.0.0.1 root update
insert into student(id,name,score) values (3,'ggg',12)
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 1350 page no 3 n bits 80 index `PRIMARY` of table `test`.`student` trx id 50299 lock mode S locks gap before rec
Record lock, heap no 4 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
 0: len 4; hex 80000005; asc     ;;
 1: len 6; hex 00000000c45e; asc      ^;;
 2: len 7; hex 6e0000022514e4; asc n   %  ;;
 3: len 3; hex 666666; asc fff;;
 4: len 4; hex 8000000b; asc     ;;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 1350 page no 3 n bits 80 index `PRIMARY` of table `test`.`student` trx id 50299 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 4 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
 0: len 4; hex 80000005; asc     ;;
 1: len 6; hex 00000000c45e; asc      ^;;
 2: len 7; hex 6e0000022514e4; asc n   %  ;;
 3: len 3; hex 666666; asc fff;;
 4: len 4; hex 8000000b; asc     ;;

*** WE ROLL BACK TRANSACTION (2)

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

LATEST DETECTED DEADLOCK

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

2018-06-04 15:53:51 700000a83000

*** (1) TRANSACTION:

TRANSACTION 50298, ACTIVE 9 sec inserting

mysql tables in use 1, locked 1

LOCK WAIT 4 lock struct(s), heap size 1184, 2 row lock(s)

MySQL thread id 235, OS thread handle 0x700000ac7000, query id 40496 localhost 127.0.0.1 root update

insert into student(id,name,score) values (3,'ggg',12)

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

RECORD LOCKS space id 1350 page no 3 n bits 80 index `PRIMARY` of table `test`.`student` trx id 50298 lock_mode X locks gap before rec insert intention waiting

Record lock, heap no 4 PHYSICAL RECORD: n_fields 5; compact format; info bits 0

0: len 4; hex 80000005; asc ;;

1: len 6; hex 00000000c45e; asc ^;;

2: len 7; hex 6e0000022514e4; asc n % ;;

3: len 3; hex 666666; asc fff;;

4: len 4; hex 8000000b; asc ;;

*** (2) TRANSACTION:

TRANSACTION 50299, ACTIVE 4 sec inserting

mysql tables in use 1, locked 1

4 lock struct(s), heap size 1184, 2 row lock(s)

MySQL thread id 236, OS thread handle 0x700000a83000, query id 40498 localhost 127.0.0.1 root update

insert into student(id,name,score) values (3,'ggg',12)

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

RECORD LOCKS space id 1350 page no 3 n bits 80 index `PRIMARY` of table `test`.`student` trx id 50299 lock mode S locks gap before rec

Record lock, heap no 4 PHYSICAL RECORD: n_fields 5; compact format; info bits 0

0: len 4; hex 80000005; asc ;;

1: len 6; hex 00000000c45e; asc ^;;

2: len 7; hex 6e0000022514e4; asc n % ;;

3: len 3; hex 666666; asc fff;;

4: len 4; hex 8000000b; asc ;;

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

RECORD LOCKS space id 1350 page no 3 n bits 80 index `PRIMARY` of table `test`.`student` trx id 50299 lock_mode X locks gap before rec insert intention waiting

Record lock, heap no 4 PHYSICAL RECORD: n_fields 5; compact format; info bits 0

0: len 4; hex 80000005; asc ;;

1: len 6; hex 00000000c45e; asc ^;;

2: len 7; hex 6e0000022514e4; asc n % ;;

3: len 3; hex 666666; asc fff;;

4: len 4; hex 8000000b; asc ;;

*** WE ROLL BACK TRANSACTION (2)

（1）为什么在rollback时发生死锁，在commit时不会产生死锁？

事务1 执行commit，事务2 和事务3 在执行insert和尝试获取互斥锁时，首先判断是否已经存在元素了，如果已经存在则此时就会报错”Duplicate entry ‘3’ for key ‘PRIMARY‘” ，不会在取获取互斥锁了。

（2）理解事务2和事务3发生死锁

https://dev.mysql.com/doc/refman/8.0/en/innodb-locks-set.html

Insert操作设置锁时，注意如下情况：
If a duplicate-key error occurs, a shared lock on the duplicate index record is set. 
This use of a shared lock can result in deadlock should there be multiple sessions trying to insert the same row if another session already has an exclusive lock.

https://dev.mysql.com/doc/refman/8.0/en/innodb-locks-set.html

Insert操作设置锁时，注意如下情况：

If a duplicate-key error occurs, a shared lock on the duplicate index record is set.

This use of a shared lock can result in deadlock should there be multiple sessions trying to insert the same row if another session already has an exclusive lock.

当存在一个事务A已经执行了insert插入相同的元素，则当前事务B的insert操作会尝试获取一个共享锁，此时会进行等等待，直到A进行commit或者rollback,然后事务1就可以获取到共享锁。所以在上面死锁例子中，当事务1进行rollback之后，事务2和事务3同时获取到共享锁，然后执行insert。如下流程：

步骤	事务1	事务2
1	begin
2		begin
3	select * from student where id =3 lock in share mode; Empty set (0.00 sec) 获取Record lock 共享锁
4		select * from student where id =3 lock in share mode; Empty set (0.00 sec) 获取Record lock 共享锁
5	insert into student(id,name,score) values (3,’ggg’,12); 尝试获取RecordLock 此时会hang住
6		insert into student(id,name,score) values (3,’ggg’,12); Deadlock found when trying to get lock; try restarting transaction
7	执行成功

通过“show engine innodb status ”查看死锁信息

------------------------
LATEST DETECTED DEADLOCK
------------------------
2018-06-04 15:05:35 700000ac7000
*** (1) TRANSACTION:
TRANSACTION 50291, ACTIVE 18 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 4 lock struct(s), heap size 1184, 2 row lock(s)
MySQL thread id 240, OS thread handle 0x700000b0b000, query id 40472 localhost 127.0.0.1 root update
insert into student(id,name,score) values (3,'ggg',12)
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 1350 page no 3 n bits 80 index `PRIMARY` of table `test`.`student` trx id 50291 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 4 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
 0: len 4; hex 80000005; asc     ;;
 1: len 6; hex 00000000c45e; asc      ^;;
 2: len 7; hex 6e0000022514e4; asc n   %  ;;
 3: len 3; hex 666666; asc fff;;
 4: len 4; hex 8000000b; asc     ;;

*** (2) TRANSACTION:
TRANSACTION 50292, ACTIVE 9 sec inserting
mysql tables in use 1, locked 1
4 lock struct(s), heap size 1184, 2 row lock(s)
MySQL thread id 235, OS thread handle 0x700000ac7000, query id 40473 localhost 127.0.0.1 root update
insert into student(id,name,score) values (3,'ggg',12)
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 1350 page no 3 n bits 80 index `PRIMARY` of table `test`.`student` trx id 50292 lock mode S locks gap before rec
Record lock, heap no 4 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
 0: len 4; hex 80000005; asc     ;;
 1: len 6; hex 00000000c45e; asc      ^;;
 2: len 7; hex 6e0000022514e4; asc n   %  ;;
 3: len 3; hex 666666; asc fff;;
 4: len 4; hex 8000000b; asc     ;;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 1350 page no 3 n bits 80 index `PRIMARY` of table `test`.`student` trx id 50292 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 4 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
 0: len 4; hex 80000005; asc     ;;
 1: len 6; hex 00000000c45e; asc      ^;;
 2: len 7; hex 6e0000022514e4; asc n   %  ;;
 3: len 3; hex 666666; asc fff;;
 4: len 4; hex 8000000b; asc     ;;

*** WE ROLL BACK TRANSACTION (2)

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

LATEST DETECTED DEADLOCK

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

2018-06-04 15:05:35 700000ac7000

*** (1) TRANSACTION:

TRANSACTION 50291, ACTIVE 18 sec inserting

mysql tables in use 1, locked 1

LOCK WAIT 4 lock struct(s), heap size 1184, 2 row lock(s)

MySQL thread id 240, OS thread handle 0x700000b0b000, query id 40472 localhost 127.0.0.1 root update

insert into student(id,name,score) values (3,'ggg',12)

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

RECORD LOCKS space id 1350 page no 3 n bits 80 index `PRIMARY` of table `test`.`student` trx id 50291 lock_mode X locks gap before rec insert intention waiting

Record lock, heap no 4 PHYSICAL RECORD: n_fields 5; compact format; info bits 0

0: len 4; hex 80000005; asc ;;

1: len 6; hex 00000000c45e; asc ^;;

2: len 7; hex 6e0000022514e4; asc n % ;;

3: len 3; hex 666666; asc fff;;

4: len 4; hex 8000000b; asc ;;

*** (2) TRANSACTION:

TRANSACTION 50292, ACTIVE 9 sec inserting

mysql tables in use 1, locked 1

4 lock struct(s), heap size 1184, 2 row lock(s)

MySQL thread id 235, OS thread handle 0x700000ac7000, query id 40473 localhost 127.0.0.1 root update

insert into student(id,name,score) values (3,'ggg',12)

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

RECORD LOCKS space id 1350 page no 3 n bits 80 index `PRIMARY` of table `test`.`student` trx id 50292 lock mode S locks gap before rec

Record lock, heap no 4 PHYSICAL RECORD: n_fields 5; compact format; info bits 0

0: len 4; hex 80000005; asc ;;

1: len 6; hex 00000000c45e; asc ^;;

2: len 7; hex 6e0000022514e4; asc n % ;;

3: len 3; hex 666666; asc fff;;

4: len 4; hex 8000000b; asc ;;

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

RECORD LOCKS space id 1350 page no 3 n bits 80 index `PRIMARY` of table `test`.`student` trx id 50292 lock_mode X locks gap before rec insert intention waiting

Record lock, heap no 4 PHYSICAL RECORD: n_fields 5; compact format; info bits 0

0: len 4; hex 80000005; asc ;;

1: len 6; hex 00000000c45e; asc ^;;

2: len 7; hex 6e0000022514e4; asc n % ;;

3: len 3; hex 666666; asc fff;;

4: len 4; hex 8000000b; asc ;;

*** WE ROLL BACK TRANSACTION (2)

2.4 delete和两个insert

1、死锁机理

这个死锁产生其实就是因为GAP Lock和Insert IntentionLocks 互斥产生的。

2、复现死锁

步骤	事务1	事务2	事务3
1	begin
2		begin
3			begin
4	delete from student where id = 3 ; 获取RecordLock排他锁
5		insert into student(id,name,score) values (3,’ggg’,12); 此时hang住尝试获取RecordLock共享锁
6			insert into student(id,name,score) values (3,’ggg’,12); 此时hang住尝试获取RecordLock共享锁
7	commit
8		获取RecordLock共享锁，尝试获取RecoredLock排他锁，此时需要等待事务2的共享锁	获取RecordLock共享锁，尝试获取RecoredLock排他锁，此时需要等待事务1的共享锁，检测到死锁 Deadlock found when trying to get lock; try restarting transaction
9			insert成功 Query OK, 1 row affected (14.76 sec)

通过“show engine innodb status ”查看死锁信息

------------------------
LATEST DETECTED DEADLOCK
------------------------
2018-06-04 15:59:29 700000a83000
*** (1) TRANSACTION:
TRANSACTION 50307, ACTIVE 14 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 360, 2 row lock(s)
MySQL thread id 235, OS thread handle 0x700000ac7000, query id 40513 localhost 127.0.0.1 root update
insert into student(id,name,score) values (3,'ggg',12)
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 1350 page no 3 n bits 80 index `PRIMARY` of table `test`.`student` trx id 50307 lock_mode X locks rec but not gap waiting
Record lock, heap no 9 PHYSICAL RECORD: n_fields 5; compact format; info bits 32
 0: len 4; hex 80000003; asc     ;;
 1: len 6; hex 00000000c482; asc       ;;
 2: len 7; hex 07000001bd11a0; asc        ;;
 3: len 3; hex 676767; asc ggg;;
 4: len 4; hex 8000000c; asc     ;;

*** (2) TRANSACTION:
TRANSACTION 50308, ACTIVE 4 sec inserting
mysql tables in use 1, locked 1
3 lock struct(s), heap size 360, 2 row lock(s)
MySQL thread id 236, OS thread handle 0x700000a83000, query id 40516 localhost 127.0.0.1 root update
insert into student(id,name,score) values (3,'ggg',12)
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 1350 page no 3 n bits 80 index `PRIMARY` of table `test`.`student` trx id 50308 lock mode S locks rec but not gap
Record lock, heap no 9 PHYSICAL RECORD: n_fields 5; compact format; info bits 32
 0: len 4; hex 80000003; asc     ;;
 1: len 6; hex 00000000c482; asc       ;;
 2: len 7; hex 07000001bd11a0; asc        ;;
 3: len 3; hex 676767; asc ggg;;
 4: len 4; hex 8000000c; asc     ;;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 1350 page no 3 n bits 80 index `PRIMARY` of table `test`.`student` trx id 50308 lock_mode X locks rec but not gap waiting
Record lock, heap no 9 PHYSICAL RECORD: n_fields 5; compact format; info bits 32
 0: len 4; hex 80000003; asc     ;;
 1: len 6; hex 00000000c482; asc       ;;
 2: len 7; hex 07000001bd11a0; asc        ;;
 3: len 3; hex 676767; asc ggg;;
 4: len 4; hex 8000000c; asc     ;;

*** WE ROLL BACK TRANSACTION (2)

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

LATEST DETECTED DEADLOCK

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

2018-06-04 15:59:29 700000a83000

*** (1) TRANSACTION:

TRANSACTION 50307, ACTIVE 14 sec inserting

mysql tables in use 1, locked 1

LOCK WAIT 3 lock struct(s), heap size 360, 2 row lock(s)

MySQL thread id 235, OS thread handle 0x700000ac7000, query id 40513 localhost 127.0.0.1 root update

insert into student(id,name,score) values (3,'ggg',12)

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

RECORD LOCKS space id 1350 page no 3 n bits 80 index `PRIMARY` of table `test`.`student` trx id 50307 lock_mode X locks rec but not gap waiting

Record lock, heap no 9 PHYSICAL RECORD: n_fields 5; compact format; info bits 32

0: len 4; hex 80000003; asc ;;

1: len 6; hex 00000000c482; asc ;;

2: len 7; hex 07000001bd11a0; asc ;;

3: len 3; hex 676767; asc ggg;;

4: len 4; hex 8000000c; asc ;;

*** (2) TRANSACTION:

TRANSACTION 50308, ACTIVE 4 sec inserting

mysql tables in use 1, locked 1

3 lock struct(s), heap size 360, 2 row lock(s)

MySQL thread id 236, OS thread handle 0x700000a83000, query id 40516 localhost 127.0.0.1 root update

insert into student(id,name,score) values (3,'ggg',12)

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

RECORD LOCKS space id 1350 page no 3 n bits 80 index `PRIMARY` of table `test`.`student` trx id 50308 lock mode S locks rec but not gap

Record lock, heap no 9 PHYSICAL RECORD: n_fields 5; compact format; info bits 32

0: len 4; hex 80000003; asc ;;

1: len 6; hex 00000000c482; asc ;;

2: len 7; hex 07000001bd11a0; asc ;;

3: len 3; hex 676767; asc ggg;;

4: len 4; hex 8000000c; asc ;;

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

RECORD LOCKS space id 1350 page no 3 n bits 80 index `PRIMARY` of table `test`.`student` trx id 50308 lock_mode X locks rec but not gap waiting

Record lock, heap no 9 PHYSICAL RECORD: n_fields 5; compact format; info bits 32

0: len 4; hex 80000003; asc ;;

1: len 6; hex 00000000c482; asc ;;

2: len 7; hex 07000001bd11a0; asc ;;

3: len 3; hex 676767; asc ggg;;

4: len 4; hex 8000000c; asc ;;

*** WE ROLL BACK TRANSACTION (2)

在上面的死锁中，必须保证delete和insert操作记录必须保证主键是一样的，才可能死锁，否则不会产生死锁。这是因为，insert只有跟delete主键一样的时候，insert才会尝试去获取共享锁，

https://dev.mysql.com/doc/refman/8.0/en/innodb-locks-set.html
Insert操作设置锁时，注意如下情况：

If a duplicate-key error occurs, a shared lock on the duplicate index record is set. 
This use of a shared lock can result in deadlock should there be multiple sessions trying to insert the same row if another session already has an exclusive lock.

https://dev.mysql.com/doc/refman/8.0/en/innodb-locks-set.html

Insert操作设置锁时，注意如下情况：

If a duplicate-key error occurs, a shared lock on the duplicate index record is set.

This use of a shared lock can result in deadlock should there be multiple sessions trying to insert the same row if another session already has an exclusive lock.

3 死锁总结

1、使用SELECT…For update时，where条件中要使用唯一索引或者主键。否则

where中使用普通索引，会使用到GAP锁，增加死锁的几率。
where中不使用索引，会产生锁表，对所有元素进行上锁RecordLock和对所有gap上锁GapLock。

2、如何避免 Select For Update为空时，造成死锁

step1 直接查询结果
setp2 如果上面结果为空执行step3，否则执行step4
step3 执行insert
step4 执行select For Update

(全文完)

Heart.Think.Do

mysql 死锁

1 死锁产生和分析

2 死锁case

2.1 四个非空selectForUpdate

2.2 两个空的selectForUpdate和两个insert

2.3 三个insert

2.4 delete和两个insert

3 死锁总结

Related Posts:

分类&标签