mysql解决死锁问题

官方定义如下：两个事务都持有对方需要的锁，并且在等待对方释放，并且双方都不会释放自己的锁。

这个就好比你有一个人质，对方有一个人质，你们俩去谈判说换人。你让对面放人，对面让你放人。

看到这里，也许你会有这样的疑问，事务和谈判不一样，为什么事务不能使用完锁之后立马释放呢？居然还要 *** 作完了之后一直持有锁？这就涉及到 MySQL 的并发控制了。

MySQL的并发控制有两种方式，一个是 MVCC，一个是两阶段锁协议。那么为什么要并发控制呢？是因为多个用户同时 *** 作 MySQL 的时候，为了提高并发性能并且要求如同多个用户的请求过来之后如同串行执行的一样（ 可串行化调度 ）。具体的并发控制这里不再展开。咱们继续深入讨论两阶段锁协议。

官方定义：

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

就是说呢，只有遵循两段锁协议，才能实现 可串行化调度 。

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

MySQL有两种死锁处理方式：

由于性能原因，一般都是使用死锁检测来进行处理死锁。

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

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

MySQL如何处理死锁

索引 KEY_TSKTASK_MONTIME (STATUS_ID MON_TIME)

分析 涉及的两条语句应该不会涉及相同的TSK_TASK记录 那为什么会造成死锁呢？

查询MySQL官网文档 发现这跟MySQL的索引机制有关 MySQL的InnoDB引擎是行级锁 我原来的理解是直接对记录进行锁定 实际上并不是这样的

要点如下:

不是对记录进行锁定 而是对索引进行锁定

在UPDATE DELETE *** 作时 MySQL不仅锁定WHERE条件扫描过的所有索引记录 而且会锁定相邻的键值 即所谓的next key locking

如语句UPDATE TSK_TASK SET UPDATE_TIME = NOW() WHERE ID >会锁定所有主键大于等于 的所有记录 在该语句完成之前 你就不能对主键等于 的记录进行 *** 作

当非簇索引(non cluster index)记录被锁定时 相关的簇索引(cluster index)记录也需要被锁定才能完成相应的 *** 作

再分析一下发生问题的两条SQL语句 就不难找到问题所在了

当 update TSK_TASK set STATUS_ID= UPDATE_TIME=now () where STATUS_ID= and MON_TIME

假设 update TSK_TASK set STATUS_ID= UPDATE_TIME=now () where ID in ( ) 几乎同时执行时 本语句首先锁定簇索引(主键) 由于需要更新STATUS_ID的值 所以还需要锁定KEY_TSKTASK_MONTIME 的某些索引记录

这样第一条语句锁定了KEY_TSKTASK_MONTIME 的记录 等待主键索引 而第二条语句则锁定了主键索引记录 而等待KEY_TSKTASK_MONTIME 的记录 在此情况下 死锁就产生了

笔者通过拆分第一条语句解决死锁问题

先查出符合条件的ID select ID from TSK_TASK where STATUS_ID= and MON_TIME <date_sub(now() INTERVAL minute) 然后再更新状态 update TSK_TASK set STATUS_ID= where ID in (… )

至此 死锁问题彻底解决

lishixinzhi/Article/program/MySQL/201311/29601

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

注 ：以下场景隔离级别均为默认的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]

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

测试表结构 :

场景复现 *** 作 ：

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

避免死锁的原则：

---