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