Conflict on two PostgreSQL transactions with serializable isolation level

user_2406502 0
Viewed 258

I have two concurrent SQL transactions with the most strict level of isolation (serializable)

According to here:

The SQL standard defines four levels of transaction isolation. The most strict is Serializable, which is defined by the standard in a paragraph which says that any concurrent execution of a set of Serializable transactions is guaranteed to produce the same effect as running them one at a time in some order

But you see in the middle of the second transaction the table is empty. How is that possible and how can I fix it??

Conflict on two Postgres transactions with serializable isolation level

serializable postgresql transactions sql
2 Answers

This is a consequence of the way Postgres provides system catalogs. The relevant note in the documentation is clear enough:

Internal access to the system catalogs is not done using the isolation level of the current transaction. This means that newly created database objects such as tables are visible to concurrent Repeatable Read and Serializable transactions, even though the rows they contain are not.

The example you provided does not break the cited rules about the serializable isolation level. Note, that you start the second transaction when the table was dropped and created by the first transaction. The resulting behavior is as expected.

If you had started both transactions before the table was dropped, then one of them would be suspended until the other completes.

user_19957380

The SQL Server documentation is clearly not relevant.

But yes, that looks like a PostgreSQL bug that you should report. But please with an SQL script, not with an animated GIF...

user_64643080
Related