INSERT and transaction serialization in PostreSQL

Question

I have a question. The transaction isolation level is set to serializable. When one user opens a transaction and INSERT or UPDATE data in "table1", and then another user opens a transaction and tries to insert INSERT data into the same table, the second user must wait "before the first user completes the transaction?" / p>

Answer 1

Generally not. The second transaction only inserts, therefore, if there is no unique check on the index or other trigger that should occur, data can be inserted unconditionally. In the case of a unique index (including the primary key), it will block if both transactions update rows with the same value, for example:

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

Things are less obvious with updates that may affect insertions by another transaction. I understand that PostgreSQL does not yet support "true" serialization in this case. I do not know how is usually supported by other SQL systems.

See http://www.postgresql.org/docs/current/interactive/mvcc.html

Answer 2

, .