CODEWITHSUNDEEP
postgresqltransactions
Wickoo
Wickoo

Reputation: 7387

Postgres's serializable transaction blocks concurrent write

In the PostgreSQL's isolation levels documentation it is stated that

To guarantee true serializability PostgreSQL uses predicate locking, which means that it keeps locks which allow it to determine when a write would have had an impact on the result of a previous read from a concurrent transaction, had it run first. In PostgreSQL these locks do not cause any blocking and therefore can not play any part in causing a deadlock. They are used to identify and flag dependencies among concurrent Serializable transactions which in certain combinations can lead to serialization anomalies.

When I try to execute two update statements in the isolation level SERIALIZABLE, it appears that the second write blocks until the first transaction commits or aborts. You can see the interaction int he gif file blow:

enter image description here

How does the about statement explain this behavior? I expected the second transaction continues until commit and then get could not serialize error.

BTW, the event table's schema is as follows:

CREATE TABLE event (
  id INT,
  created_at timestamp
)

Upvotes: 4

Views: 2015

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 247260

The paragraph describes only predicate locks, which are a special class of locks only used on the serializable isolation level.

Besides these special locks, a serializable transaction will also take the "normal" locks used by other isolation levels.

Upvotes: 2

Related Questions