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

在 PostgreSQL 中，如果你希望在执行 UPDATE / DELETE 时，防止其他事务对这些行进行 INSERT/UPDATE/DELETE 的影响（确保一致、隔离、避免并发冲突），核心就是正确使用锁与事务隔离级别。

一、使用行级锁（Row-Level Locks）

1）SELECT … FOR UPDATE

锁住选中的行，使其他事务无法更新/删除这些行
防止别人 UPDATE / DELETE
但允许别人 SELECT

BEGIN;

SELECT * FROM table_name
WHERE id = 100
FOR UPDATE;

UPDATE table_name SET ... WHERE id = 100;

COMMIT;

作用

其它事务对同一行执行 UPDATE / DELETE 会等待当前事务提交
防止并发改写，保证你操作这一行的独占性

2）SELECT … FOR NO KEY UPDATE

几乎等价于 FOR UPDATE，但允许别人在唯一键不变的前提下 UPDATE
推荐用于普通 UPDATE

用于读共享场景，允许你读取但阻止其他事务删除行。

二、在 UPDATE / DELETE 语句中直接加锁

不一定要单独 SELECT，直接写：

UPDATE … RETURNING（自动加行锁）

PostgreSQL 的 UPDATE 会自动对更新行加上 RowExclusiveLock，但若你希望提前锁行，可加 CTE：

WITH lock_rows AS (
    SELECT * FROM users
    WHERE id = 100
    FOR UPDATE
)
UPDATE users
SET name = 'hello'
WHERE id IN (SELECT id FROM lock_rows);

好处：

明确锁定目标行，避免更新时出现竞争或意外阻塞

三、使用更严格的隔离级别（Serializable）

如果你想保证你的事务期间，数据不被别人改变，可使用：

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

效果：

防止脏读、不可重复读、幻读
若检测到并发冲突，会报错 could not serialize access due to concurrent update

适合防止并发写入时引发逻辑错误

四、使用表级锁（不推荐但偶尔需要）

仅当你需要 “整个表禁止写入” 时使用：

LOCK TABLE … IN SHARE MODE

允许查询，不允许更新/删除

LOCK TABLE … IN ACCESS EXCLUSIVE MODE

最强锁，阻止所有其他读/写

不推荐在高并发系统使用。

⭐ 实战场景总结（你可以直接用）

场景 1：更新一行并防止别人修改这行

BEGIN;

SELECT id FROM orders
WHERE id = 123
FOR UPDATE;

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

COMMIT;

场景 2：逻辑删除，期间禁止别人插入冲突数据（如唯一键检查）

BEGIN;

SELECT * FROM users
WHERE email = 'a@b.com'
FOR UPDATE;

UPDATE users SET deleted=true
WHERE email = 'a@b.com';

COMMIT;

场景 3：批量 UPDATE，需要先锁定所有受影响的行

BEGIN;

SELECT id FROM products
WHERE price < 10
FOR UPDATE;

UPDATE products SET price = price + 1
WHERE price < 10;

COMMIT;

场景 4：防止并发 Update 引发逻辑错误

使用 SERIALIZABLE：

BEGIN;

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

UPDATE accounts
SET balance = balance - 10
WHERE id = 5;

COMMIT;

如冲突会自动回滚，需要重新执行。

🎯 最佳实践总结（简明）

问题	推荐解决方案
防止别人 UPDATE / DELETE	SELECT … FOR UPDATE
防止别人删除	FOR SHARE / FOR KEY SHARE
确保更新期间数据不变	FOR UPDATE 或 SERIALIZABLE
保证批量 UPDATE 的一致性	先 SELECT FOR UPDATE 锁行，再 UPDATE
全表禁止写入	LOCK TABLE IN SHARE MODE