如何防范MySQL定时任务批量更新引发的大面积死锁?

阿墨吖_9461

阿墨吖_9461

2026-11-19

482人浏览

原创

批量更新定时任务死锁主因是加锁顺序不可控,IN子句不保序,InnoDB按聚簇索引物理顺序加锁;必须用ORDER BY id强制升序扫描保序,且需配合有序ID分批与事务外处理非DB操作。

如何防范mysql定时任务批量更新引发的大面积死锁?

批量更新定时任务是死锁高发区,根本原因不是“量大”,而是加锁顺序不可控——IN子句本身不保序,InnoDB内部加锁顺序取决于索引扫描路径,不同批次、不同执行计划下极易错位。

为什么UPDATE ... WHERE id IN (100, 5, 88)会触发死锁

IN 列表顺序 ≠ InnoDB 加锁顺序。InnoDB 按主键聚簇索引的物理存储顺序加锁,而物理顺序与主键值大小无关(尤其在有大量删除/插入后)。事务 A 批次按 (100, 5, 88) 提交,实际加锁顺序可能是 5 → 88 → 100;事务 B 同样传 (100, 5, 88),却因页分裂或缓存状态差异,加锁顺序变成 88 → 5 → 100,只要任意两行顺序相反,就构成循环等待基础。

  • EXPLAIN 显示 key 为 PRIMARY,但 rows 值波动大 → 表明扫描范围不稳定,锁粒度飘移
  • 复合索引未覆盖全部 WHERE 字段时,优化器可能放弃索引,退化为全表扫描+行锁升级
  • 同一 SQL 在不同时间点执行计划突变(如统计信息过期),导致某次走索引、下次全表,顺序彻底失控

必须用ORDER BY id强制物理加锁顺序

仅靠应用层排序 ID 列表不够,InnoDB 不保证按输入顺序加锁。真正起效的是在 UPDATE 语句中显式加上 ORDER BY id,让优化器明确按主键升序扫描——这是 InnoDB 官方文档确认的加锁保序手段。

  • 正确写法:UPDATE orders SET status = 'processed' WHERE id IN (100, 5, 88) ORDER BY id
  • 错误写法:UPDATE orders SET status = 'processed' WHERE id IN (100, 5, 88)(无 ORDER BY)
  • 若用复合条件(如 WHERE user_id = ? AND status = 'pending'),必须建联合索引 (user_id, status, id),并确保 ORDER BY id 能被索引覆盖,否则排序失效
  • 注意:MySQL 8.0+ 支持 UPDATE ... ORDER BY,但 MySQL 5.7 及更早版本不支持,需降级为分页 + 单条更新

拆批逻辑必须绑定主键升序,不能只靠 LIMIT

UPDATE ... LIMIT 100 是危险操作:InnoDB 仍会扫描满足 WHERE 条件的所有行,锁住 LIMIT 之后的匹配行(尤其当索引选择率差时)。真正的安全拆批,是先查出有序 ID,再分段构造 IN 子句。

win可用的mysql指令skill
win可用的mysql指令skill

安装、修复、更新或打包一个基于 Python 和 PyMySQL 实现的 Windows `mysql` 命令。适用于用户希望在 PowerShell/CMD 中输入 `mysql` 时重定向到 Python 实现、需要交互式输入 MySQL 连接信息、需要同步到当前用户本地 bin 目录,或需要将该能力封装成可公开复用的 Codex skill。

下载
  • 第一步:SELECT id FROM orders WHERE status = 'pending' ORDER BY id LIMIT 100(确保走索引)
  • 第二步:拿到结果集后,按 id 升序排列(通常已有序,但需校验)
  • 第三步:拼接 UPDATE ... WHERE id IN (?, ?, ...) ORDER BY id,每批严格 ≤ 100 行
  • 避免用 OFFSET 分页(深度分页性能差且易漏数据),改用游标式分页:WHERE id > ? AND status = 'pending' ORDER BY id LIMIT 100

定时任务里禁止任何外部依赖阻塞事务

一个定时任务常含日志、消息通知、HTTP 回调等操作。若这些放在事务内,会导致行锁持有时间从毫秒级拉长到秒级,极大抬高死锁窗口——这不是死锁直接原因,却是压垮系统的最后一根稻草。

  • 所有非数据库操作(log.info()、kafkaTemplate.send()、restTemplate.postForObject())必须移到 @Transactional 方法之外
  • 若需依赖查询结果做后续判断,先在事务内查好数据并返回,再在事务外处理
  • 对失败重试场景,不要在事务内 sleep 或循环,应抛出异常由调度框架控制重试节奏
  • 监控项重点看 Innodb_row_lock_time_avg 和 Threads_running,若平均锁等待时间 > 10ms 或运行线程持续 > 50,说明锁已被拖慢

