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语句写简单点，先找出更新之前哪条是默认的，直接将这条更新为非默认就行了；