databasepostgresqltransactionsisolation-level

Postgres serializable isolation level confusion


I'm learning about database isolation levels and I'm attempting to walk through an example of when the serializable isolation level would cause concurrent transactions to error.

I have come up with the following scenario to attempt to reach this condition

a very table called Test with the following format

Test
Id: int

The following SQL queries

Query 1)

BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE
select id from test where id = 2
COMMIT

Query 2)

BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE
update test SET id = 3 WHERE id = 2
COMMIT

Lets imagine that I first run

select id from test where id = 2

And this returns one row

Then I run

update test SET id = 3 WHERE id = 2

Then I commit query2, and commit query1 in that order.

I was partially expecting the transaction to error because of this line in the documentation

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

Since query2 updates the row with id = 2, the result of query1 would change. Why does this not error?

I realize that there is an order in which these queries can be ran which leave the database in a consistent state, which is my overall understanding of the serializable isolation level. That goes query1 executes and returns, then query2 executes and returns. My question is, am I misinterpreting this section of the documentation, or is it wrong?


Solution

  • You understanding is mostly correct. The database takes the predicate locks and knows that there is a dependency, but that alone is not enough to throw a serialization error.

    As long as there is only a single dependency that requires that transaction A be logically before transaction B, we have no problem. Only if transaction B also has to be logically before transaction A at the same time, we are no longer serializable and have to abort a transaction.

    For example, if your first transaction runs

    UPDATE test SET id = 2 WHERE id = 3;
    

    and the second transaction concurrently runs

    UPDATE test SET id = 3 WHERE id = 2;
    

    They would have dependencies on each other, and you would get a serialization error.