关于mysql死锁问题简单记录

在实际项目中，遇到过多次关于mysql报错日志：

Deadlock found when trying to get lock;try restarting transaction

日志分析后定位到代码是进行了先删除记录，再批量插入数据，且处于多线程环境下，多个事务同时进行

由于删除操作是根据索引列删除的，因此不考虑锁表情况，于是怀疑是间隙锁在多事务下引发的问题

加锁说明

• 不走索引加表锁
• 当where条件为唯一索引时，且查询条件能走索引
  例如：select * from test where id = 10 for update;
  此时会将这一行锁住，这也叫记录锁；
• 当where条件为普通索引，且查询条件能走索引
  例如：select * from test where age=10 for update;
  此时会将索引树上前后两个值的区间锁住，这叫间隙锁
• 对于不存在的数据，都使用间隙锁，不管啥索引都是间隙锁

间隙锁

间隙锁（Gap Lock）是Innodb在可重复读提交下为了解决幻读问题时引⼊的锁机制，幻读的问题存在是因为新增或者更新操作，这时如果进⾏范围查询的时候（加锁查询），会出现不⼀致的问题，这时使⽤不同的⾏锁已经没有办法满⾜要求，需要对⼀定范围内的数据进⾏加锁，间隙锁就是解决这类问题的。在可重复读隔离级别下，数据库是通过⾏锁和间隙锁共同组成的（next-key lock）来实现的。

⾏锁和间隙锁的定义如下所示：

• record lock：⾏锁，也就是仅仅锁着单独的⼀⾏。
• gap lock：间隙锁，仅仅锁住⼀个区间（注意这⾥的区间都是开区间，也就是不包括边界值)。
• next-key lock：record lock+gap lock，所以next-key lock也就半开半闭区间，且是下界开，上界闭。

间隙锁可能是锁定一行数据，也可能是锁定一个区间（间隙）的数据

• 当修改或删除的数据存在时，只会锁定当前行
• 当修改或删除的数据不存在时，会向左找比当前索引值小的值，向右找比当前索引值大的值，将此区间锁住，阻止其他事务在此区间插入数据 ，next-key lock
• 多个事务是可以同时对同一个间隙加锁的，而如果需要对这个间隙插入数据，则需要等待锁释放

定位死锁原因

show engine innodb status

只需关注： LATEST DETECTED DEADLOCK ：最新发现的死锁
可以清楚看到每个事务执行的当前死锁sql，阻塞的状态等

案例复现

数据库出现死锁是相关分析执行命令：

show ENGINES; -- 查看当前执行引擎
show variables like '%storage_engine%' -- 数据库默认使用的存储引擎
show create table table_name -- 表使用的存储引擎

show variables like 'autocommit' -- 查看是否自动提交 OFF：否 ON：是
set autocommit=0  -- 关闭自动提交 0：关闭，1：开启

select * from information_schema.INNODB_LOCKS --查看当前的锁
select * from information_schema.INNODB_LOCK_WAITS -- 查看当前等待的锁
select * from information_schema.INNODB_TRX --查看当前事务

show processlist -- 查看当前所有数据库连接

show status like '%lock%' -- 模糊查询lock相关状态

kill thread_id  -- 杀死阻塞线程

删除再插入

数据库数据

id	age
1	1
10	10
18	18

这里简述一下几个名词：
共享锁（S） 排他锁（X）意向共享锁（IS）意向排他锁（IX）

	S	X
IS	兼容	互斥
IX	互斥	互斥

通过先删除后插入模拟死锁：

事务1	事务2	备注	锁
delete from test where id =12		事务1获取到10-18间隙锁	事务1加S锁
	delete from test where id =13	事务2获取到10-18间隙锁	事务2加S锁
insert into test value(12,12)		事务1加插入意向锁，加锁失败，等待间隙锁释放	事务1加IX锁，与事务2S锁互斥，等待
	insert into test value(13,13)	事务2加插入意向锁，加锁失败，报死锁错误	事务2加IX锁，与事务1S锁互斥，死锁

简单理解就是两个事务分别有间隙锁锁了一个间隙，因为间隙锁不互斥，此时是没有什么问题，假设事务1插入一条数据，且该数据刚好在事务2锁住的间隙中，由于插入时会加插入意向锁，插入意向锁和间隙锁是冲突的，因为间隙锁的作用就是防止幻读。此时事务1会阻塞，等待事务2间隙锁释放。此时事务2也在事务1锁住的间隙中插入数据，也需要加插入意向锁，也阻塞，因此造成了死锁

更新再插入

事务1	事务2	备注
update test set age =14 where id =12		事务1获取到10-18间隙锁
	update test set age =13 where id =13	事务2获取到10-18间隙锁
insert into test value(14,14)		事务1等待事务2加的间隙锁释放
	insert into test value(13,13)	报死锁错误