MySQL 在 UPDATE / DELETE 时防止其它操作

MySQL 在执行 UPDATE / DELETE 时，如何防止别的事务对同一批数据进行 INSERT / UPDATE / DELETE 的干扰。

一句话总结

要防止其它操作影响你正在 UPDATE/DELETE 的数据，核心是：用正确的 InnoDB 锁（记录锁、间隙锁、Next-Key Lock），并确保走索引。

UPDATE / DELETE 在执行时：

自动对目标行加 排他锁（X 锁） -> 防止其他事务 UPDATE/DELETE
自动加 Next-Key Lock（记录锁 + 间隙锁） -> 防止其他事务在范围内 INSERT
如果语句不能走索引 -> 会锁全表（非常危险！）

📌 MySQL 在 UPDATE / DELETE 时的实际行为（重点）

① UPDATE / DELETE 会自动加 X 锁（Record Lock）

例如：

UPDATE users SET age=30 WHERE id=10;

锁住记录 id=10：

🔒 阻止其它事务 UPDATE 这行
🔒 阻止其它事务 DELETE 这行
❌ 不阻止 SELECT（快照读）

② WHERE 是范围查询时，会加 Next-Key Lock

例如：

DELETE FROM orders WHERE amount > 100 AND amount < 500;

InnoDB 会自动加 Next-Key Lock（记录锁 + 间隙锁）：

🔒 阻止其他事务插入（INSERT）
🔒 阻止其他事务更新命中该范围的数据
🔒 防止幻读

也就是说：

你的 DELETE 正在扫描范围

其他人不能在这个范围插入
也不能更新范围内记录
也不能删除范围内记录

③ 如果 WHERE 没有走索引 -> 锁全表

例如：

UPDATE users SET status=1 WHERE name='abc';

但 name 没有索引 -> 产生：

全表扫描
全表加 Next-Key Lock！

其他事务对整张表的 INSERT/UPDATE/DELETE 都会被阻塞。

④ INSERT 会被间隙锁阻止

事务 A：

UPDATE users SET score = score + 10 WHERE id BETWEEN 100 AND 200;

这会锁住：

[100, 200] 之间的所有记录 + 所有间隙

事务 B：

INSERT INTO users(id, score) VALUES(150, 10);

会被阻塞，因为 150 在事务 A 的锁区间内。

📌 结论：MySQL 自动阻止了其它 UPDATE/DELETE/INSERT

UPDATE、DELETE 自动保护机制：

操作	是否阻止别人 INSERT	是否阻止别人 UPDATE	是否阻止别人 DELETE	原因
单点 UPDATE	❌（单点无间隙）	✅	✅	X Lock
范围 UPDATE	✅	✅	✅	Next-Key Lock
单点 DELETE	❌	✅	✅	X Lock
范围 DELETE	✅	✅	✅	Next-Key Lock

📌 如何手动加强隔离？（业务常用）

如果希望“当前事务操作的数据完全避免外界改动”，可以使用：

方案 1：显式加锁（FOR UPDATE）

先锁行，再更新

SELECT * FROM users WHERE id IN (1,2,3) FOR UPDATE;

UPDATE users SET ... WHERE id IN (1,2,3);

优点：

强制锁定目标记录
阻止其它 UPDATE/DELETE
确保不会发生并发覆盖

方案 2：使用事务包裹 UPDATE / DELETE

把所有操作变成一个原子过程：

START TRANSACTION;

UPDATE/DELETE ...;

-- 其它检查、业务逻辑
-- 计算新值

COMMIT;

方案 3：使用最精确的索引（非常重要）

如果没有索引：

UPDATE/DELETE 会锁表
会阻塞别人所有写入
性能极差

如果有索引：

精确锁定记录
减少冲突
避免锁扩大化

方案 4：范围操作一定要使用索引

例如：

❌ 这样会锁表：

DELETE FROM msg WHERE created_at < '2020-01-01';

✅ 应加索引：

ALTER TABLE msg ADD INDEX idx_created_at(created_at);

📌 如何让 UPDATE / DELETE 完全隔离并发写？

一般用这两个方案：

方案 A：加锁读 + 精确 UPDATE

START TRANSACTION;

SELECT * FROM orders 
WHERE id = 123 
FOR UPDATE;

UPDATE orders SET status = 'done' 
WHERE id = 123;

COMMIT;

这时：

其它事务不能 UPDATE 同一行
也不能 DELETE 同一行
不能 INSERT 冲突范围（若是范围查询）

方案 B：范围锁 + UPDATE

比如你希望范围内数据完全由你独占：

START TRANSACTION;

SELECT * FROM users 
WHERE score BETWEEN 100 AND 200 
FOR UPDATE;

UPDATE users
SET level = level + 1
WHERE score BETWEEN 100 AND 200;

COMMIT;

这会锁定：

所有匹配行的记录锁
所有间隙锁

-> 其它事务无法插入符合条件的记录

完全隔离，绝对安全。

📌 实战总结（最权威做法）

要防止别人对你正在 UPDATE/DELETE 的数据进行读写：

需求	使用
防止别人 UPDATE/DELETE 同一行	自动加的 X 锁
防止别人 INSERT 冲突数据	使用范围查询 -> Next-Key Lock
防止别人并发修改同一批数据	SELECT … FOR UPDATE
防止写入冲突且确保强一致	使用事务包裹