MySQL锁等待超时处理
2022-09-167.9k 阅读
MySQL 锁等待超时概念
在 MySQL 数据库中，锁机制是确保数据一致性和并发控制的关键手段。当多个事务同时访问和修改相同的数据时，锁会被用来防止数据冲突。然而，当一个事务试图获取一个已经被其他事务持有的锁时，就会进入等待状态。如果等待时间超过了预先设定的阈值，就会触发锁等待超时错误。
MySQL 中有多种类型的锁，如共享锁（S 锁）和排他锁（X 锁）。共享锁允许多个事务同时读取数据，而排他锁则只允许一个事务对数据进行写操作，并且其他事务在排他锁释放之前无法获取任何类型的锁。当事务 A 持有排他锁对数据进行修改，而事务 B 试图获取排他锁或共享锁时，事务 B 就会进入等待状态。
例如，假设有两个事务 T1 和 T2，T1 先对表 users 中的某一行数据加了排他锁进行更新操作：
START TRANSACTION;
UPDATE users SET balance = balance - 100 WHERE user_id = 1;
-- T1 此时持有对 user_id = 1 这行数据的排他锁
然后 T2 尝试对同一行数据进行读取操作：
START TRANSACTION;
SELECT balance FROM users WHERE user_id = 1;
-- T2 此时会等待 T1 释放锁
如果 T1 长时间不提交事务释放锁，而 T2 的等待时间超过了 MySQL 设定的锁等待超时时间，T2 就会抛出锁等待超时的错误。
锁等待超时的原因分析
- 长事务：长时间运行的事务是导致锁等待超时的常见原因之一。例如，一个事务中包含复杂的业务逻辑，涉及大量的数据处理或长时间的计算，在事务未提交或回滚之前，它所获取的锁会一直保持，从而阻塞其他事务的操作。 假设我们有一个处理订单的事务，其中包括更新订单状态、修改库存以及记录日志等多个操作：
START TRANSACTION;
-- 更新订单状态
UPDATE orders SET status = 'completed' WHERE order_id = 123;
-- 修改库存
UPDATE products SET stock = stock - 1 WHERE product_id = (SELECT product_id FROM order_items WHERE order_id = 123);
-- 记录日志，可能涉及复杂的 I/O 操作
INSERT INTO order_logs (order_id, operation, timestamp) VALUES (123, 'order completed', NOW());
-- 此处如果业务逻辑复杂，导致事务长时间运行
COMMIT;
如果这个事务执行过程中因为某些原因（如复杂的日志记录逻辑或网络延迟）运行时间过长，在这段时间内其他事务如果试图对 orders、products 或 order_logs 表中相关数据进行操作，就可能会等待锁，进而导致锁等待超时。
- 高并发竞争：在高并发环境下，大量事务同时竞争相同的数据资源，很容易出现锁等待超时的情况。当多个事务频繁地对同一行数据进行读写操作时，锁的争用会变得非常激烈。 例如，在一个电商系统的秒杀场景中，大量用户同时抢购同一商品。每个用户的购买操作都可能是一个事务，这些事务都需要对商品库存数据进行更新：
START TRANSACTION;
-- 检查库存
SELECT stock FROM products WHERE product_id = 456;
-- 如果库存足够，更新库存
UPDATE products SET stock = stock - 1 WHERE product_id = 456 AND stock > 0;
COMMIT;
由于大量事务同时执行，很可能出现多个事务同时等待获取商品库存数据的锁，导致部分事务等待超时。
- 锁粒度问题：MySQL 支持不同粒度的锁，包括表级锁、行级锁等。如果锁粒度设置不当，也可能引发锁等待超时。表级锁的粒度较大，会锁定整个表，在高并发情况下，可能会阻塞过多的事务；而行级锁虽然粒度小，但在某些情况下，如索引使用不当，可能会导致锁的范围扩大，从而影响并发性能。 例如，当使用表级锁时：
LOCK TABLES products WRITE;
-- 进行一系列对 products 表的操作
UPDATE products SET price = price * 1.1;
UNLOCK TABLES;
在这个过程中，整个 products 表被锁定，其他事务无法对该表进行任何读写操作，即使只是对表中的某一行数据感兴趣，也会被阻塞，大大增加了锁等待超时的可能性。
- 死锁：死锁是一种特殊的情况，当两个或多个事务相互等待对方释放锁，形成循环等待时，就会发生死锁。虽然 MySQL 有死锁检测机制，会自动回滚其中一个事务来打破死锁，但如果死锁发生频繁，也会导致锁等待超时错误。
假设有两个事务
T3和T4：
-- T3
START TRANSACTION;
SELECT * FROM accounts WHERE account_id = 1 FOR UPDATE;
UPDATE accounts SET balance = balance - 100 WHERE account_id = 1;
-- T3 等待 T4 释放 account_id = 2 的锁
SELECT * FROM accounts WHERE account_id = 2 FOR UPDATE;
-- T4
START TRANSACTION;
SELECT * FROM accounts WHERE account_id = 2 FOR UPDATE;
UPDATE accounts SET balance = balance + 100 WHERE account_id = 2;
-- T4 等待 T3 释放 account_id = 1 的锁
SELECT * FROM accounts WHERE account_id = 1 FOR UPDATE;
在这个例子中，T3 和 T4 相互等待对方释放锁，形成死锁。如果死锁检测机制不能及时发现并处理，就可能导致锁等待超时。
锁等待超时的相关参数
- innodb_lock_wait_timeout：这是 InnoDB 存储引擎中控制锁等待超时时间的关键参数，单位为秒。默认值通常为 50 秒。可以通过以下方式查看当前的值：
SHOW VARIABLES LIKE 'innodb_lock_wait_timeout';
要修改这个参数的值，可以在 MySQL 配置文件（如 my.cnf 或 my.ini）中添加或修改以下行：
[mysqld]
innodb_lock_wait_timeout = 120
修改完成后，重启 MySQL 服务使参数生效。也可以在运行时通过 SQL 语句动态修改：
SET GLOBAL innodb_lock_wait_timeout = 120;
不过，动态修改只对新连接的会话有效，已经存在的会话不受影响。
- innodb_deadlock_detect：这个参数控制 InnoDB 是否启用死锁检测机制。默认值为
ON，表示启用。如果设置为OFF，InnoDB 将不会主动检测死锁，虽然可以减少死锁检测带来的开销，但可能会导致死锁情况无法及时处理，从而增加锁等待超时的概率。 可以通过以下命令查看和修改：
SHOW VARIABLES LIKE 'innodb_deadlock_detect';
SET GLOBAL innodb_deadlock_detect = OFF;
同样，动态修改只对新连接的会话生效。
锁等待超时的监控与诊断
- 使用 SHOW ENGINE INNODB STATUS：这是一个非常有用的命令，它可以提供 InnoDB 存储引擎的详细状态信息，包括锁等待、死锁等情况。通过执行以下命令：
SHOW ENGINE INNODB STATUS\G;
在输出结果中，LATEST DETECTED DEADLOCK 部分会记录最近发生的死锁信息，包括涉及的事务、锁等待情况等。TRANSACTIONS 部分可以查看当前活跃的事务，以及它们持有和等待的锁。例如，从输出中可以看到类似以下的信息：
---TRANSACTION 2837, ACTIVE 62 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 15, OS thread handle 140533030201600, query id 205 127.0.0.1 root updating
UPDATE products SET stock = stock - 1 WHERE product_id = 456 AND stock > 0
------- TRX HAS BEEN WAITING 62 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 13 page no 3 n bits 72 index `PRIMARY` of table `test`.`products` trx id 2837 lock_mode X waiting
Record lock, heap no 3 PHYSICAL RECORD: n_fields 5; compact format; info bits 32
0: len 4; hex 800001c2; asc ;;
1: len 6; hex 000000000b0f; asc ;;
2: len 7; hex 4e000014270110; asc N ' ;;
3: len 10; hex 31303030303030303030; asc 1000000000;;
4: len 4; hex 80000064; asc d;;
从这段信息中，可以知道事务 2837 已经等待锁 62 秒，正在等待对 products 表中 product_id = 456 这一行数据的排他锁。
- 性能_schema 库：MySQL 的
performance_schema库提供了丰富的性能监控数据，包括锁相关的信息。通过查询performance_schema.data_locks表，可以获取当前数据库中正在使用的锁的详细信息，例如：
SELECT * FROM performance_schema.data_locks;
该表中的字段可以展示锁的类型、模式、关联的事务 ID、对象信息等。通过分析这些数据，可以了解锁的争用情况，进而找出可能导致锁等待超时的原因。例如，如果发现某个事务长时间持有锁，而其他事务频繁等待该锁，就需要进一步分析该事务的操作逻辑。
- 慢查询日志：虽然慢查询日志主要用于记录执行时间较长的 SQL 语句，但在排查锁等待超时问题时也有一定的帮助。开启慢查询日志后，它会记录所有执行时间超过设定阈值（通过
long_query_time参数设置，默认值为 10 秒）的 SQL 语句。如果发现某个事务中的 SQL 语句执行时间过长，可能是因为等待锁导致的，进一步分析这些慢查询语句有助于定位锁等待的根源。 可以通过以下方式开启慢查询日志： 在 MySQL 配置文件中添加或修改：
[mysqld]
slow_query_log = 1
long_query_time = 2
重启 MySQL 服务后，慢查询日志就会记录执行时间超过 2 秒的 SQL 语句。通过分析慢查询日志文件，可以找出那些可能因为锁等待而执行缓慢的事务。
锁等待超时的处理策略
- 优化事务设计：
- 减少事务执行时间：尽量将长事务拆分成多个短事务。例如，在前面处理订单的事务中，可以将记录日志的操作放到一个单独的事务中执行，这样主事务可以更快地提交，释放锁资源。
-- 主事务
START TRANSACTION;
UPDATE orders SET status = 'completed' WHERE order_id = 123;
UPDATE products SET stock = stock - 1 WHERE product_id = (SELECT product_id FROM order_items WHERE order_id = 123);
COMMIT;
-- 日志记录事务
START TRANSACTION;
INSERT INTO order_logs (order_id, operation, timestamp) VALUES (123, 'order completed', NOW());
COMMIT;
- **合理安排事务操作顺序**：在多个事务需要访问相同的数据时，确保它们按照相同的顺序获取锁，这样可以避免死锁的发生。例如，在涉及多个账户转账的操作中，所有事务都按照账户 ID 从小到大的顺序获取锁：
-- 事务 A
START TRANSACTION;
SELECT * FROM accounts WHERE account_id = 1 FOR UPDATE;
SELECT * FROM accounts WHERE account_id = 2 FOR UPDATE;
UPDATE accounts SET balance = balance - 100 WHERE account_id = 1;
UPDATE accounts SET balance = balance + 100 WHERE account_id = 2;
COMMIT;
-- 事务 B
START TRANSACTION;
SELECT * FROM accounts WHERE account_id = 1 FOR UPDATE;
SELECT * FROM accounts WHERE account_id = 3 FOR UPDATE;
UPDATE accounts SET balance = balance - 200 WHERE account_id = 1;
UPDATE accounts SET balance = balance + 200 WHERE account_id = 3;
COMMIT;
- 调整锁策略：
- 选择合适的锁粒度：根据业务场景，尽量使用粒度较小的锁。对于大多数 OLTP（在线事务处理）应用，行级锁通常是更好的选择。例如，在电商系统的库存更新场景中，使用行级锁可以只锁定需要更新的商品库存行，而不是整个库存表。
START TRANSACTION;
SELECT stock FROM products WHERE product_id = 456 FOR UPDATE;
UPDATE products SET stock = stock - 1 WHERE product_id = 456 AND stock > 0;
COMMIT;
- **优化索引使用**：确保在需要加锁的操作上使用了合适的索引。索引可以缩小锁的范围，提高并发性能。如果表 `users` 经常根据 `user_id` 进行更新操作，并且没有为 `user_id` 字段创建索引，可能会导致锁的范围扩大到整个表。
-- 创建索引
CREATE INDEX idx_user_id ON users (user_id);
- 处理死锁：虽然 MySQL 有自动的死锁检测和回滚机制，但在某些情况下，我们可以通过调整事务隔离级别来减少死锁的发生。例如，将事务隔离级别从
REPEATABLE READ降低到READ COMMITTED，在READ COMMITTED隔离级别下，一个事务只能看到已经提交的更改，这可能会减少锁的争用和死锁的可能性。
-- 设置事务隔离级别
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
不过，降低事务隔离级别可能会带来其他数据一致性问题，需要根据具体业务需求进行权衡。另外，在应用层捕获死锁异常并进行适当的重试也是一种常见的处理方式。例如，在 Java 应用中，可以使用 try - catch 块捕获死锁异常，然后重新执行事务：
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
public class DeadlockRetryExample {
private static final String URL = "jdbc:mysql://localhost:3306/test";
private static final String USER = "root";
private static final String PASSWORD = "password";
public static void main(String[] args) {
int retryCount = 0;
while (true) {
try {
Connection conn = DriverManager.getConnection(URL, USER, PASSWORD);
conn.setAutoCommit(false);
PreparedStatement pstmt1 = conn.prepareStatement("SELECT * FROM accounts WHERE account_id = 1 FOR UPDATE");
pstmt1.executeQuery();
PreparedStatement pstmt2 = conn.prepareStatement("SELECT * FROM accounts WHERE account_id = 2 FOR UPDATE");
pstmt2.executeQuery();
// 执行事务操作
PreparedStatement updateStmt1 = conn.prepareStatement("UPDATE accounts SET balance = balance - 100 WHERE account_id = 1");
updateStmt1.executeUpdate();
PreparedStatement updateStmt2 = conn.prepareStatement("UPDATE accounts SET balance = balance + 100 WHERE account_id = 2");
updateStmt2.executeUpdate();
conn.commit();
conn.close();
break;
} catch (SQLException e) {
if (e.getSQLState().equals("40001")) { // 死锁错误码
retryCount++;
if (retryCount > 3) {
System.err.println("多次重试后仍发生死锁，放弃操作");
break;
}
System.out.println("发生死锁，重试第 " + retryCount + " 次");
} else {
e.printStackTrace();
break;
}
}
}
}
}
- 调整参数：根据系统的实际负载和业务需求，合理调整
innodb_lock_wait_timeout参数的值。如果系统中的事务通常执行时间较长，并且锁争用情况不是特别严重，可以适当增大这个值，避免不必要的锁等待超时错误。但如果锁争用非常激烈，增大这个值可能会导致更多的事务长时间等待，降低系统的整体性能，此时需要结合其他优化措施来解决问题。 例如，在一个数据分析系统中，虽然事务执行时间较长，但数据一致性要求较高，锁争用相对较少，可以将innodb_lock_wait_timeout设置为 300 秒：
SET GLOBAL innodb_lock_wait_timeout = 300;
同时，密切关注 innodb_deadlock_detect 参数，确保死锁检测机制在高并发环境下能够有效地工作，避免死锁情况的积累导致锁等待超时。如果死锁检测机制带来的性能开销过大，可以考虑在业务逻辑层面进行更严格的锁顺序控制，以减少死锁的发生概率，在这种情况下，可以尝试适当降低死锁检测的频率或调整检测算法。
案例分析
- 案例一：长事务导致锁等待超时
- 场景描述：在一个银行转账系统中，有一个事务负责处理复杂的转账逻辑，包括更新账户余额、记录转账日志以及更新一些相关的统计信息。这个事务执行时间较长，同时其他事务频繁地进行账户查询操作。
- 问题分析：通过
SHOW ENGINE INNODB STATUS命令，发现有大量事务等待对账户表的锁。进一步查看慢查询日志，发现转账事务中的一些操作执行时间很长，特别是记录转账日志的操作，由于涉及到文件 I/O 操作，导致整个事务执行缓慢，长时间持有锁，从而使其他查询事务等待超时。 - 解决方案：将记录转账日志的操作放到一个单独的事务中执行。这样，主转账事务可以更快地提交，释放对账户表的锁。同时，优化日志记录的 I/O 操作，例如使用批量插入的方式减少 I/O 次数。
-- 主转账事务
START TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE account_id = 1;
UPDATE accounts SET balance = balance + 100 WHERE account_id = 2;
COMMIT;
-- 日志记录事务
START TRANSACTION;
INSERT INTO transfer_logs (from_account_id, to_account_id, amount, timestamp) VALUES (1, 2, 100, NOW());
COMMIT;
- 案例二：高并发竞争导致锁等待超时
- 场景描述：在一个在线商城的促销活动中，大量用户同时抢购限量商品。每个用户的购买操作都是一个事务，这些事务都需要更新商品库存表。由于并发量非常大，频繁出现锁等待超时错误。
- 问题分析：通过
performance_schema.data_locks表的分析，发现对商品库存表的锁争用非常激烈，大量事务等待获取库存行的排他锁。这是因为高并发情况下，每个事务都试图在短时间内更新库存，导致锁的竞争加剧。 - 解决方案：首先，对库存表的更新操作进行优化，使用行级锁代替表级锁，确保每次只锁定需要更新的库存行。其次，引入乐观锁机制，在更新库存前先读取库存版本号，更新时验证版本号是否一致，如果不一致则重新读取并尝试更新。这样可以减少锁的争用，提高并发性能。
-- 读取库存及版本号
SELECT stock, version FROM products WHERE product_id = 456;
-- 假设读取到的 stock 为 10，version 为 5
-- 更新库存，同时验证版本号
UPDATE products SET stock = stock - 1, version = version + 1 WHERE product_id = 456 AND version = 5;
- 案例三：死锁导致锁等待超时
- 场景描述：在一个多线程的应用程序中，两个线程分别执行不同的事务，这两个事务相互依赖对方持有的锁，形成死锁，导致锁等待超时错误频繁出现。
- 问题分析：通过
SHOW ENGINE INNODB STATUS命令中的LATEST DETECTED DEADLOCK部分，明确了死锁的发生情况。发现两个事务在获取锁的顺序上不一致，导致了死锁。 - 解决方案：在应用层调整事务获取锁的顺序，确保所有事务按照相同的顺序获取锁。例如，在 Java 代码中，对涉及到的资源进行编号，所有事务按照资源编号从小到大的顺序获取锁。
// 定义资源编号
final int RESOURCE_1 = 1;
final int RESOURCE_2 = 2;
// 线程 1
Thread thread1 = new Thread(() -> {
synchronized (Integer.valueOf(RESOURCE_1)) {
synchronized (Integer.valueOf(RESOURCE_2)) {
// 执行事务操作
}
}
});
// 线程 2
Thread thread2 = new Thread(() -> {
synchronized (Integer.valueOf(RESOURCE_1)) {
synchronized (Integer.valueOf(RESOURCE_2)) {
// 执行事务操作
}
}
});
通过以上案例分析可以看出，针对不同原因导致的锁等待超时问题，需要采用不同的处理策略，综合运用事务优化、锁策略调整、死锁处理以及参数调整等方法，才能有效地解决问题，提高 MySQL 数据库在高并发环境下的性能和稳定性。
在实际的 MySQL 数据库开发和运维过程中，深入理解锁等待超时的原理、原因以及处理方法是非常重要的。通过合理的设计和优化，可以避免锁等待超时错误对系统造成的不良影响，确保数据库系统能够高效、稳定地运行。无论是小型应用还是大型企业级系统，都需要根据自身的业务特点和并发需求，灵活运用上述技术手段，打造一个健壮的数据库环境。同时，持续监控和分析数据库的运行状态，及时发现并解决潜在的锁等待超时问题，也是保障系统性能的关键环节。通过不断地实践和总结经验，数据库管理员和开发人员可以更好地应对各种复杂的并发场景，为应用程序提供可靠的数据支持。