了解速响应：当MySQL遇到死锁时，你应该怎么做

msmkmm2012

在数据库管理中，死锁是一个常见且复杂的问题，尤其是当多个事务并发操作时。本文将深入探讨MySQL中的死锁问题。随着行业的影响力不断扩大，图数据库有哪些的生意也在不断的蔓延，市场的发展也在逐步推进。悦数图数据库是一款完全自主研发的国产图数据库和原生分布式图数据库,具有高性能,易扩展,安全稳定,自主可控的特点.万亿级数据仅需毫秒级查询延时,应用于金融风控,实时推荐,知识图谱等业务场景。https://www.yueshu.com.cn/

1.死锁是什么
死锁是数据库中的一种现象，当两个或多个事务互相等待对方释放资源时发生。每个事务都持有另一个事务所需的资源，并且都在等待这些资源被释放，结果就是所有涉及的事务都法继续执行，形成了一种僵局。

2.死锁是如何产生的
死锁在MySQL中主要由以下几种情况引起

并发事务：当多个事务试图同时访问同一资源。
锁定顺序不一致：如果事务以不同的顺序获取锁，可能会导致循环等待。
长时间运行的事务：长事务期间持有的锁可能导致其他事务等待。
锁升级：从行级锁升级到表级锁，增加了死锁的可能性。
3. 死锁会导致什么问题
死锁对数据库系统的影响主要体现在

系统性能下降：死锁导致事务等待时间增加，降低系统吞吐量。
资源浪费：死锁占用系统资源，其他正常事务法获取所需资源。
用户体验差：长时间的事务等待可能导致用户操作卡顿或失败。
4.如何监控死锁
SHOW ENGINE INNODB STATUS命令
这个命令可以提供比较近一次死锁的信息。它会返回大量关于InnoDB存储引擎状态的数据，包括比较新的死锁事件。

Performance Schema 库
可以通过查询performance_schema中的特定表来监控死锁。关键表包括

performance_schema.metadata_locks：用于查看元数据锁。
performance_schema.data_locks：记录当前所有活跃事务持有的锁信息。
performance_schema.data_lock_waits：此表记录了当前等待锁的请求，可以帮助识别潜在的死锁。
你可以通过以下SQL语句查询死锁相关信息

复制
SELECT * FROM performance_schema.data_lock_waits;
1.
5.简单粗暴的解决死锁
查看正在进行中的事务

复制
SELECT * FROM information_schema.INNODB_TRX;
1.
这条查询将返回当前所有InnoDB事务的信息，包括事务ID、状态、开始时间、等待的锁ID（如果有的话）等。

查看正在锁的事务（及其锁信息）

复制
SELECT * FROM performance_schema.data_locks;
1.
这个表并不直接显示哪个事务正在锁哪个资源。要获取这个信息，你需要将data_locks表与information_schema.INNODB_TRX表（或performance_schema.threads表，如果你愿意处理更多的数据）结合起来，通过事务ID来关联它们。

查看等待锁的事务

复制
SELECT * FROM performance_schema.data_lock_waits;
1.
通过这条查询，你可以看到哪些事务正在等待锁，以及哪些事务持有锁并造成阻塞。

杀死死锁进程

复制
KILL [CONNECTION | QUERY] thread_id;
1.
thread_id是要终止的事务对应的MySQL线程ID。

使用KILL CONNECTION会终止整个连接，包括该连接上的所有事务。
使用KILL QUERY只会终止当前正在执行的查询，但连接仍然保持打开状态。
请注意，在杀死事务之前，比较好先尝试理解死锁的原因，并考虑是否有其他更雅的解决方案，比如调整事务的顺序、化查询语句或调整锁的粒度等。杀死事务可能会导致数据不一致或丢失，因此应该谨慎使用。

6.死锁案例演示
假设我们有两个事务T1和T2，分别更新不同行的数据，但这两个行恰好位于同一个页面上。T1先锁定了行A，然后试图锁定行B；与此同时，T2已经锁定了行B，然后尝试锁定行A。这就形成了一个死锁，因为每个事务都在等待另一个事务释放它所需要的锁。

复制
-- T1
START TRANSACTION;
UPDATE accounts SET balance = balance - 100
WHERE account_id = 1; -- 锁定行A


-- 假设这里有一个延迟，模拟际业务操作
-- T2
START TRANSACTION;
UPDATE accounts SET balance = balance + 100
WHERE account_id = 2; -- 锁定行B


UPDATE accounts SET balance = balance - 100
WHERE account_id = 1; -- 尝试锁定行A，等待中...


-- T1继续
UPDATE accounts SET balance = balance + 100
WHERE account_id = 2; -- 尝试锁定行B，等待中...


COMMIT;
-- T2继续
COMMIT;
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
在这个例子中，MySQL会检测到死锁，并选择一个事务进行回滚（通常是代价较小的那个）。另一个事务则可以顺利完成。

7.如何避免死锁
尽量缩短事务持续时间：速完成事务可以减少与其他事务冲突的机会。
保持事务的简单性和原子性：复杂事务应该分解成更小的部分。
遵循固定的锁获取顺序：对于涉及多张表或多行的事务，始终按照相同的顺序获取锁。
使用适当的隔离级别：根据应用需求选择合适的隔离级别。
设计良好的索引：良好的索引可以减少锁争用。
现合理的重试策略：当检测到死锁时，应用程序应能够雅地处理并重试失败的事务。
8.小结
通过上述措施，可以在很大程度上减少死锁的发生频率，提高系统的稳定性和响应速度。定期监控和维护数据库，确保及时发现并解决问题，对维持高效稳定的数据库环境至关重要。