|
这是一个典型的 MySQL 锁等待超时 错误。下面为您详细解释原因和解决方案。
核心原因
简单来说：有一个事务正在长时间锁定这条SQL要操作的数据行，导致当前事务一直等待锁释放，最终超过了MySQL的最大等待时间（innodb_lock_wait_timeout，默认50秒），从而失败回滚。
详细原因分析
- 锁竞争：SQL语句是一个复杂的操作。
- 阻塞事务：在这个事务开始之前，很可能已经存在另一个未提交的事务（比如一个长时间的查询、更新或插入操作），这个“前辈”事务已经锁定了您SQL语句中想要更新的部分或全部数据行。
- 等待与超时：这个事务（报错的事务）因为拿不到锁，只能进入等待队列。在MySQL默认的50秒内，如果那个“阻塞事务”一直没有提交或回滚，您的这个事务就会因超时而失败，抛出
CannotAcquireLockException。
解决方案
1. 立即处理（治标）
2. 长期优化（治本）
- 优化事务设计：
- 缩小事务范围：确保事务尽可能短小精悍。不要在事务中包含不必要的业务逻辑、远程调用或文件操作。执行完数据库操作后立即提交。
- 避免长事务：这是最根本的原因。检查代码中是否有事务方法执行时间过长。
- 优化SQL和索引：
- 您的SQL使用了
IN (?) 和多个 JOIN。确保 tcqp.id、连接条件上的字段（如 tcqpd.contract_process_id, tcaf.contract_process_id）都有合适的索引。没有索引会导致锁升级（例如锁表而不是锁行），大大增加锁冲突的概率。 - 使用
EXPLAIN 分析您的SQL，看是否使用了正确的索引。
- 业务逻辑分离：
- 考虑将这个大而复杂的更新操作拆分成多个较小、较快的事务。例如，可以先更新主表，再更新子表。
- 调整数据库参数（需DBA评估）：
- innodb_lock_wait_timeout：可以适当调大这个参数（例如调到120秒），但这只是延长了等待时间，并未解决根本问题。
- innodb_rollback_on_timeout：默认是OFF，超时后只回滚最后一条语句。可以设置为ON，让整个事务回滚，但这可能会影响业务逻辑。
- 使用不同的隔离级别：
- 默认的
REPEATABLE READ 隔离级别更容易产生锁。如果业务允许，可以考虑在特定查询中使用 READ COMMITTED 级别，但这需要仔细评估一致性要求。
总结
这个错误的根本原因是 数据库中存在“长事务”阻塞了您的正常操作。
建议处理流程：
- 紧急处理：通过
information_schema 找到并 KILL 阻塞进程。 - 分析原因：审查代码，找到是哪个业务操作导致了长事务。
- 根本解决：优化事务代码和SQL，确保所有数据库操作快速完成并及时提交。
SELECT * FROM information_schema.INNODB_TRX; 这个sql执行完的返回的数据怎么看？
执行 SELECT * FROM information_schema.INNODB_TRX; 后，您会看到当前所有InnoDB事务的详细信息。以下是关键字段的解释和如何分析：
关键字段解释
| 字段 | 说明 | 重点关注 |
|---|
trx_id | InnoDB内部事务ID | 用于识别特定事务 | trx_state | 事务状态 | LOCK WAIT(锁等待中), RUNNING(运行中), ROLLING BACK(回滚中) | trx_started | 事务开始时间 | 判断事务运行了多久 | trx_requested_lock_id | 正在等待的锁ID | 仅在trx_state='LOCK WAIT'时有值 | trx_wait_started | 开始等待的时间 | 判断等待了多久 | trx_weight | 事务权重 | 值越大越可能被回滚 | trx_mysql_thread_id | MySQL连接线程ID | 用于KILL命令 | trx_query | 当前正在执行的SQL | 查看事务在做什么 | trx_operation_state | 当前操作状态 | | trx_tables_in_use | 涉及的表数量 | | trx_tables_locked | 被锁定的表数量 | | trx_lock_structs | 锁结构数量 | | trx_lock_memory_bytes | 锁内存占用 | | trx_rows_locked | 被锁定的行数 | 值过大可能是问题 | trx_rows_modified | 修改的行数 | 值过大可能是长事务 |
如何分析结果
1. 识别问题事务
-- 按事务开始时间排序，查看运行时间最长的事务
SELECT
trx_id,
trx_state,
trx_started,
TIMEDIFF(NOW(), trx_started) as running_time,
trx_mysql_thread_id,
trx_query,
trx_rows_locked,
trx_rows_modified
FROM information_schema.INNODB_TRX
ORDER BY trx_started ASC;
2. 重点关注的情况
锁等待事务(trx_state = 'LOCK WAIT')
-- 查找正在等待锁的事务
SELECT * FROM information_schema.INNODB_TRX
WHERE trx_state = 'LOCK WAIT';
- 这类事务就是您的报错事务
- 查看
trx_query 了解它在等待什么 - 记录
trx_mysql_thread_id 以备需要时终止
长时间运行事务(RUNNING状态但运行时间很长)
-- 查找运行超过30秒的事务
SELECT * FROM information_schema.INNODB_TRX
WHERE trx_state = 'RUNNING'
AND trx_started < NOW() - INTERVAL 30 SECOND;
3. 实际分析示例
假设查询结果如下：
| trx_id | trx_state | trx_started | trx_mysql_thread_id | trx_query | trx_rows_locked |
|---|
| 12345 | RUNNING | 2024-01-01 10:00:00 | 100 | UPDATE table SET ... | 5000 | | 67890 | LOCK WAIT | 2024-01-01 10:01:30 | 101 | UPDATE tb_contract_quantities_process ... | 0 |
分析结论：
- 线程ID 100 的事务从 10:00 开始运行，已经锁定了5000行，很可能是阻塞事务
- 线程ID 101 的事务在等待锁，这就是您的报错事务
4. 进一步定位阻塞关系
-- 更精确地查找谁阻塞了谁
SELECT
r.trx_id AS waiting_trx_id,
r.trx_mysql_thread_id AS waiting_thread,
r.trx_query AS waiting_query,
b.trx_id AS blocking_trx_id,
b.trx_mysql_thread_id AS blocking_thread,
b.trx_query AS blocking_query
FROM information_schema.INNODB_LOCK_WAITS w
INNER JOIN information_schema.INNODB_TRX b ON b.trx_id = w.blocking_trx_id
INNER JOIN information_schema.INNODB_TRX r ON r.trx_id = w.requesting_trx_id;
5. 处理建议
如果是测试环境：直接终止阻塞事务
KILL 100; -- 终止线程ID为100的连接
如果是生产环境：
- 联系相关业务负责人确认该长时间运行的事务是否可以终止
- 分析
trx_query 中的SQL，优化其性能 - 考虑在业务低峰期执行相关操作
通过这种方法，您就能准确识别出导致锁等待超时的根本原因了。
阻塞事务 可能产生的原因
阻塞事务的产生原因多种多样，以下是主要的分类和具体原因：
1. 事务设计问题
长事务
-- 典型的长事务模式
BEGIN;
-- 执行复杂的业务逻辑
UPDATE large_table SET ... WHERE ...; -- 耗时操作
-- 中间可能包含业务逻辑、外部API调用等
COMMIT; -- 很久之后才提交
特征：事务开始和提交时间间隔很长
未提交的事务
// 代码中忘记提交或回滚
@Transactional
public void processData() {
// 执行更新操作
updateTableA(...);
// 如果这里发生异常，事务可能一直挂起
if (someCondition) {
return; // 忘记提交或回滚
}
// ... 其他操作
}
2. SQL性能问题
缺乏合适的索引
-- 没有索引的更新操作
UPDATE tb_contract_quantities_process
SET del_flag = 1
WHERE contract_name LIKE '%某合同%'; -- 全表扫描，锁住大量行
-- 有索引的高效更新
UPDATE tb_contract_quantities_process
SET del_flag = 1
WHERE id IN (1, 2, 3); -- 使用主键索引，只锁特定行
全表扫描操作
-- 导致锁表的操作
UPDATE table_a SET status = 1 WHERE unindexed_column = 'value';
DELETE FROM large_table WHERE create_time < '2023-01-01';
3. 锁机制相关
锁升级
- 行锁升级为表锁：当一条SQL需要锁定大量数据行时，InnoDB可能将锁升级为表锁
- 间隙锁（Gap Lock）：在REPEATABLE READ隔离级别下，范围查询会锁定不存在的记录区间
死锁循环
-- 事务A
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
-- 事务B (同时执行)
UPDATE accounts SET balance = balance - 50 WHERE id = 2;
UPDATE accounts SET balance = balance + 50 WHERE id = 1;
4. 应用架构问题
同步批量操作
// 在事务中处理大量数据
@Transactional
public void batchProcessContracts(List<Long> contractIds) {
for (Long id : contractIds) { // 循环处理，事务时间很长
updateContractStatus(id);
insertProcessLog(id);
// ... 其他操作
}
}
嵌套事务问题
@Transactional
public void mainProcess() {
// 主事务开始
updateMainTable();
// 调用另一个事务方法
subProcess(); // 如果subProcess有@Transactional(propagation=REQUIRES_NEW)
// 主事务继续...
}
5. 业务逻辑缺陷
用户交互式事务
@Transactional
public void approveContract(Long contractId) {
// 开始事务
updateContractStatus(contractId, "APPROVING");
// 等待用户确认（事务一直打开！）
waitForUserConfirmation(); // 错误做法！
updateContractStatus(contractId, "APPROVED");
}
循环依赖更新
-- 事务A
UPDATE table_a SET status = (SELECT count FROM table_b WHERE ...);
-- 事务B
UPDATE table_b SET count = (SELECT status FROM table_a WHERE ...);
6. 数据库配置问题
不合理的隔离级别
- READ UNCOMMITTED：脏读风险
- REPEATABLE READ：容易产生间隙锁（MySQL默认）
- SERIALIZABLE：严格的锁机制，性能差
连接池配置不当
# 连接池配置问题
spring.datasource.hikari.maximum-pool-size=200 # 连接数过多，竞争激烈
spring.datasource.hikari.connection-timeout=30000 # 连接超时时间过长
7. 监控和诊断方法
识别阻塞事务的SQL
-- 查看当前执行的所有SQL
SHOW PROCESSLIST;
-- 查看详细的锁信息
SELECT
r.trx_id waiting_trx_id,
r.trx_query waiting_query,
b.trx_id blocking_trx_id,
b.trx_query blocking_query,
b.trx_started blocking_started,
TIMEDIFF(NOW(), b.trx_started) blocking_duration
FROM information_schema.INNODB_LOCK_WAITS w
INNER JOIN information_schema.INNODB_TRX b ON b.trx_id = w.blocking_trx_id
INNER JOIN information_schema.INNODB_TRX r ON r.trx_id = w.requesting_trx_id;
8. 预防和解决方案
优化事务设计
// 正确的做法：短事务
public void processInShortTransactions(List<Long> ids) {
for (Long id : ids) {
transactionTemplate.execute(status -> {
// 每个ID在独立事务中处理
updateContract(id);
insertLog(id);
return null;
});
}
}
添加合适的索引
-- 为查询和更新条件添加索引
CREATE INDEX idx_contract_process ON tb_contract_quantities_process(id);
CREATE INDEX idx_process_details ON tb_contract_quantities_process_details(contract_process_id);
CREATE INDEX idx_arrival_file ON tb_contract_arrival_file(contract_process_id);
使用乐观锁
// 使用版本号避免悲观锁
@Transactional
public boolean updateWithOptimisticLock(Long id, String newValue) {
Entity entity = entityDao.findById(id);
int version = entity.getVersion();
int affected = entityDao.updateWithVersion(id, newValue, version, version + 1);
return affected > 0; // 如果失败可以重试
}
通过分析这些可能的原因，您可以系统地排查和解决阻塞事务问题。
总结 |
發表於 2025-12-22 11:47:52
