MySQL：死锁模拟

这个问题的关键在于sup伪列上的S锁放大，线上肯定是无序的因此block不一定是最后一个，这里就用顺序来模拟

建表：
CREATE TABLE `test3` (
`id1` int NOT NULL AUTO_INCREMENT,
`id2` int DEFAULT NULL,
 `a` int DEFAULT NULL,
 PRIMARY KEY (`id1`),
  UNIQUE KEY `a` (`a`,`id1`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8;
Query OK, 0 rows affected, 1 warning (0.05 sec)

sql>mysql> set auto_increment_increment=10;
Query OK, 0 rows affected (0.00 sec)

sql>mysql> insert into test3(id2,a) values(1,1);
Query OK, 1 row affected (0.01 sec)

sql>mysql> insert into test3(id2,a) select id2,a from test3;
Query OK, 1 row affected (0.01 sec)
Records: 1  Duplicates: 0  Warnings: 0

sql>mysql> insert into test3(id2,a) select id2,a from test3;
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

sql>mysql> insert into test3(id2,a) select id2,a from test3;
Query OK, 4 rows affected (0.01 sec)
Records: 4  Duplicates: 0  Warnings: 0

sql>mysql> insert into test3(id2,a) select id2,a from test3;
Query OK, 8 rows affected (0.01 sec)
Records: 8  Duplicates: 0  Warnings: 0

sql>mysql> insert into test3(id2,a) select id2,a from test3;
Query OK, 16 rows affected (0.01 sec)
Records: 16  Duplicates: 0  Warnings: 0

sql>mysql> insert into test3(id2,a) select id2,a from test3;
Query OK, 32 rows affected (0.01 sec)
Records: 32  Duplicates: 0  Warnings: 0

sql>mysql> insert into test3(id2,a) select id2,a from test3;
Query OK, 64 rows affected (0.02 sec)
Records: 64  Duplicates: 0  Warnings: 0

sql>mysql> insert into test3(id2,a) select id2,a from test3;
Query OK, 128 rows affected (0.03 sec)
Records: 128  Duplicates: 0  Warnings: 0

sql>mysql> insert into test3(id2,a) select id2,a from test3;
Query OK, 256 rows affected (0.05 sec)
Records: 256  Duplicates: 0  Warnings: 0

sql>mysql> insert into test3(id2,a) select id2,a from test3;
Query OK, 512 rows affected (0.11 sec)
Records: 512  Duplicates: 0  Warnings: 0

sql>mysql> insert into test3(id2,a) select id2,a from test3;
Query OK, 1024 rows affected (0.22 sec)
Records: 1024  Duplicates: 0  Warnings: 0

sql>mysql> alter table test3 change id1 id1 int NOT NULL ;
Query OK, 2048 rows affected (0.51 sec)
Records: 2048  Duplicates: 0  Warnings: 0

sql>mysql> alter table test3 drop primary key;
Query OK, 2048 rows affected (0.58 sec)
Records: 2048  Duplicates: 0  Warnings: 0

sql>mysql> alter table test3 add primary key(id1,id2);
Query OK, 0 rows affected (0.15 sec)
Records: 0  Duplicates: 0  Warnings: 0

sql>mysql> alter table test3 add key(a);
Query OK, 0 rows affected (0.08 sec)
Records: 0  Duplicates: 0  Warnings: 0

sql>mysql> update test3 set id2=id1 ,a=id1;
Query OK, 2047 rows affected (1.41 sec)
Rows matched: 2048  Changed: 2047  Warnings: 0

表建立好以后，总共3列，其中id1,id2为主键，a,id1为唯一键，数据处理好以后主键唯一键都是有序的，而线上可能是乱序的。而自增的步长改为了10，记录从1,11,21开始，那么最后几条记录如下：

+-------+-------+-------+
| 30511 | 30511 | 30511 |
| 30521 | 30521 | 30521 |
| 30531 | 30531 | 30531 |
| 30541 | 30541 | 30541 |
| 30551 | 30551 | 30551 |
| 30561 | 30561 | 30561 |
| 30571 | 30571 | 30571 |
| 30581 | 30581 | 30581 |
| 30591 | 30591 | 30591 |
| 30601 | 30601 | 30601 |
+-------+-------+-------+

时序如下：

session1	session2	session3
begin;
insert into test3 values(119,119,30610);
这一步的目的主要是在唯一键a,id1最后插入一行数据
	begin;
	insert into test3 values(119,219,30610),(6059,6059,6059);（堵塞）
	这一步完成后虽然有两个insert的值，但是第二个数据并没有进行唯一性检查和插入，而是堵塞在第一行数据，第一行数据，主键和session1是没有冲突的，但是唯一键有冲突，因此实际等待点为第一行数据(119,219,30610)对唯一键冲突检测上
		begin;
		insert into test3 values(6059,6071,6059);（不堵塞完成）
		完成这一步我们插入了一行数据，目的在于后面堵塞session2语句的第二行数据在唯一键索引上的数据插入，这里我们可以看到主键是不冲突的，但是唯一键都是6059,6059，因此后面会冲突
rollback;
完成这一步的目的在于让session2的lock s 扩展到唯一键的最后一个block的sup伪列上，为next key lock
	注意：这个时候session2的第一行数据插入实际上已经完成，并且有一个lock S到sup伪列上，这个时候堵塞在第二行数据的6059,6059这个唯一键上，而这个锁的持有者为session3
		insert into test3 values(119,319,30620);
		完成这一步的目的在于让session3也堵被session2堵塞，这个时候插入的唯一键只要落到最后一行记录到sup伪列之间则会被堵塞，这里插入的是唯一键30620,119

模拟中session3，没有使用insert values(),()的方式，是因为如果这样2行记录都会插入成功，因为线上是并发的，因此即便insert values(),()也可能出现问题这是其一。

其二，如果insert的值过多则持有锁的时间就更长，触发这个问题的机会就更大。因为insert values(),(),()....实际上还是每个行数据分别进行唯一性检查，插入操作，但是节省了语法解析和优化的部分。得到的死锁记录如下：

*** (1) TRANSACTION:
TRANSACTION 2752736, ACTIVE 111 sec inserting
sql>mysql tables in use 1, locked 1
LOCK WAIT 7 lock struct(s), heap size 1200, 5 row lock(s), undo log entries 2
MySQL thread id 26237, OS thread handle 140736551307008, query id 2964 localhost root update
insert into test106 values(119,219,30610),(6059,6059,6059)

*** (1) HOLDS THE LOCK(S):
RECORD LOCKS space id 1639 page no 15 n bits 800 index a of table `t10`.`test106` trx id 2752736 lock mode S
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len 8; hex 73757072656d756d; asc supremum;;


*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 1639 page no 10 n bits 952 index a of table `t10`.`test106` trx id 2752736 lock mode S waiting
Record lock, heap no 441 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
 0: len 4; hex 800017ab; asc     ;;
 1: len 4; hex 800017ab; asc     ;;
 2: len 4; hex 800017b7; asc     ;;


*** (2) TRANSACTION:
TRANSACTION 2752739, ACTIVE 39 sec inserting
sql>mysql tables in use 1, locked 1
LOCK WAIT 5 lock struct(s), heap size 1200, 3 row lock(s), undo log entries 2
MySQL thread id 26239, OS thread handle 140736283956992, query id 2970 localhost root update
insert into test106 values(119,319,30620)

*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 1639 page no 10 n bits 952 index a of table `t10`.`test106` trx id 2752739 lock_mode X locks rec but not gap
Record lock, heap no 441 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
 0: len 4; hex 800017ab; asc     ;;
 1: len 4; hex 800017ab; asc     ;;
 2: len 4; hex 800017b7; asc     ;;


*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 1639 page no 15 n bits 800 index a of table `t10`.`test106` trx id 2752739 lock_mode X insert intention waiting
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len 8; hex 73757072656d756d; asc supremum;;

从死锁模型来看和线上一致。