死锁场景之insert语句加锁分析与死锁案例分析（三）

死锁场景之insert语句加锁分析与死锁案例分析（三） 死锁场景之insert语句加锁分析与死锁案例分析（三）

关于mysql有哪些锁？锁的分类有哪些？可查看另外一篇博客：mysql 的锁

在实际情景中的加锁情况分析博客请查看另外一篇博客：数据库 *** 作加锁情况分析

关于数据库的死锁日志如何查看？ 请查看另外一篇博客：死锁日志查看

关于数据库的insert导致死锁案例查看：死锁场景之insert语句死锁分析（一）

关于平时常见的insert语句的加锁情况：死锁场景之insert语句加锁分析（二）

这篇主要介绍两个内容：

• insert语句加什么锁？
• 死锁案例分析

加锁案例一：唯一索引下的insert加锁情况分析（回顾） 1、环境介绍

针对mysql数据库，innodb 引擎下，隔离级别在 REPEATABLE READ 出现的死锁问题

CREATE TABLE `my2` (

`id` int(11) NOT NULL AUTO_INCREMENT,

`num1` int(11) DEFAULT NULL,

`num2` int(11) DEFAULT NULL,

PRIMARY KEY (`id`),

unique KEY  `indx_1` (`num1`)

) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8mb4

insert into my2(num1,num2) values(2,3),(5,4),(7,8);

说明：创建一个表 my2 ，id为自增主键索引，num1上有一个唯一索引

2 、事务 *** 作介绍 T2T1begin;delete from my2 where num1= 2;begin;insert into my2(num1,num2) values(2,3);commit;update my2 set num2=13 where num1=2;

说明：

事务T2：

• 删除num1为2的记录
• 在T1查询后进行提交

事务T1：

• 插入一条num1为2的数据记录（数据表中已经存在）
• 更新num1为2的数据记录数据

3 、事务日志

数据库查看最近的事务日志：

show engine innodb status

**（1）**delete from my2 where num1= 2; 事务日志：

show engine innodb status
TABLE LOCK table `mydatabase`.`my2 ` trx id 462308671 lock mode IX
RECORD LOCKS space id 232 page no 4 n bits 72 index `indx_1` of table `mydatabase`.`my2 ` trx id 462308671 lock_mode X locks rec but not gap
RECORD LOCKS space id 232 page no 3 n bits 72 index `PRIMARY` of table `mydatabase`.`my2 ` trx id 462308671 lock_mode X locks rec but not gap

加锁情况分析：

a. 事务T2 也即sess1执行的事务

sess1 先执行 sql delete from my2 where num1= 2 这里就不用说了，因为num1是唯一索引，所以只会在该条记录上加一个行锁，并在对应的主键索引上加一个行锁，无间隙锁，相当于就是加了两个行锁

**（2）**insert into my2(num1,num2) values(2,3); 事务日志：

LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 462308676, ACTIVE 4 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 360, 1 row lock(s), undo log entries 1
MySQL thread id 3796966, OS thread handle 0x7f78ea5c4700, query id 781051460 localhost root update
insert into my2(num1,num2) values(2,3);
7. ------- TRX HAS BEEN WAITING 4 SEC FOR THIS LOCK TO BE GRANTED:
8. RECORD LOCKS space id 232 page no 4 n bits 72 index `indx_1` of table `mydatabase`.`my2 ` trx id 462308676 lock mode S waiting
------------------

加锁情况分析：

b. 事务T1 也即sess2执行的事务

sess2 执行 sql insert into my2(num1,num2) values(2,3); 因为num1字段是一个唯一索引，所以insert语句会在插入前进行一次duplicate key的检查，需要申请S锁防止其他事务对a字段进行重复插入，而此时这个唯一索引上有了之前delete语句家的索引，所以这里肯定是waiting状态

**（3）**update my2 set num2=13 where num1=2;事务日志：

a. 事务T2 也即sess1执行的事务 ： 这时候T2提交了，那delete加的两个行锁就释放了，按理来说，上面的那个insert是加锁成功了

show engine innodb status
---TRANSACTION 462308676, ACTIVE 41 sec
3 lock struct(s), heap size 360, 2 row lock(s), undo log entries 1
MySQL thread id 3796966, OS thread handle 0x7f78ea5c4700, query id 781051460 localhost root cleaning up
TABLE LOCK table `test`.`t8` trx id 462308676 lock mode IX
RECORD LOCKS space id 232 page no 4 n bits 72 index `indx_1` of table `mydatabase`.`my2 ` trx id 462308676 lock mode S
RECORD LOCKS space id 232 page no 4 n bits 72 index `indx_1` of table `mydatabase`.`my2 ` trx id 462308676 lock mode S locks gap before rec

可以看到从获取锁的状态上看insert获取一把S Next-key Lock 锁和插入行之前的S GAP锁，按理来说还会对这条记录加一个X锁，但是没有显示，那如果再执行update会发生什么？

b. 事务T1 也即sess2执行的事务

sess2 执行 sql update my2 set num2=13 where num1=2;

---TRANSACTION 462308678, ACTIVE 12 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 3796960, OS thread handle 0x7f78eaabe700, query id 781059217 localhost root updating
update my2 set  num2=13 where num1=2;
------- TRX HAS BEEN WAITING 12 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 232 page no 4 n bits 72 index `indx_1` of table `mydatabase`.`my2 ` trx id 462308678 lock_mode X locks rec but not gap waiting
------------------
TABLE LOCK table `mydatabase`.`my2` trx id 462308678 lock mode IX
RECORD LOCKS space id 232 page no 4 n bits 72 index `indx_1` of table `mydatabase`.`my2 ` trx id 462308678 lock_mode X locks rec but not gap waiting