最易被忽略的一点:二级索引变更也会隐式加锁。比如你只更新 status 字段,但它上有单独索引,InnoDB 就要同时锁聚簇索引和该二级索引的对应项——这意味着看似单字段更新,实际锁了两处资源,交叉概率翻倍。检查 SHOW INDEX FROM table_name,删掉业务中从不用于查询的冗余索引,比加更多重试逻辑更治本。

相关文章

PHP速学视频免费教程(入门到精通)
PHP速学视频免费教程(入门到精通)

PHP怎么学习?PHP怎么入门?PHP在哪学?PHP怎么学才快?不用担心,这里为大家提供了PHP速学教程(入门到精通),有需要的小伙伴保存下载就能学习啦!

下载

相关标签:

mysql

本站声明:本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系admin@php.cn

相关专题

更多
mysql修改数据表名
mysql修改数据表名

MySQL修改数据表:1、首先查看数据库中所有的表,代码为:‘SHOW TABLES;’;2、修改表名,代码为:‘ALTER TABLE 旧表名 RENAME [TO] 新表名;’。php中文网还提供MySQL的相关下载、相关课程等内容,供大家免费下载使用。

2023.06.20

2673

6

MySQL创建存储过程
MySQL创建存储过程

存储程序可以分为存储过程和函数,MySQL中创建存储过程和函数使用的语句分别为CREATE PROCEDURE和CREATE FUNCTION。使用CALL语句调用存储过程智能用输出变量返回值。函数可以从语句外调用(通过引用函数名),也能返回标量值。存储过程也可以调用其他存储过程。php中文网还提供MySQL创建存储过程的相关下载、相关课程等内容,供大家免费下载使用。

2023.06.21

1599

5

mongodb和mysql的区别
mongodb和mysql的区别

mongodb和mysql的区别:1、数据模型;2、查询语言;3、扩展性和性能;4、可靠性。本专题为大家提供mongodb和mysql的区别的相关的文章、下载、课程内容,供大家免费下载体验。

2023.07.18

935

5

mysql密码忘了怎么查看
mysql密码忘了怎么查看

MySQL是一个关系型数据库管理系统,由瑞典MySQL AB 公司开发,属于 Oracle 旗下产品。MySQL 是最流行的关系型数据库管理系统之一,在 WEB 应用方面,MySQL是最好的 RDBMS 应用软件之一。那么mysql密码忘了怎么办呢?php中文网给大家带来了相关的教程以及文章,欢迎大家前来阅读学习。

2023.07.19

3852

5

mysql创建数据库
mysql创建数据库

MySQL是一个关系型数据库管理系统,由瑞典MySQL AB 公司开发,属于 Oracle 旗下产品。MySQL 是最流行的关系型数据库管理系统之一,在 WEB 应用方面,MySQL是最好的 RDBMS 应用软件之一。那么mysql怎么创建数据库呢?php中文网给大家带来了相关的教程以及文章,欢迎大家前来阅读学习。

2023.07.25

6728

4

mysql默认事务隔离级别
mysql默认事务隔离级别

MySQL是一种广泛使用的关系型数据库管理系统,它支持事务处理。事务是一组数据库操作,它们作为一个逻辑单元被一起执行。为了保证事务的一致性和隔离性,MySQL提供了不同的事务隔离级别。php中文网给大家带来了相关的教程以及文章欢迎大家前来学习阅读。

2023.08.08

1399

3

sqlserver和mysql区别
sqlserver和mysql区别

SQL Server和MySQL是两种广泛使用的关系型数据库管理系统。它们具有相似的功能和用途,但在某些方面存在一些显著的区别。php中文网给大家带来了相关的教程以及文章,欢迎大家前来学习阅读。

2023.08.11

7011

4

mysql忘记密码
mysql忘记密码

MySQL是一种关系型数据库管理系统,关系数据库将数据保存在不同的表中,而不是将所有数据放在一个大仓库内,这样就增加了速度并提高了灵活性。那么忘记mysql密码我们该怎么解决呢?php中文网给大家带来了相关的教程以及其他关于mysql的文章,欢迎大家前来学习阅读。

2023.08.14

5962

7

mysql事务隔离级别
mysql事务隔离级别

mysql规范中定义了四种事务隔离级别,不同的隔离级别对事务的处理有所不同。本专题为大家提供mysql事务隔离级别相关的文章内容,大家可以免费体验。

2023.08.16

8134

11

热门下载

更多
网站特效
/
网站源码
/
网站素材
/
前端模板

精品课程

更多
相关推荐
/
热门推荐
/
最新课程