mysql死锁场景整理

本文死锁场景皆为工作中遇到（或同事遇到）并解决的死锁场景，写这篇文章的目的是整理和分享，欢迎指正和补充，本文死锁场景包括：

注 ：以下场景隔离级别均为默认的Repeatable Read；

前提 ：表 t_user 的 uid 字段创建了唯一索引，并拥有可更新字段age。

场景复现 ：

相应业务案例和解决方案 ：

该场景常见于事务中存在for循环更新某条记录的情况，死锁日志显示 lock_mode X locks rec but not gap waiting （即行锁而非间隙锁），解决方案：

表结构 :

场景复现 ：

首先查询表中目前存在的记录：

执行两个事务的 *** 作：

死锁原因分析 ：

解决方案 ：

t_user结构改造为：

场景复现 *** 作（几率不高） ：

假设存在以下数据 ：

死锁分析 ：

事务1 ：

① 锁住zone_id=1对应的间隙锁: zoneId in (1,2)

② 锁住索引zone_id=1对应的主键索引行锁id = [1,2]

③ 锁住uid=1对应的间隙锁: uid in (1, 2)

④ 锁住uid=1对应的主键索引行锁: id = [1, 3]

事务2 ：

① 锁住zone_id=2对应的间隙锁: zoneId in (1,2)

② 锁住索引zone_id=2对应的主键索引行锁id = [3,4]

③ 锁住uid=2对应的间隙锁: uid in (1, 2)

④ 锁住uid=2对应的主键索引行锁: id = [2, 4]

解决方案 ：创建联合索引，使执行计划只会用到一个索引。

测试表结构 :

场景复现 *** 作 ：

解决办法：尽量避免这种插入又回滚的场景。

避免死锁的原则：

锁是需要事务结束后才释放的。

一个是 MVCC，一个是两阶段锁协议。

为什么要并发控制呢？是因为多个用户同时 *** 作 MySQL 的时候，为了提高并发性能并且要求如同多个用户的请求过来之后如同串行执行的一样（为了解决脏读、不可重复读、幻读）

官方定义：

两阶段锁协议是指所有事务必须分两个阶段对数据加锁和解锁，在对任何数据进行读、写 *** 作之前，事务首先要获得对该数据的封锁；在释放一个封锁之后，事务不再申请和获得任何其他封锁。

对应到 MySQL 上分为两个阶段：

但是两阶段锁协议不要求事务必须一次将所有需要使用的数据加锁（innodb在需要的索引列数据才锁行），并且在加锁阶段没有顺序要求，所以这种并发控制方式会形成死锁。

MySQL有两种死锁处理方式：

死锁检测 （默认开启）

死锁检测的原理是构建一个以事务为顶点、锁为边的有向图，判断有向图是否存在环，存在即有死锁。

回滚

检测到死锁之后，选择插入更新或者删除的行数最少的事务回滚，基于 INFORMATION_SCHEMA.INNODB_TRX 表中的 trx_weight 字段来判断。

收集死锁信息：

减少死锁：

死锁解决：

---