PostreSQL中的INSERT和事务序列化 [英] INSERT and transaction serialization in PostreSQL

问题描述

我有一个问题。事务隔离级别设置为可序列化。当一个用户打开一个事务并在 table1中插入数据或更新数据，然后另一个用户打开一个事务并尝试将数据插入同一表时，第二个用户是否需要等待直到第一个用户提交事务？

I have a question. Transaction isolation level is set to serializable. When the one user opens a transaction and INSERTs or UPDATEs data in "table1" and then another user opens a transaction and tries to INSERT data to the same table, does the second user need to wait 'til the first user commits the transaction?

推荐答案

通常，不会。第二个事务仅在插入，因此除非需要进行唯一的索引检查或其他触发器，否则可以无条件地插入数据。对于唯一索引（包括主键），如果两个事务都更新具有相同值的行，它将阻塞，例如：

Generally, no. The second transaction is inserting only, so unless there is a unique index check or other trigger that needs to take place, the data can be inserted unconditionally. In the case of a unique index (including primary key), it will block if both transactions are updating rows with the same value, e.g.:

-- Session 1                           -- Session 2
CREATE TABLE t (x INT PRIMARY KEY);
BEGIN;
INSERT INTO t VALUES (1);
                                       BEGIN;
                                       INSERT INTO t VALUES (1);  -- blocks here
COMMIT;
                                       -- finally completes with duplicate key error

在更新的情况下事情不那么明显可能会影响其他交易的插入。我了解PostgreSQL在这种情况下还不支持真正的可序列化性。我不知道其他SQL系统如何普遍支持它。

Things are less obvious in the case of updates that may affect insertions by the other transaction. I understand PostgreSQL does not yet support "true" serialisability in this case. I do not know how commonly supported it is by other SQL systems.

请参见 http://www.postgresql.org/docs/current/interactive/mvcc.html

这篇关于PostreSQL中的INSERT和事务序列化的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！