CODEWITHSUNDEEP
postgresql
XanderMK
XanderMK

Reputation: 389

Understanding Postgres SERIALIZABLE isolation level

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!

Upvotes: 4

Views: 3016

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 246493

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.

Upvotes: 5

Related Questions