Understanding Postgres SERIALIZABLE isolation level

Question

I have two experiments that do not work as expect after reading postgres documentation. I use postgres 12

Experiment 1

Data preparation:

CREATE TABLE Test 
(
    id SERIAL primary key,
    level int,
    value int
);

INSERT INTO Test (
    level,
    value
)
SELECT 1, 10 UNION ALL
SELECT 1, 20 UNION all
SELECT 1, 30 UNION ALL
SELECT 2, 100;

Then I open two query windows

Window 1

BEGIN TRANSACTION ISOLATION level SERIALIZABLE;

INSERT INTO Test(level, value)
SELECT 2, SUM(value)
FROM Test
WHERE level = 1

Window 2

BEGIN TRANSACTION ISOLATION level SERIALIZABLE;

INSERT INTO Test(level, value)
SELECT 3, SUM(value)
FROM Test
WHERE level = 2

Now, if I commit first Window 1 then Window 2, Window 2 fails because the data it has read is stale, which is expected. However, if I first commit Window 2 then Window 1, Window 1 fails, but why? Window 2 has already committed, its result has not been affected by Window 1. The result of Window 1 has not been affected either. Therefore, I don't understand why Window 1 commit fails after Window 2 commit

Experiment 2

It is very similar to experiment 1, but now different levels are stored in different tables.

Data preparation

CREATE TABLE Level1 (
    id SERIAL primary key,
    value int
);

CREATE TABLE Level2 (
    id SERIAL primary key,
    value int
);

CREATE TABLE Level3 (
    id SERIAL primary key,
    value int
);

INSERT INTO Level1 (
    value
)
SELECT 10 UNION ALL
SELECT 20 UNION all
SELECT 30; 

INSERT INTO Level2 (
    value
)
SELECT 100;

Window 1

BEGIN TRANSACTION ISOLATION level SERIALIZABLE;

INSERT INTO Level2(value)
SELECT SUM(value)
FROM Level1

Window 2

BEGIN TRANSACTION ISOLATION level SERIALIZABLE;

INSERT INTO Level3(value)
SELECT SUM(value)
FROM Level2

Now both windows commit successfully in any order I commit them! It totally confuses me. Logically, it is the same as experiment 1, I would understand if it worked as in experiment 1, but here the serializable isolation does not seem to be working at all!

Laurenz Albe · Accepted Answer · 2020-09-19 08:42:45Z

5

In experiment 1, both transaction read and write the same tables, so there is a potential for conflict.

You are right that there is no actual conflict, but the SELECT statements you ran performed a sequential scan, which reads all rows and consequently places a predicate lock on the whole table. That is why you get a false positive serialization error.

Compare what the documentation has to say:

Predicate locks in PostgreSQL, like in most other database systems, are based on data actually accessed by a transaction. These will show up in the pg_locks system view with a mode of SIReadLock. The particular locks acquired during execution of a query will depend on the plan used by the query

While PostgreSQL's Serializable transaction isolation level only allows concurrent transactions to commit if it can prove there is a serial order of execution that would produce the same effect, it doesn't always prevent errors from being raised that would not occur in true serial execution.

This does not happen in your second experiment. The two transactions can be serialized (like in your first experiment): first the one in window 2, then the one in window 1. This time there is no false positive serialization error.

answered Sep 19, 2020 at 8:42

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

XanderMK Over a year ago

So, my understanding was "If two transactions run concurrently and they would produce different results depending on the order of execution, one of the transaction must be aborted". But in reality it is "If two transactions run concurrently and there is at least one order of execution that would produce the same results, no transaction needs to be aborted". With some false positives, of course. Is it correct now?

Laurenz Albe Over a year ago

Yes, that is the definition of "serializable". To quote the standard: A serializable execution is defined to be an execution of the operations of concurrently executing SQL-transactions that produces the same effect as some serial execution of those same SQL-transactions.

Collectives™ on Stack Overflow

Understanding Postgres SERIALIZABLE isolation level

Experiment 1

Experiment 2

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

Experiment 1

Experiment 2

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related