MySQL锁原理分析 - 死锁案例分析

1、两个Session各两条SQL产生死锁

---

准备数据

# 建表
CREATE TABLE teacher (
	id INT PRIMARY KEY,
	name VARCHAR(10)
);
# 测试数据
INSERT INTO teacher VALUES (1, 'zhangsan');
INSERT INTO teacher VALUES (2, 'lisi');
INSERT INTO teacher VALUES (3, 'wangwu');
INSERT INTO teacher VALUES (4, 'zhaoliu');
INSERT INTO teacher VALUES (5, 'tianqi');

假设存在两个 SESSION 分别开启自己的事务，如下图：

这个死锁非常好理解，也是最常见的死锁：每个事务执行两条SQL，分别持有了一把锁，并期望获取另一个 SESSION 手中的锁来进行下一步操作，死锁因此产生。

2、两个Session各一条SQL产生死锁

---

准备数据

# 建表
CREATE TABLE score (
	std_id INT PRIMARY KEY, 
	chinese INT,
	math INT,
	remark VARCHAR(10)
);
# 创建索引
ALTER TABLE score ADD INDEX idx_score_ch(chinese);
ALTER TABLE score ADD INDEX idx_score_math(math);
# 添加测试数据
INSERT INTO score VALUES (1, 44, 45, NULL);
INSERT INTO score VALUES (2, 43, 45, NULL);
INSERT INTO score VALUES (3, 48, 49, NULL);
INSERT INTO score VALUES (4, 47, 46, NULL);
INSERT INTO score VALUES (5, 50, 50, 'GREAT');

假设存在两个 SESSION 分别开启自己的事务，如下图：

此案例中，虽然每个 SESSION 都只执行了一条语句，但仍旧产生了死锁：

• SESSION A：从【chinese】列对应索引（idx_score_ch）出发：
  索引上的(43, 2)、(44, 1)均满足过滤条件，且不仅会在 idx_score_ch 索引上添加记录X锁，还会在聚簇索引上添加记录X锁，加锁顺序为先(2, 43, 45, NULL)，后(1, 44, 45, NULL)。
• SESSION B：从【math】列对应索引（idx_score_math）出发：
  索引上的(45, 1)、(45, 2)均满足过滤条件，且不仅会在 idx_score_math 索引上添加记录X锁，还会在聚簇索引上添加记录X锁，加锁顺序为先(1, 44, 45, NULL)，后(2, 43, 45, NULL)。

显然 SESSION A 与 SESSION B 加锁顺序正好相反。如果两个 SESSION 恰好都持有了第一把锁，请求另外一个 SESSION 手中的锁，死锁就会发生！

3、总结

---

我们可以将不同 SESSION 在同一时间段内都访问到的数据视为同一组数据，同一组数据可能来源于同一张表，也有可能来源于不同的表。

死锁的发生与否，并不在于事务中有多少条SQL语句，而关键在于：两个或两个以上的 SESSION 对于同一组数据的加锁顺序是否一致，若不一致，死锁就会发生！

我们可以通过分析MySQL每条SQL语句的加锁规则，判断出各种潜在的死锁情况，从而解决我们遇到的问题。

4、解决方法

---

MySQL默认会主动探知死锁，并回滚某一个影响最小的事务。等另一事务执行完成之后，再重新执行该事务。

5、如何避免死锁

---

1. 注意程序的逻辑：根本的原因是程序逻辑的顺序，最常见的是交替更新，如案例1。
2. 保持事务的轻量：越是轻量的事务，占有越少的锁资源，这样发生死锁的几率就越小。
3. 提高运行的速度：避免使用子查询，尽量使用主键等等。
4. 尽量快提交事务：减少持有锁的时间，越早提交事务，锁就越早释放。