mysql 死锁场景分析

今天在DBGeek威信公众号上看到一篇推荐文章——记录一次MySQL死锁排查过程

文中提供了一个死锁场景，并给出了合理的解释：
表结构：
Create Table: CREATE TABLE `test` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `a` int(10) unsigned DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `a` (`a`)
) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=utf8
表数据：
+----+------+
| id | a    |
+----+------+
|  1 |    1 |
|  2 |    2 |
|  4 |    4 |
+----+------+
死锁的操作：(操作执行顺序从上到下依次执行)

事务1 ：mysql> start transaction;

事务2： mysql> start transaction ;

事务1：mysql> delete from test where a=2;

事务2：mysql> delete from test where a=2;#wait

事务1：mysql> insert into test select 10,2;

ERROR 1213 (40001): Deadlock found when trying to get lock; try restarti ng transaction 

使用show engine innodb status\G查看死锁日志如下：


------------------------
LATEST DETECTED DEADLOCK
------------------------
2017-07-27 11:55:24 7f6399b4e700
*** (1) TRANSACTION:
TRANSACTION 151839, ACTIVE 23 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 360, 1 row lock(s)
MySQL thread id 3, OS thread handle 0x7f6399b0d700, query id 64 localhost user updating
delete from test where a=2
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 114 page no 4 n bits 72 index `a` of table `city`.`test` trx id 151839 lock_mode X waiting
Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 32
 0: len 4; hex 00000002; asc     ;;
 1: len 4; hex 00000002; asc     ;;


*** (2) TRANSACTION:
TRANSACTION 151834, ACTIVE 34 sec inserting
mysql tables in use 1, locked 1
4 lock struct(s), heap size 1184, 3 row lock(s), undo log entries 2
MySQL thread id 1, OS thread handle 0x7f6399b4e700, query id 65 localhost user executing
insert into test select 10,2
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 114 page no 4 n bits 72 index `a` of table `city`.`test` trx id 151834 lock_mode X locks rec but not gap
Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 32
 0: len 4; hex 00000002; asc     ;;
 1: len 4; hex 00000002; asc     ;;


*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 114 page no 4 n bits 72 index `a` of table `city`.`test` trx id 151834 lock mode S waiting
Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 32
 0: len 4; hex 00000002; asc     ;;
 1: len 4; hex 00000002; asc     ;;


*** WE ROLL BACK TRANSACTION (1)
------------
根据日志分析可以知道：
1.事务1是TRANSACTION 151834,事务2是RANSACTION 151839
2.死锁发生时，事务1处于inserting状态，事务2处于waiting lock的delete状态
3.事务1在执行delete语句时会持有 X Locks(写锁)
4.事务2在执行delete语句时会进入X lock waiting状态(等待写锁)
5.事务1在执行insert语句时由于`a`字段是唯一索引，要做duplicate-key检测，检查时需要申请S Lock。这么，insert时由于事务2执行delete时申请的X lock还在等待中，事务2申请的S Lock也需要在X Lock waiting后等待，而这时事务2又持有X Lock锁无法释放，如此事务1和事务2陷入了循环等待，死锁出现。




如果将上面的死锁操作中的事务1的insert操作换成: delete from test where a=2
使用show engine innodb status\G查看死锁日志：
------------------------
LATEST DETECTED DEADLOCK
------------------------
2017-07-27 16:44:15 7f6399b0d700
*** (1) TRANSACTION:
TRANSACTION 151850, ACTIVE 6 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 360, 1 row lock(s)
MySQL thread id 4, OS thread handle 0x7f6399b4e700, query id 104 localhost user updating
delete from test where a=2
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 114 page no 4 n bits 72 index `a` of table `city`.`test` trx id 151850 lock_mode X waiting
Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 32
 0: len 4; hex 00000002; asc     ;;
 1: len 4; hex 00000002; asc     ;;


*** (2) TRANSACTION:
TRANSACTION 151845, ACTIVE 17 sec starting index read
mysql tables in use 1, locked 1
4 lock struct(s), heap size 1184, 3 row lock(s), undo log entries 1
MySQL thread id 5, OS thread handle 0x7f6399b0d700, query id 105 localhost user updating
delete from test where  a=2
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 114 page no 4 n bits 72 index `a` of table `city`.`test` trx id 151845 lock_mode X locks rec but not gap
Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 32
 0: len 4; hex 00000002; asc     ;;
 1: len 4; hex 00000002; asc     ;;


*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 114 page no 4 n bits 72 index `a` of table `city`.`test` trx id 151845 lock_mode X waiting
Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 32
 0: len 4; hex 00000002; asc     ;;
 1: len 4; hex 00000002; asc     ;;


*** WE ROLL BACK TRANSACTION (1)
------------


可以发现TRANSACTION 15184（事务1）中的waiting for this lock to be granted 中的锁是X Lock waiting


通过对这篇推文的学习和实践，拓展了以下几点：
1.对于唯一性索引执行insert 操作时会进行duplicate-key检测，并在检测中为了数据不会被改变施加 S Lock锁
2.学会了阅读死锁日志，`（1）`中的第一个描述的是TRANSACTION 151850的状态，之后的几个`（1）`描述的是TRANSACTION 151850中的events的状态，`（2）`同理。