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?