MySQL死锁处理策略与优化
2024-01-102.3k 阅读
什么是 MySQL 死锁
在 MySQL 数据库中，死锁是指两个或多个事务在执行过程中，因争夺资源而造成的一种互相等待的现象，若无外力作用，这些事务都将无法推进下去。当死锁发生时，数据库系统通常会选择牺牲一个事务（回滚该事务）来释放资源，让其他事务能够继续执行。
例如，假设有两个事务 T1 和 T2，T1 持有资源 R1 并请求资源 R2，而 T2 持有资源 R2 并请求资源 R1，这样就形成了死锁。
死锁产生的原因
- 资源竞争：多个事务同时访问和修改相同的数据行或表，争夺锁资源。例如，两个事务同时尝试更新同一行数据，每个事务都需要获取该行的排他锁，从而导致死锁。
- 事务顺序不一致：不同事务以不同顺序访问资源。假设事务
A先访问表a再访问表b，而事务B先访问表b再访问表a，如果两个事务同时执行，就可能出现死锁。 - 锁的粒度：MySQL 支持不同粒度的锁，如行锁、表锁等。如果锁的粒度选择不当，也可能引发死锁。例如，使用表锁时，若多个事务同时对表进行操作，很容易产生冲突。
死锁示例代码
下面通过一段简单的代码示例来演示死锁的发生。
首先，创建两个表 table1 和 table2：
CREATE TABLE table1 (
id INT PRIMARY KEY,
data VARCHAR(50)
);
CREATE TABLE table2 (
id INT PRIMARY KEY,
data VARCHAR(50)
);
然后，模拟两个事务并发执行：
事务 1：
START TRANSACTION;
UPDATE table1 SET data = 'new data 1' WHERE id = 1;
UPDATE table2 SET data = 'new data 2' WHERE id = 1;
COMMIT;
事务 2：
START TRANSACTION;
UPDATE table2 SET data = 'new data 3' WHERE id = 1;
UPDATE table1 SET data = 'new data 4' WHERE id = 1;
COMMIT;
如果这两个事务同时执行，就很可能发生死锁。事务 1 先锁定 table1 的 id = 1 行，事务 2 先锁定 table2 的 id = 1 行，然后事务 1 尝试锁定 table2 的 id = 1 行，事务 2 尝试锁定 table1 的 id = 1 行，从而形成死锁。
MySQL 死锁检测机制
InnoDB 死锁检测
InnoDB 存储引擎具备自动死锁检测机制。它会在事务等待锁的时间超过一定阈值时，自动检测是否存在死锁。如果检测到死锁，InnoDB 会选择一个回滚代价最小的事务进行回滚，以打破死锁局面。
回滚代价主要考虑以下因素：
- 事务已修改和锁定的行数：修改和锁定行数越少，回滚代价越小。
- 事务已执行的时间：执行时间越短，回滚代价越小。
死锁检测相关参数
- innodb_deadlock_detect：该参数控制 InnoDB 是否启用死锁自动检测，默认值为
ON。如果将其设置为OFF，InnoDB 将不会自动检测死锁，这可能会导致死锁一直存在，直到事务超时报错。但在某些高并发场景下，关闭死锁检测可以减少检测带来的性能开销。 - innodb_lock_wait_timeout：此参数定义了一个事务等待获取锁的最长时间，默认值为 50 秒。如果一个事务等待锁的时间超过这个值，就会抛出
Lock wait timeout exceeded; try restarting transaction错误。
MySQL 死锁处理策略
事务回滚
当 MySQL 检测到死锁时，会自动选择一个事务进行回滚，以释放资源，让其他事务继续执行。被回滚的事务会收到一个 1213 (40001) 错误代码，表示发生了死锁。
应用程序可以捕获这个错误，并根据业务需求决定是否重新执行事务。例如，在 Java 中，可以使用如下代码捕获死锁异常并重新执行事务：
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
public class DeadlockExample {
private static final String URL = "jdbc:mysql://localhost:3306/yourdatabase";
private static final String USER = "yourusername";
private static final String PASSWORD = "yourpassword";
public static void main(String[] args) {
int maxRetries = 3;
for (int i = 0; i < maxRetries; i++) {
try (Connection conn = DriverManager.getConnection(URL, USER, PASSWORD)) {
conn.setAutoCommit(false);
String updateQuery = "UPDATE table1 SET data =? WHERE id =?";
try (PreparedStatement pstmt = conn.prepareStatement(updateQuery)) {
pstmt.setString(1, "new data");
pstmt.setInt(2, 1);
pstmt.executeUpdate();
updateQuery = "UPDATE table2 SET data =? WHERE id =?";
try (PreparedStatement pstmt2 = conn.prepareStatement(updateQuery)) {
pstmt2.setString(1, "new data");
pstmt2.setInt(2, 1);
pstmt2.executeUpdate();
}
conn.commit();
System.out.println("Transaction completed successfully.");
break;
} catch (SQLException e) {
if (e.getSQLState().equals("40001")) {
System.out.println("Deadlock detected. Retrying transaction...");
conn.rollback();
} else {
System.out.println("Other SQL error: " + e.getMessage());
conn.rollback();
break;
}
}
} catch (SQLException e) {
System.out.println("Database connection error: " + e.getMessage());
}
}
}
}
调整事务顺序
通过确保所有事务以相同的顺序访问资源，可以避免死锁。例如，在前面的示例中，如果两个事务都先更新 table1，再更新 table2，就不会发生死锁。
调整后的事务 1：
START TRANSACTION;
UPDATE table1 SET data = 'new data 1' WHERE id = 1;
UPDATE table2 SET data = 'new data 2' WHERE id = 1;
COMMIT;
调整后的事务 2：
START TRANSACTION;
UPDATE table1 SET data = 'new data 3' WHERE id = 1;
UPDATE table2 SET data = 'new data 4' WHERE id = 1;
COMMIT;
减小锁的粒度
- 行锁与表锁：在可能的情况下，尽量使用行锁而不是表锁。行锁只锁定需要修改的行，而表锁会锁定整个表，从而减少锁冲突的可能性。InnoDB 默认使用行锁，但在某些情况下（如
SELECT... FOR UPDATE语句中未命中索引时）可能会升级为表锁。
例如，假设有一个表 orders，包含 order_id、customer_id 和 order_amount 字段。如果要更新某个客户的所有订单金额，可以使用以下两种方式：
使用表锁：
LOCK TABLES orders WRITE;
UPDATE orders SET order_amount = order_amount * 1.1 WHERE customer_id = 1;
UNLOCK TABLES;
使用行锁：
START TRANSACTION;
UPDATE orders SET order_amount = order_amount * 1.1 WHERE customer_id = 1;
COMMIT;
- 意向锁：InnoDB 使用意向锁来协调行锁和表锁。意向锁分为意向共享锁（IS）和意向排他锁（IX）。当一个事务想要获取某行的共享锁时，会先获取表的意向共享锁；当想要获取某行的排他锁时，会先获取表的意向排他锁。这样可以避免在获取行锁时，与其他事务获取表锁产生冲突。
优化事务设计
- 减少事务持有锁的时间：尽量缩短事务的执行时间，尽快释放锁资源。例如，将大事务拆分成多个小事务，在每个小事务中只包含必要的操作。
假设有一个复杂的业务逻辑，需要更新多个表的数据，并进行一些计算。可以将其拆分为多个小事务：
原大事务：
START TRANSACTION;
UPDATE table1 SET column1 = value1 WHERE condition1;
-- 进行复杂计算
UPDATE table2 SET column2 = value2 WHERE condition2;
COMMIT;
拆分后的小事务：
START TRANSACTION;
UPDATE table1 SET column1 = value1 WHERE condition1;
COMMIT;
-- 进行复杂计算
START TRANSACTION;
UPDATE table2 SET column2 = value2 WHERE condition2;
COMMIT;
- 合理使用锁：避免不必要的锁获取。例如，在只读事务中，可以使用共享锁（
SELECT... LOCK IN SHARE MODE）来读取数据，而不是排他锁（SELECT... FOR UPDATE），这样可以允许其他事务同时读取数据，减少锁冲突。
死锁日志分析
MySQL 会将死锁相关信息记录在错误日志中。通过分析这些日志，可以了解死锁发生的原因和过程，从而针对性地进行优化。
死锁日志通常包含以下信息：
- 死锁发生的时间：记录死锁发生的具体时刻。
- 事务信息：涉及死锁的事务 ID、事务执行的 SQL 语句等。
- 锁信息：事务请求和持有的锁类型、锁对象等。
例如，以下是一段死锁日志的示例：
2023-10-01T12:34:56.789Z 12345 [ERROR] InnoDB: Deadlock found when trying to get lock; trying to break the deadlock by rolling back transaction
2023-10-01T12:34:56.789Z 12345 [INFO] InnoDB: *** (1) TRANSACTION:
TRANSACTION 123456789, ACTIVE 10 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 1
MySQL thread id 12345, OS thread handle 1234567890, query id 1234567890 192.168.1.100 user1 update
UPDATE table1 SET data = 'new data' WHERE id = 1
2023-10-01T12:34:56.789Z 12345 [INFO] InnoDB: *** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 131 page no 3 n bits 72 index `PRIMARY` of table `test`.`table1` trx id 123456789 lock_mode X waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
0: len 4; hex 80000001; asc ;;
1: len 6; hex 000000000001; asc ;;
2: len 7; hex 100000012e0110; asc . . ;;
2023-10-01T12:34:56.789Z 12345 [INFO] InnoDB: *** (2) TRANSACTION:
TRANSACTION 987654321, 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 67890, OS thread handle 0987654321, query id 987654321 192.168.1.101 user2 insert
INSERT INTO table1 (id, data) VALUES (2, 'new data')
2023-10-01T12:34:56.789Z 12345 [INFO] InnoDB: *** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 131 page no 3 n bits 72 index `PRIMARY` of table `test`.`table1` trx id 987654321 lock_mode X
Record lock, heap no 2 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
0: len 4; hex 80000001; asc ;;
1: len 6; hex 000000000001; asc ;;
2: len 7; hex 100000012e0110; asc . . ;;
2023-10-01T12:34:56.789Z 12345 [INFO] InnoDB: *** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 131 page no 3 n bits 72 index `PRIMARY` of table `test`.`table1` trx id 987654321 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
0: len 4; hex 80000001; asc ;;
1: len 6; hex 000000000001; asc ;;
2: len 7; hex 100000012e0110; asc . . ;;
2023-10-01T12:34:56.789Z 12345 [INFO] InnoDB: *** WE ROLL BACK TRANSACTION (2)
通过分析这段日志，可以看出事务 123456789 在等待获取 table1 表中 id = 1 行的排他锁，而事务 987654321 持有 table1 表中 id = 1 行的排他锁，并在等待插入新记录时获取锁，从而导致死锁。MySQL 选择回滚事务 987654321 来打破死锁。
MySQL 死锁优化实践
高并发场景下的优化
在高并发场景中，死锁更容易发生。以下是一些针对高并发场景的优化建议：
- 数据库连接池优化：合理配置数据库连接池的参数，如最大连接数、最小连接数、连接超时时间等。避免连接池过小导致事务等待连接，也避免连接池过大消耗过多系统资源。
- 分布式锁：在分布式系统中，可以使用分布式锁（如 Redis 分布式锁）来协调多个节点之间的资源访问，减少数据库锁的竞争。
实际案例分析
- 案例一：某电商系统在高并发下单时频繁出现死锁。经过分析，发现多个事务在处理订单时，对订单表和库存表的访问顺序不一致。有的事务先更新订单表，再更新库存表；而有的事务则相反。通过统一事务对这两个表的访问顺序，先更新库存表，再更新订单表，成功解决了死锁问题。
- 案例二：一个社交平台在用户发布动态时出现死锁。原因是发布动态涉及多个表的操作，包括用户表、动态表、点赞表等。事务持有锁的时间过长，导致其他事务等待锁的时间也变长，增加了死锁的可能性。通过将大事务拆分成多个小事务，每个小事务只处理一个表的操作，大大减少了锁的持有时间，从而避免了死锁。
性能测试与监控
- 性能测试工具：使用工具如
sysbench、mysqlslap等对数据库进行性能测试。通过模拟高并发场景，检测是否存在死锁问题，并评估系统的性能瓶颈。 - 监控指标：关注数据库的关键性能指标，如
InnoDB row lock waits（InnoDB 行锁等待次数）、InnoDB row lock time（InnoDB 行锁等待时间）等。这些指标可以帮助及时发现潜在的死锁风险。
定期优化与维护
- 索引优化：定期检查和优化数据库索引，确保查询能够快速定位数据，减少锁等待时间。例如，通过
EXPLAIN关键字分析查询语句的执行计划，查看索引是否被正确使用。 - 统计信息更新：及时更新数据库的统计信息，让查询优化器能够生成更准确的执行计划。可以使用
ANALYZE TABLE语句更新表的统计信息。
预防死锁的最佳实践
- 编写事务时遵循相同的资源访问顺序：确保所有事务以一致的顺序访问资源，减少死锁的可能性。
- 避免长事务：尽量缩短事务的执行时间，减少锁的持有时间。
- 合理使用锁：根据业务需求选择合适的锁类型和锁粒度，避免不必要的锁竞争。
- 监控与预警：建立完善的监控机制，实时监测数据库的锁状态和性能指标，及时发现并预警潜在的死锁问题。
通过以上死锁处理策略与优化方法，可以有效减少 MySQL 数据库中死锁的发生，提高系统的稳定性和性能。在实际应用中，需要根据具体的业务场景和数据库负载情况，灵活选择和应用这些方法。