INSERT ... SELECT是原子事务吗? [英] Is INSERT ... SELECT an atomic transaction?

问题描述

我使用这样的查询:

  INSERT INTO表SELECT * FROM table2 t2加入 ......在哪里table2.date<now()-'1天':: INTERVAL用于更新t2跳过锁定发生冲突时(...)做更新集...返回*; 

我的问题是关于更新t2跳过锁定.我应该在这里使用吗?还是Postgres使用 INSERT SELECT ON CONFLICT 自动锁定这些行，直到交易结束?

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

解决方案

是的，用于更新t2跳过锁定是使用默认 读取已提交 事务隔离./p>

添加的 SKIP LOCKED 也可以防止死锁.请注意，竞争交易可能会分别从 SELECT 中获得部分交易集-可以首先锁定的交易.

尽管任何事务在Postgres中都是原子的，但它不会阻止另一个(也是原子的)事务选择(并插入-或至少尝试)同一行，因为 SELECT without FOR UPDATE 不需要有关交易的Postgres手册:

一项交易被称为是 atomic :从其他交易的角度来看，它要么完全发生，要么根本不发生.

相关:

• Postgres更新…限制1

---

说明:

• 诸如 INSERT 之类的SQL DML命令始终自动为 atomic ，因为它不能在事务外部运行.但是您不能说 INSERT 是事务.术语错误.

• 在Postgres中，所有锁将保留到当前事务结束并在当前事务结束时释放.

I use a query like this:

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 *;

My question is about FOR UPDATE t2 SKIP LOCKED. Should I use it here? Or will Postgres lock these rows automatically with INSERT SELECT ON CONFLICT till the end of the transaction?

My goal is to prevent other apps from (concurrently) capturing rows with the inner SELECT which are already captured by this one.

解决方案

Yes, FOR UPDATE OF t2 SKIP LOCKED is the right approach to prevent race conditions with default Read Committed transaction isolation.

The added SKIP LOCKED also prevents deadlocks. Be aware that competing transactions might each get a partial set from the SELECT - whatever it could lock first.

While any transaction is atomic in Postgres, it would not prevent another (also atomic) transaction from selecting (and inserting - or at least trying) the same row, because SELECT without FOR UPDATE does not take an exclusive lock.

The 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.

Related:

• Postgres UPDATE … LIMIT 1

---

Clarifications:

• An SQL DML command like INSERT is always automatically atomic, since it cannot run outside a transaction. But you can't say that INSERT is a transaction. Wrong terminology.

• In Postgres all locks are kept until and released at the end of the current transaction.

这篇关于INSERT ... SELECT是原子事务吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！