---TRANSACTION 462308676, ACTIVE 5113 sec
4 lock struct(s), heap size 1184, 3 row lock(s), undo log entries 1
MySQL thread id 3796966, OS thread handle 0x7f78ea5c4700, query id 781059230 localhost root init
show engine innodb status
TABLE LOCK table `mydatabase`.`my2` trx id 462308676 lock mode IX
RECORD LOCKS space id 232 page no 4 n bits 72 index `indx_1` of table `mydatabase`.`my2 ` trx id 462308676 lock mode S
RECORD LOCKS space id 232 page no 4 n bits 72 index `indx_1` of table `mydatabase`.`my2 ` trx id 462308676 lock mode S locks gap before rec
RECORD LOCKS space id 232 page no 4 n bits 72 index `indx_1` of table `mydatabase`.`my2 ` trx id 462308676 lock_mode X locks rec but not gap

因为num1字段是一个唯一索引，所以update语句直接就应该对num1=2这条记录加一个行锁，但是并没有加成功

这里可以看到从获取锁的状态上看insert获取一把S Next-key Lock 锁和插入行之前的S GAP锁，除此之外，多了一个记录的X锁，说明这里insert是对该记录加记录行锁的

总结

insert的加锁策略是，分为两个阶段：

1. 第一阶段，先进行唯一性约束检查，申请LOCK_S 锁
2. 第二阶段，当获取到LOCK_S锁insert成功之后
   1. 插入的位置有Gap锁: LOCK_INSERT_INTENTION（插入意向锁）,为了防止其他insert 唯一键冲突。
   2. 新数据插入:LOCK_X（排他锁） + LOCK_REC_NOT_GAP（记录锁）

死锁案例：唯一索引下的insert死锁分析 1、环境介绍

针对mysql数据库，innodb 引擎下，隔离级别在 REPEATABLE READ 出现的死锁问题

本案例是当两个事务并发insert ，出现唯一键冲突 和gap锁一起导致的死锁案例

CREATE TABLE `my2` (

`id` int(11) NOT NULL AUTO_INCREMENT,

`num1` int(11) DEFAULT NULL,

`num2` int(11) DEFAULT NULL,

PRIMARY KEY (`id`),

unique KEY  `indx_1` (`num1`)

) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8mb4

insert into my2(num1,num2) values(2,3),(5,4),(7,8);

说明：创建一个表 my2 ，id为自增主键索引，num1上有一个唯一索引

2 、事务 *** 作介绍 T2T1begin;begin;insert into my2(num1,num2) values(3,10);insert into my2(num1,num2) values(3,11);insert into my2(num1,num2) values(4,13);

说明：

事务T2：

• 插入一条num1为3的数据

事务T1：

• 插入一条num1为3的数据记录
• 插入一条num1为4的数据记录

3 、死锁日志

数据库查看最近的死锁日志：

show engine innodb status

日志：

------------------------
LATEST DETECTED DEADLOCK
------------------------
2017-09-17 15:15:03 7f78eac15700
*** (1) TRANSACTION:
TRANSACTION 462308661, ACTIVE 6 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 360, 1 row lock(s), undo log entries 1
MySQL thread id 3796966, OS thread handle 0x7f78ead9d700, query id 781045166 localhost root update
insert into my2(num1,num2) values(3,10);
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 231 page no 4 n bits 72 index `indx_1` of table `mydatabase`.`my2` trx id 462308661 lock mode S waiting
*** (2) TRANSACTION:
TRANSACTION 462308660, ACTIVE 43 sec inserting, thread declared inside InnoDB 5000
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 3796960, OS thread handle 0x7f78eac15700, query id 781045192 localhost root update
insert into my2(num1,num2) values(4,13);
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 231 page no 4 n bits 72 index `indx_1` of table `mydatabase`.`my2` trx id 462308660 lock_mode X locks rec but not gap
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 231 page no 4 n bits 72 index `indx_1` of table `mydatabase`.`my2` trx id 462308660 lock_mode X locks gap before rec insert intention waiting
*** WE ROLL BACK TRANSACTION (1)

死锁日志情况分析：

a. 事务T1 也即sess2执行的事务

sess2 先执行 sql insert into my2(num1,num2) values(3,10); 其直接插入成功，根据加锁情况日志看，这时候会加上一个排他锁，对记录加上一个行记录锁

b. 事务T2的日志也即sess1执行的事务

其执行 insert into my(num1,num2) values(3,10); 因为该num1为3的记录已经存在，其发生唯一约束冲突，需要申请对冲突的唯一索引加上S Next-key Lock (也即是 lock mode S waiting ) 这是一个间隙锁会申请锁住num1为[2,3],[3,5]之间的gap区域，这时候肯定会等待上面的insert的锁释放

c. 事务T1 也即sess2执行的事务

sess2 再执行 sql insert into my2(num1,num2) values(4,13); 其在锁住的gap区间内，其其需要等待事务T1的S-Next-key Lock锁释放，在日志中显示lock_mode X locks gap before rec insert intention waiting，从而有互相等待的过程，出现死锁