sql - INSERT ... SELECT 是原子事务吗？

Question

我使用这样的查询:

INSERT INTO table
     SELECT * FROM table2 t2
        JOIN ...
        ...
        WHERE table2.date < now() - '1 day'::INTERVAL
     FOR UPDATE OF t2 SKIP LOCKED
ON CONFLICT (...)
    DO UPDATE SET ...
RETURNING *;

我的问题是关于FOR UPDATE t2 SKIP LOCKED。我应该在这里使用它吗？还是 Postgres 会使用 INSERT SELECT ON CONFLICT 自动锁定这些行直到事务结束？

我的目标是防止其他应用(同时)使用内部 SELECT 捕获已被该应用捕获的行。

Answer 1

是的，FOR UPDATE OF t2 SKIP LOCKED 是防止默认竞争条件的正确方法 Read Committed transaction isolation .

添加的 SKIP LOCKED 也可以防止死锁。请注意，竞争事务可能每个都从 SELECT 中获得部分集 - 无论它能先锁定什么。

虽然在 Postgres 中任何事务都是原子的，但它不会阻止另一个(也是原子的)事务选择(和插入 - 或者至少尝试)同一行，因为 SELECT without FOR UPDATE 不接受 exclusive lock .

Postgres manual about transactions :

A transaction is said to be atomic: from the point of view of other transactions, it either happens completely or not at all.

相关:

• Postgres UPDATE … LIMIT 1

---

说明:

• 像 INSERT 这样的 SQL DML 命令总是自动原子，因为它不能在事务之外运行。但是你不能说 INSERT 是一个事务。错误的术语。

• 在 Postgres 中，所有锁一直保持到当前事务结束并在结束时释放。