使用子查询与临时表执行DELETE的锁时长对比分析

结论很明确：换成子查询的DELETE会显著增加行/表的锁定时间，直接影响其他查询的吞吐量，原因如下：

两种方案的锁行为差异 #

1. 临时表原方案 #

• 第一步插入临时表：这是对MyTable的只读查询，只会加共享锁（Share Lock），这种锁完全兼容其他读操作，也不会阻塞大部分写操作（除非是同一行的写），而且这10秒的查询阶段不会持有任何会影响后续操作的锁，查询完成后共享锁就释放了。
• 第二步DELETE：仅在这1秒内，对需要删除的行加排他锁（Exclusive Lock），锁持有时间只有1秒，操作完成后立即释放。

2. 子查询DELETE方案 #

这个版本中，子查询和DELETE属于同一个事务，锁的持有逻辑完全不同：

• 不管是默认的READ COMMITTED还是REPEATABLE READ隔离级别，整个操作的11秒（子查询10秒+删除1秒）内，目标行的排他锁会从子查询扫描阶段开始持有，直到整个事务结束。
• 也就是说，原本只需要锁1秒的行，现在要被锁11秒，期间其他需要修改这些行的查询都会被阻塞。如果子查询扫描范围很大，还会持有表级的意向排他锁（IX Lock），虽然不阻塞读，但会阻塞需要表级锁的操作（比如ALTER TABLE）。

优化建议 #

• 继续使用临时表方案，建议给临时表的Key1字段加索引：

  CREATE INDEX idx_tmp_key1 ON tmp(Key1);
  

  这样能让DELETE阶段的JOIN操作更快，进一步缩短锁持有时间。
• 如果要删除的数据量很大，可以把DELETE拆成分批操作，比如每次删除1000行，把单次1秒的锁时间拆成多个更小的时间段，进一步降低对其他业务的影响。

内容的提问来源于stack exchange，提问作者Liero