解决一次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

在项目的一次需求中，需要对一个表增加字段，然而在执行增加字段的sql语句时，卡住了很久都没提交到Mysql完成，而此时对外接口服务请求也卡住了，这时中断卡住的alter table 语句，服务慢慢恢复正常，如果不搞清楚这个问题的根源，不敢增加字段，因为会直接影响到服务

通过show processlist 查看到在alter table语句执行卡住过程中，累计了大量状态为 Waiting for table metadata lock 的记录

然后查看当前的事务状态 执行 select * from information_schema.innodb_trx\G

发现了其中一条已经运行了很久的事务，我怀疑跟这个运行很久的而且没有提交的事务有关。

在本地mysql开多个终端测试

session 1： 开启事务，执行select 语句，但不提交事务

session 2：执行增加字段sql

执行被阻塞了

可以看到alter table语句的状态为Waiting for table metadata lock

session 3 ： 再次查询t1表

也被阻塞了

select * from t1 再次查询t1表也是 Waiting for table metadata lock状态，说明由于 metadata lock的存在，会导致后面正常的查询都会因为等待锁而阻塞

再查看当前事务运行状态：

可以看到，session1的事务由于还没提交，所以这里能看到它的状态还是running

这时我们commit session1的事务，看看效果

session 1：

session 2：

session 3：

可以看到session1的事务提交后，session2 和session3 都正常执行了， 他们完成的时间分别是30秒和7秒

通过上面的还原测试，可以知道是由于事务没有提交而给表加了锁，导致后面alter语句因为等待锁而阻塞，从而影响后面的正常请求。

那说明我们的项目是默认开启了事务吗？

继续排查，项目是使用flask-sqlchemy的插件来管理mysql接入，然后查了下文档

在实例化sqlchemy的时候，会创建一个用于跟Mysql交互的session对象，看看源码

从 SignallingSession类的定义看来，autocommit=False，说明默认都给所有的sql执行开启事务，也就是说，哪怕是纯select语句，不需要加锁的select，我们的项目默认也需要开启事务，这对于Mysql MVCC的版本控制来说，是没必要的。

解决办法：就是在实例化SQLAlchemy的时候，给一个参数，修改的session的autocommit=True：

来自官网的介绍：

意思就是为了保证事务的串行执行，而启用的一个锁，这个锁只会在事务结束的时候释放，因此在事务提交或回滚钱，任何对这个表做的DDL *** 作，都是会阻塞的

这个 Metadata lock 是MySQL在5.5.3版本后引入了，为的是防止5.5.3以前的一个bug的出现：

当一个会话在主库执行DML *** 作还没提交时，另一个会话对同一个对象执行了DDL *** 作如drop table，而由于MySQL的binlog是基于事务提交的先后顺序进行记录的，因此在从库上应用时，就出现Q了先drop table，然后再向table中insert的情况，导致从库应用出错。

---