根本原因在于事务边界、索引和执行节奏未对齐：where无索引导致全表扫描与锁升级；分批操作需显式提交、避免offset分页；禁用函数索引、确保order by走索引；警惕幽灵事务与隐性锁源。
存储过程里批量操作锁表，根本不是语法写错了，而是事务边界、索引和执行节奏三者没对齐——哪怕语句完全合法，照样卡住整个表。
WHERE条件没索引，删/改多少行都等于锁全表
MySQL 和 SQL Server 在 WHERE 字段无索引时，会退化为全表扫描；InnoDB 不是“加了行锁”，而是边扫边加行锁+间隙锁，锁数量爆炸后直接升级为表锁。这不是配置问题，是引擎保护机制。
- 用
EXPLAIN SELECT *替代EXPLAIN DELETE（MySQL 不支持后者），确认type是range或ref，不是ALL - 别写
WHERE DATE(created_at) = '2024-01-01'—— 函数导致索引失效，改用created_at >= '2024-01-01' AND created_at - 别传字符串给 INT 字段，比如
WHERE user_id = '123'，隐式转换会让索引失效 - 加索引要选低峰期，MySQL 5.6+ 可用
ALGORITHM=INPLACE减少锁表时间
分批逻辑必须带 ORDER BY + LIMIT，且不能靠 OFFSET
只写 DELETE ... LIMIT 1000 极其危险：优化器可能忽略 LIMIT，或因无序导致重复删、漏删；并发执行时更易错乱。关键是要锁定扫描起点和方向。
- 正确写法是：
DELETE FROM logs WHERE id > @last_id AND status = 'pending' ORDER BY id LIMIT 2000 -
ORDER BY字段必须是索引列（最好是主键），否则触发filesort，更慢更耗资源 - 禁用
LIMIT 10000, 5000这类 offset 分页式写法——偏移越大越慢，且并发下容易跳过数据块 - 每批执行完查
ROW_COUNT()，返回 0 就停；别硬设循环次数
事务必须显式提交，且不能包着耗时操作
锁释放时机取决于事务结束，不是语句执行完。BEGIN 后套个 WHILE 处理 10 万行，最后才 COMMIT，是最高危的反模式。
- 每批
DELETE/UPDATE后必须显式COMMIT，或确保autocommit = 1 - 单次处理建议控制在 500–5000 行之间：太小事务开销占比高；太大仍可能触发锁升级或日志写满
-
SELECT ... FOR UPDATE别放太早——一上来就加锁，然后做日志、校验、调外部服务，锁挂着几十秒不动 - 把 HTTP 调用、文件读写、JSON 解析等移出事务块；日志记录、消息推送放到
COMMIT之后再做
最容易被忽略的“幽灵事务”和隐形锁源
你加了索引、分了批、限了速，还是卡——大概率不是 SQL 写得不对，而是别的进程在“偷偷锁着表”。这类问题不报错，但会让所有优化失效。
- 监控脚本每 5 秒跑一次
SELECT COUNT(*) FROM t，拿的是共享锁，而你的DELETE在等排他锁 - 应用异常退出、连接池复用未清理、调试时手动中断，都会留下
trx_state = 'RUNNING'但trx_started几分钟前的“幽灵事务” - 它不争锁，但拖慢 MVCC 清理、阻塞 DDL，还占着连接资源
- 用
SELECT * FROM information_schema.INNODB_TRX查活跃事务，重点关注trx_started时间异常久的
