Mysql死锁问题探究

1. 场景复现
2. 示例
3. 表结构
4. SQL执行过程
5. 分析原因
6. 解决方案
7. 总结

为了培养自己的并发思维和意识，最近在写一个接口的时候用JMeter并发测试了一下，结果还真发现了“喜闻乐见”的DeadLock（死锁）。

1. 场景复现
   由于项目中那个接口业务逻辑太复杂，所以将核心部分抽象成如下示例。

2. 示例
   对于表name_info，启动A、B两个事物（设置了手动提交，使用默认事务隔离级别RR）做如下操作：

A事物插入一个depart_id为1的数据；
B事物同样插入depart_id为1的数据；
A事物修改depart_id为1的数据;
B事物修改depart_id为1的数据;
2. 表结构
CREATE TABLE name_info (
id int NOT NULL AUTO_INCREMENT,
name varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT '',
create_time datetime DEFAULT NULL,
status varchar(255) DEFAULT NULL,
count int NOT NULL DEFAULT '0',
depart_id int DEFAULT NULL,
PRIMARY KEY (id),
KEY depart_id_idx (depart_id)
)

3. SQL执行过程
   事物A：

1、mysql> begin;
Query OK, 0 rows affected (0.00 sec)

2、mysql> select * from name_info;
Empty set (0.00 sec)

3、mysql> insert into name_info(name,depart_id) values('a',1);
Query OK, 1 row affected (0.02 sec)

4、mysql> select * from name_info;
+----+------+-------------+--------+-------+-----------+
| id | name | create_time | status | count | depart_id |
+----+------+-------------+--------+-------+-----------+
| 21 | a | NULL | NULL | 0 | 1 |
+----+------+-------------+--------+-------+-----------+
1 row in set (0.00 sec)

5、mysql> update name_info set name='b' where depart_id=1;
----blocking----
----blocking----
----blocking----
----事物B执行步骤5以后----
Query OK, 1 row affected (9.76 sec)
Rows matched: 1 Changed: 1 Warnings: 0

6、mysql> select * from name_info;
+----+------+-------------+--------+-------+-----------+
| id | name | create_time | status | count | depart_id |
+----+------+-------------+--------+-------+-----------+
| 21 | b | NULL | NULL | 0 | 1 |
+----+------+-------------+--------+-------+-----------+
1 row in set (0.00 sec)

事物B：

1、mysql> begin;
Query OK, 0 rows affected (0.00 sec)
2、mysql> select * from name_info;
Empty set (0.00 sec)
3、mysql> insert into name_info(name,depart_id) values('a',1);
Query OK, 1 row affected (0.00 sec)
4、mysql> select * from name_info;
+----+------+-------------+--------+-------+-----------+
| id | name | create_time | status | count | depart_id |
+----+------+-------------+--------+-------+-----------+
| 22 | b | NULL | NULL | 0 | 1 |
+----+------+-------------+--------+-------+-----------+
1 row in set (0.00 sec)
5、mysql> update name_info set name='b' where depart_id=1;
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction
6、mysql> select * from name_info;
Empty set (0.00 sec)

执行顺序为A-1,B-1,A-2,B-2,A-3,B-3,A-4,B-4,A-5,B-5。通过上述操作可以看到两个现象：一个是事物A在执行步骤5的时候阻塞了；另一个是事物B在执行步骤5的时候死锁了。

2. 分析原因
   查看最近的死锁日志：
   mysql> SHOW ENGINE INNODB STATUS;
   结果如下：
   事物A
   

事物B 

可以发现因为插入depart_id=1的数据的时候A、B事物都在要插入的索引位置加了X锁，heap no不同，在A事物修改depart_id=1的数据的时候，涉及到的数据除了自己插入的数据，还有事物B插入的数据，因为事物B持有的X锁还没释放，所以A事物阻塞。而在B事物修改depart_id=1的数据的时候，同样的道理他需要等待A事物持有的X锁，出现互相等待对方的锁即死锁，MySQL检测到以后抛出异常并将B事物回滚，A事物在B事物回滚释放锁以后修改成功。

3. 解决方案
   对于实际项目中遇到的这种情况，我选择先插入数据，然后根据各自事物中插入成功之后的主键id来修改对应的记录，而不是依据depart_id这样的索引字段，这样各个事物只处理各自已经获得了锁的数据就不会出现互相等待对方锁的情况，从而避免死锁。

4. 总结
   在事物中应该各守本分，避免在自己事物中处理另一个事物加了锁的数据。
   出现死锁之后应该第一时间用SHOW ENGINE INNODB STATUS看死锁日志（我当时项目中碰到这种情况的时候一根筋通过业务日志来排查，经过一天的调试也算是大概看出了问题所在，真累~）。