一次线上Mysql死锁分析

在数据库实战开发的过程中，我们经常会遇到一些这样那样的问题，然后要卡好半天，等问题解决了才发现原来一些细节知识点还是没有掌握好。今天golang学习网就整理分享《一次线上Mysql死锁分析》，聊聊MySQL、死锁，希望可以帮助到正在努力赚钱的你。

发生死锁的是用户地址表，先贴下表结构：

CREATE TABLE

user_address

(

addr_id

int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT '地址id',

user_id

int(11) DEFAULT NULL COMMENT '用户id',

mobile

varchar(20) DEFAULT NULL COMMENT '联系人手机',

is_default

tinyint(4) DEFAULT '0' COMMENT '是否默认地址（0.非默认，1默认）',
PRIMARY KEY (

addr_id

),
KEY

idx_user_id

(

user_id

) USING BTREE,
) ENGINE=InnoDB AUTO_INCREMENT=1 CHARSET=utf8;

为了简化分析和安全性，上表省略了许多字段，但不影响我们分析；

这个表用来保存用户的收货地址，下单时需要选择相应的收货地址；通过查看日志平台日志，出问题的主要发生在添加地址的场景。

添加时执行的SQL如下：

INSERT INTO

oneplus_user

.

user_address

(

user_id

,

mobile

)
VALUES ('1', '1361234567');

update user_address set is_default=( case when addr_id=100 then 1 else 0 end)
where user_id = 1 and (addr_id = 100 or is_default = 1)

即先添加地址，示例中添加后返回的ID是100，然后更新默认地址。

发现问题后，第一时间断定是多次请求了，因为通过相关SQL语句分析，上面的语句只会影响到某个用户自己的地址，没有操作太多的数据；

通过查看日志平台的日志，也证实了假设。

有一个请求在 10:00:44.895  插入成功，然后开始执行Update语句，而同一个用户的另一个请求在 10:00:44.130插入成功，从这个时间至 10:00:45.523修改成功

即在44.895开始两个事务是重叠在跑的，造成了死锁。

为什么同一个用户请求同时出现2次，这个后面分析；先分析执行同样的SQL，什么会发生死锁呢，可以复现下，先插入数据：

INSERT INTO

oneplus_user

.

user_address

(

addr_id

,

user_id

,

mobile

)
VALUES (100, '1','13612345677');

在两个查询窗口，分别执行以下语句：

set autocommit=0;
INSERT INTO

oneplus_user

.

user_address

(

user_id

,

mobile

)
VALUES ('1','13612345678');

update user_address set is_default=( case when addr_id=100 then 1 else 0 end)
where user_id = 1 and (addr_id = 100 or is_default = 1);

其中1为用户ID，100为已经存的地址ID；先都执行插入语句，再执行update，必然死锁。

为什么会死锁呢，我们分析下上面的SQL执行过程中获取的锁情况：

假设用户（ID为1）只有一条地址，即addr_id为100，则在执行update语句的时候需要获取3个锁：

锁1、idx_user_id索引中primary为100的记录 (Innodb非聚集索引只存聚集索引的值)，即[1, 100]这行记录

锁2、Primary索引(addr_id字段)值为100的记录；

锁3、间隙锁, idx_user_id  [1, 100]以后的记录进行加锁，即[(1,100), 正无穷)，即地址表中user_id为1以后的记录不允许有新的记录插入，这样做是为了保证事务级别； 

事务1得到了锁1和锁2，事务2在执行插入语句的时候与锁3有冲突，所以事务1等待锁1；

事务2在执行update的时候请求锁1和锁2; 

这样就出现了死锁的条件，互相拥有对方想获取的锁，又想获取对方的锁。

还有一个问题，为什么同一个用户添加地址的请求同时会出现2条，发现代码中用了Redis锁，锁定时间是2秒，但数据库因为用的是5.6版本的，无法设置超时时间，也没有定时查询过长时间的查询机制，导致应用服务器认为请求超时了，但后台Mysql还在执行，所以前面的SQL还没执行完，新的请求又进来了。

解决方案有两个：

1、Redis锁和数据库执行时间保持一致；

2、Update语句写简单点，先找出更新之前哪条是默认的，直接将这条更新为非默认就行了；

Mysql中间件360 Atlas踩坑
Mysql Proxy盘点

到这里，我们也就讲完了《一次线上Mysql死锁分析》的内容了。个人认为，基础知识的学习和巩固，是为了更好的将其运用到项目中，欢迎关注golang学习网公众号，带你了解更多关于mysql的知识点！