在SQL中如何通过分批循环DELETE有效降低数据库锁等待？-SQL-PHP中文网

直接DELETE大表会卡住其他查询，因其一次性加锁导致事务长、锁持有久、并发阻塞；应改用LIMIT分批删除并显式COMMIT，或按主键范围切片以避免漏删重复删。

在sql中如何通过分批循环delete有效降低数据库锁等待？

为什么直接 DELETE 大表会卡住其他查询？

因为 DELETE FROM table WHERE ... 会尝试一次性获取满足条件的所有行的行锁（甚至升级为页锁或表锁），事务持续时间越长，锁持有时间越久，其他会话在访问相同数据页时就会被阻塞。尤其在高并发 OLTP 场景下，Waiting for table metadata lock 或 Lock wait timeout exceeded 错误频发，本质是锁粒度和事务时长失控。

用 LIMIT + WHILE 循环分批删除（MySQL / PostgreSQL 兼容写法）

核心思路：每次只删固定行数（如 1000 行），提交事务，释放锁，再删下一批。避免单事务过长、日志暴涨、锁堆积。

MySQL 示例（存储过程）：

DELIMITER $$
CREATE PROCEDURE batch_delete_expired()
BEGIN
  DECLARE done INT DEFAULT FALSE;
  DECLARE affected_rows INT DEFAULT 1;
<p>WHILE affected_rows > 0 DO
DELETE FROM orders 
WHERE status = 'cancelled' AND created_at </p><pre class="brush:php;toolbar:false;">SET affected_rows = ROW_COUNT();
-- 强制提交，释放锁
COMMIT;
-- 可选：休眠 100ms 减轻 I/O 压力
DO SLEEP(0.1);

END WHILE; END$$ DELIMITER ;

PostgreSQL 需改用 DELETE ... USING + CTE 或游标，但逻辑一致：控制单次影响行数 + 立即 COMMIT。

LIMIT 是关键，不加会导致全量扫描+全量加锁
必须在循环体内显式 COMMIT（即使自动提交开启，也建议显式写出）
避免在 WHERE 条件中使用未索引字段，否则每次循环都全表扫描
若删除条件涉及 JOIN 或子查询，优先重写为基于主键/索引字段的范围删除（如 id BETWEEN ? AND ?）

用主键范围分片替代 LIMIT（更稳定，适合超大表）

当表有自增 id 或时间戳主键时，按主键切片比 LIMIT 更可靠——不会因并发 INSERT 导致漏删或重复删。

DrugBank 数据库

访问并分析来自 DrugBank 数据库的全面药物信息，包括药物属性、相互作用、靶点、通路、化学结构和药理学数据。用于药物数据、药物发现研究、药理学研究、药物-

下载

示例（MySQL）：

SET @min_id = 1;
SET @max_id = (SELECT MAX(id) FROM orders WHERE status = 'cancelled' AND created_at WHILE @min_id COMMIT;
SET @min_id = @min_id + 1000;
DO SLEEP(0.05);
END WHILE;