MySQL死锁的检测、预防与解决
2023-08-082.4k 阅读
MySQL 死锁的基本概念
什么是死锁
死锁是指两个或多个事务在执行过程中，因争夺资源而造成的一种互相等待的现象，若无外力作用，这些事务都将无法推进下去，从而导致系统处于僵持状态。在 MySQL 数据库中，死锁通常发生在多个事务试图以不同顺序锁定相同的一组资源时。例如，事务 A 持有资源 X 的锁，同时请求资源 Y 的锁；而事务 B 持有资源 Y 的锁，同时请求资源 X 的锁，这时就形成了死锁。
死锁产生的条件
- 互斥条件：资源在同一时刻只能被一个事务访问。例如，在 MySQL 中，对于行级锁，一行数据在某一时刻只能被一个事务加锁并进行修改操作。如果多个事务都试图修改同一行数据，就会产生资源竞争。
- 占有并等待条件：事务已经持有了至少一个资源，但又提出了新的资源请求，而该资源已被其他事务占有，此时事务会等待其他事务释放资源。比如，事务 A 已经锁定了表中的一行数据，然后又试图锁定另一行数据，而这另一行数据被事务 B 锁定，事务 A 就会进入等待状态。
- 不可剥夺条件：事务所获得的资源在未使用完之前，不能被其他事务强行剥夺，只能由获得该资源的事务自己释放。在 MySQL 中，一旦一个事务获得了某个锁，其他事务不能强制拿走这个锁，只能等待持有锁的事务主动释放。
- 循环等待条件：存在一个事务链，其中每个事务都在等待下一个事务释放其所需要的资源，形成一个环形等待关系。例如，事务 A 等待事务 B 释放资源，事务 B 等待事务 C 释放资源，而事务 C 又等待事务 A 释放资源，这就构成了循环等待，从而导致死锁。
MySQL 死锁的检测机制
自动检测机制
MySQL 有一套自动检测死锁的机制。InnoDB 存储引擎会在事务等待锁的时间超过一定阈值时，自动启动死锁检测。当检测到死锁时，InnoDB 会选择一个牺牲者（Victim）事务，回滚该事务，并释放它持有的所有锁，以便其他事务能够继续执行。
在 InnoDB 中，检测死锁的算法通常基于等待图（Wait-for Graph，WFG）。等待图是一个有向图，其中节点表示事务，边表示事务之间的等待关系。如果等待图中存在环，就意味着发生了死锁。InnoDB 会定期检查等待图，一旦发现环，就会判定发生死锁，并选择合适的事务进行回滚。
检测相关参数
- innodb_lock_wait_timeout：这个参数用于设置事务等待锁的最长时间，单位是秒。默认值是 50 秒。如果一个事务等待锁的时间超过了这个值，就会抛出
Lock wait timeout exceeded; try restarting transaction错误。例如，将该参数设置为 30：
SET innodb_lock_wait_timeout = 30;
- innodb_deadlock_detect：该参数用于控制 InnoDB 是否开启死锁自动检测功能。默认值为
ON，表示开启。如果将其设置为OFF，InnoDB 将不再自动检测死锁，这在某些特殊场景下，如高并发且死锁发生概率极低的情况下，可能会减少系统开销，但同时也会导致死锁无法被及时发现和处理。关闭死锁检测可以使用以下语句：
SET innodb_deadlock_detect = OFF;
MySQL 死锁的预防策略
优化事务设计
- 减少事务持有锁的时间：尽量缩短事务的执行时间，避免长时间持有锁。例如，在一个事务中，如果有一些操作并不需要锁资源，可以将这些操作移到事务之外执行。假设我们有一个电商系统，在处理订单时，可能需要更新库存、记录订单日志等操作。更新库存操作需要加锁，而记录订单日志操作可以在更新库存完成后，在事务外执行。
-- 优化前
START TRANSACTION;
UPDATE products SET stock = stock - 1 WHERE product_id = 1;
INSERT INTO order_logs (order_id, log_message) VALUES (1, 'Order processed');
COMMIT;
-- 优化后
START TRANSACTION;
UPDATE products SET stock = stock - 1 WHERE product_id = 1;
COMMIT;
INSERT INTO order_logs (order_id, log_message) VALUES (1, 'Order processed');
- 合理安排事务内的操作顺序：确保多个事务以相同的顺序访问资源。例如，在一个涉及多个表的操作中，所有事务都按照相同的表顺序进行锁定和操作。假设有两个表
table1和table2，多个事务都先对table1进行操作，再对table2进行操作，这样可以避免因操作顺序不同而导致的死锁。
-- 事务 A
START TRANSACTION;
UPDATE table1 SET column1 = 'value1' WHERE id = 1;
UPDATE table2 SET column2 = 'value2' WHERE id = 1;
COMMIT;
-- 事务 B
START TRANSACTION;
UPDATE table1 SET column1 = 'value3' WHERE id = 2;
UPDATE table2 SET column2 = 'value4' WHERE id = 2;
COMMIT;
锁粒度控制
- 使用合适的锁粒度：MySQL 支持不同粒度的锁，如行级锁、表级锁等。行级锁粒度小，并发性能好，但加锁和解锁的开销相对较大；表级锁粒度大，并发性能较差，但加锁和解锁的开销相对较小。在实际应用中，应根据业务场景选择合适的锁粒度。对于读多写少的场景，可以适当使用表级共享锁，提高并发读的效率；对于写操作较多的场景，应尽量使用行级锁，减少锁冲突。 例如，在一个论坛系统中，帖子的查看操作（读操作）较多，而回复操作（写操作）相对较少。对于查看帖子操作，可以使用表级共享锁：
LOCK TABLES posts READ;
SELECT * FROM posts WHERE post_id = 1;
UNLOCK TABLES;
而对于回复帖子操作，使用行级锁：
START TRANSACTION;
SELECT * FROM posts WHERE post_id = 1 FOR UPDATE;
INSERT INTO replies (post_id, reply_content) VALUES (1, 'This is a reply');
COMMIT;
- 避免不必要的锁升级：在 InnoDB 中，当一个事务对同一页中的多个行进行加锁操作时，如果满足一定条件，会发生锁升级，将行级锁升级为页级锁甚至表级锁，这会大大降低并发性能，并增加死锁的风险。要避免这种情况，应尽量减少在同一页中对大量行进行频繁的加锁操作。
事务隔离级别调整
- 了解不同隔离级别对死锁的影响：MySQL 支持多种事务隔离级别，包括读未提交（Read Uncommitted）、读已提交（Read Committed）、可重复读（Repeatable Read）和串行化（Serializable）。不同的隔离级别对锁的使用和死锁的发生概率有不同的影响。
- 读未提交：这是最低的隔离级别，事务可以读取其他事务未提交的数据。这种隔离级别下，锁的使用相对较少，死锁发生的概率也较低，但可能会出现脏读问题。
- 读已提交：事务只能读取其他事务已提交的数据。在这个隔离级别下，InnoDB 使用的是基于行级的锁，并发性能较好，但在高并发场景下，仍然可能发生死锁。
- 可重复读：这是 InnoDB 的默认隔离级别。在一个事务内，多次读取同一数据的结果是一致的。在这个隔离级别下，InnoDB 使用间隙锁（Gap Lock）来防止幻读，这可能会增加死锁的发生概率。
- 串行化：这是最高的隔离级别，所有事务都按照顺序依次执行，不会发生并发问题，但性能较低，死锁的概念在这种隔离级别下基本不存在，因为不存在并发竞争。
- 根据业务需求选择合适的隔离级别：如果业务对数据一致性要求不是特别高，且对并发性能要求较高，可以选择读已提交隔离级别；如果业务对数据一致性要求较高，且并发量不是特别大，可以选择可重复读隔离级别。例如，在一个简单的新闻发布系统中，对新闻的查看操作可以使用读已提交隔离级别：
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
START TRANSACTION;
SELECT * FROM news WHERE news_id = 1;
COMMIT;
而对于涉及金融交易等对数据一致性要求极高的业务，可能需要使用可重复读隔离级别：
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
START TRANSACTION;
SELECT balance FROM accounts WHERE account_id = 1 FOR UPDATE;
UPDATE accounts SET balance = balance - 100 WHERE account_id = 1;
COMMIT;
MySQL 死锁的解决方法
分析死锁日志
- 查看 InnoDB 错误日志：当 InnoDB 检测到死锁并回滚牺牲者事务时，会在错误日志中记录详细的死锁信息。通过查看错误日志，可以了解死锁发生的时间、涉及的事务、锁定的资源等关键信息，从而分析死锁产生的原因。错误日志的位置通常由
log_error参数指定。例如，在 Linux 系统中，MySQL 的错误日志可能位于/var/log/mysql/error.log。 - 解析死锁日志内容：死锁日志中会包含等待图的相关信息，展示事务之间的等待关系。例如，以下是一段简化的死锁日志示例：
------------------------
LATEST DETECTED DEADLOCK
------------------------
170728 16:07:24
*** (1) TRANSACTION:
TRANSACTION 2918, ACTIVE 10 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 1136, 1 row lock(s)
MySQL thread id 5, OS thread handle 0x7f8c9916c700, query id 45 /192.168.1.100 root update
UPDATE `test_table` SET `column1` = 'value1' WHERE `id` = 1
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 4 n bits 72 index `PRIMARY` of table `test_db`.`test_table` trx id 2918 lock_mode X waiting
*** (2) TRANSACTION:
TRANSACTION 2917, ACTIVE 15 sec inserting
mysql tables in use 1, locked 1
4 lock struct(s), heap size 1136, 3 row lock(s), undo log entries 2
MySQL thread id 6, OS thread handle 0x7f8c9916d700, query id 46 /192.168.1.100 root insert
INSERT INTO `test_table` (`id`, `column1`) VALUES (2, 'value2')
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 0 page no 4 n bits 72 index `PRIMARY` of table `test_db`.`test_table` trx id 2917 lock_mode X locks rec but not gap
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 4 n bits 72 index `PRIMARY` of table `test_db`.`test_table` trx id 2917 lock_mode X locks gap before rec insert intention waiting
*** WE ROLL BACK TRANSACTION (1)
从上述日志中，可以看出事务 2918 在等待获取 test_table 表中 id 为 1 的行的排他锁，而事务 2917 持有 test_table 表中部分行的锁，并等待获取插入意向锁，最终事务 2918 被回滚。
手动处理死锁
- 重启事务：在应用程序中捕获到死锁异常（如
Deadlock found when trying to get lock; try restarting transaction错误）后，最简单的处理方法是捕获异常，然后重新启动事务。例如，在 Java 中使用 JDBC 连接 MySQL 数据库时，可以如下处理：
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
public class DeadlockExample {
public static void main(String[] args) {
Connection conn = null;
PreparedStatement pstmt = null;
boolean success = false;
while (!success) {
try {
conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/test_db", "root", "password");
conn.setAutoCommit(false);
pstmt = conn.prepareStatement("UPDATE `test_table` SET `column1` = 'value1' WHERE `id` = 1");
pstmt.executeUpdate();
pstmt = conn.prepareStatement("UPDATE `test_table` SET `column1` = 'value2' WHERE `id` = 2");
pstmt.executeUpdate();
conn.commit();
success = true;
} catch (SQLException e) {
if (e.getSQLState().equals("40001")) { // 死锁错误 SQL 状态码
try {
if (conn != null) {
conn.rollback();
}
} catch (SQLException ex) {
ex.printStackTrace();
}
System.out.println("Deadlock detected, retrying...");
} else {
e.printStackTrace();
}
} finally {
try {
if (pstmt != null) {
pstmt.close();
}
if (conn != null) {
conn.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
}
- 调整业务逻辑：根据死锁分析的结果，对业务逻辑进行调整。如果发现死锁是由于多个事务对资源的访问顺序不一致导致的，可以统一访问顺序；如果是因为锁持有时间过长，可以优化事务内的操作，减少锁的持有时间。例如，如果发现两个事务在更新不同表时存在死锁风险，可以将相关操作合并到一个事务中，或者确保所有事务按照相同的表顺序进行操作。
分布式系统中的死锁处理
- 分布式死锁检测算法：在分布式数据库环境中，死锁的检测和处理更为复杂。常用的分布式死锁检测算法有超时法、全局等待图法等。超时法是通过设置一个超时时间，如果一个事务等待锁的时间超过这个时间，就认为发生了死锁，并回滚该事务。全局等待图法是在整个分布式系统中维护一个全局等待图，通过检测图中是否存在环来判断是否发生死锁。
- 使用分布式锁管理器：可以引入分布式锁管理器（如 Redis 等）来协调分布式系统中的锁操作。分布式锁管理器可以统一管理锁资源，避免因各个节点独立管理锁而导致的死锁问题。例如，使用 Redis 实现分布式锁时，可以通过
SETNX命令来获取锁，如果获取失败则等待或重试。在释放锁时，使用DEL命令。以下是一个简单的使用 Jedis 库在 Java 中实现分布式锁的示例：
import redis.clients.jedis.Jedis;
public class DistributedLockExample {
private static final String LOCK_KEY = "distributed_lock";
private static final String LOCK_VALUE = "unique_value";
public static void main(String[] args) {
Jedis jedis = new Jedis("localhost", 6379);
boolean locked = false;
try {
long result = jedis.setnx(LOCK_KEY, LOCK_VALUE);
if (result == 1) {
locked = true;
// 执行业务逻辑
System.out.println("Lock acquired, doing business logic...");
} else {
System.out.println("Failed to acquire lock");
}
} finally {
if (locked) {
jedis.del(LOCK_KEY);
System.out.println("Lock released");
}
jedis.close();
}
}
}
通过深入理解 MySQL 死锁的检测、预防与解决方法，可以有效地提高数据库系统的稳定性和并发性能，避免因死锁问题导致的业务中断。在实际应用中，应根据具体的业务场景，综合运用各种策略，确保数据库系统的高效运行。同时，对于分布式系统中的死锁问题，需要采用专门的处理方法，以保障整个分布式环境的可靠性。