mysql怎么避免死锁 mysql如何实现锁

mysql死锁场景整理

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

成都一家集口碑和实力的网站建设服务商，拥有专业的企业建站团队和靠谱的建站技术，10余年企业及个人网站建设经验 ,为成都1000+客户提供网页设计制作,网站开发,企业网站制作建设等服务,包括成都营销型网站建设，品牌网站制作，同时也为不同行业的客户提供成都网站建设、网站制作的服务,包括成都电商型网站制作建设,装修行业网站制作建设，传统机械行业网站建设,传统农业行业网站制作建设。在成都做网站,选网站制作建设服务商就选创新互联。

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

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

测试表结构 :

场景复现操作 ：

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

避免死锁的原则：

解决一次mysql死锁问题

多线程开启事务处理。每个事务有多个update操作和一个insert操作（都在同一张表）。

默认隔离级别：Repeatable Read

只有hotel_id=2和hotel_id=11111的数据

逻辑删除原有数据

插入新的数据

根据现有数据情况，update的时候没有数据被更新

报了非常多一样的错

发现居然有死锁。

根据常识考虑，我每个线程（事务）更新的数据都不冲突，为什么会产生死锁？

带着这个问题，打印mysql最近一次的死锁信息

show engine innodb status

显示如下

发现事务1在等待一个锁

事务2也在等待一个锁

而且事物2持有了事物1需要的锁

关于锁的描述，出现了 lock_mode ， gap before rec ， insert intention 等字眼，看不懂说明了什么？说明我关于mysql的锁相关的知识储备还不够。那就开始调查mysql的锁相关知识。

通过搜索引擎，

锁的持有兼容程度如下表

那么再回到死锁日志，可以知道 :

事务1正在获取插入意向锁

事务2正在获取插入意向锁，持有排他gap锁

再看我们上面的锁兼容表格，可以知道， gap lock和insert intention lock是不兼容的

那么就可以推断出： 事务1持有gap lock，等待事务2的insert intention lock释放；事务2持有gap lock，等待事务1的insert intention lock释放，从而导致死锁。

那么新的问题就来了，事务1的intention lock 为什么会和事务2的gap lock 有交集，或者说，事务1要插入的数据的位置为什么会被事务2给锁住？

让我回顾一下gap lock的定义：

间隙锁，锁定一个范围，但不包括记录本身。GAP锁的目的，是为了防止同一事务的两次当前读，出现幻读的情况

那为什么是gap lock，gap lock到底是基于什么逻辑锁的记录？发现自己相关的知识储备还不够。那就开始调查。

调查后发现，当当前索引是一个 普通索引 的时候，会加一个gap lock来防止幻读， 此gap lock 会锁住一个左开右闭的区间。 假设索引为xx_idx（xx_id），数据分布为1,4,6,8,12，当更新xx_id=9的时候，这个时候gap lock的锁定记录区间就是(8，12]，也就是锁住了xxid in (9,10,11,12)的数据，当有其他事务要插入xxid in (9,10,11,12)的数据时，就会处于等待获取锁的状态。

ps：当前索引不是普通索引，而且是唯一索引等其他情况，请参考下面资料

MySQL 加锁处理分析

回到我自己的案例中，重新屡一下事务1的执行过程：

因为普通索引

KEY hotel_date_idx ( hotel_id , rate_date )

的关系 这段sql会获取一个gap lock，范围(2,11111]

这段sql会获取一个insert intention lock （waiting）

再看事务2的执行过程

因为普通索引

KEY hotel_date_idx ( hotel_id , rate_date )

的关系 这段sql也会获取一个gap lock，范围也是(2,11111]（根据前面的知识，gap lock之间会互相兼容，可以一起持有锁的）

这段sql也会获取一个insert intention lock （waiting）

看到这里，基本也就破案了。因为普通索引的关系，事务1和事务2的gap lock的覆盖范围太广，导致其他事务无法插入数据。

重新梳理一下：

所以从结果来看，一堆事务被回滚，只有10007数据被更新成功

gap lock 导致了并发处理的死锁

在mysql默认的事务隔离级别（repeatable read）下，无法避免这种情况。只能把并发处理改成同步处理。或者从业务层面做处理。

共享锁、排他锁、意向共享、意向排他

record lock、gap lock、next key lock、insert intention lock

show engine innodb status

详解MySQL（InnoDB）如何处理死锁

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

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

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

官方定义：

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

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

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

MySQL有两种死锁处理方式：

死锁检测 （默认开启）

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

回滚

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

收集死锁信息：

减少死锁：

死锁解决：

mysql解决死锁问题

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

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

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

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

官方定义：

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

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

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

MySQL有两种死锁处理方式：

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

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

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

MySQL如何处理死锁