一次线上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), '1557059806900', '1557059806917');

 

即先添加地址，示例中添加后返回的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	事务2	备注
1	INSERT INTO `user_address` (`user_id`, `mobile`) VALUES(1, ‘13612345678’);	INSERT INTO.`user_address` (`user_id`, `mobile`) VALUES(1,  ‘13612345678’);
2	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）得到idx_user_id上Primary为(1, 100)的行锁 2）得到Primaray上值为100的行锁
3	提交	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)
4		提交	事务1请求锁3被事务2拦掉； 事务2请求锁1被事务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盘点