Why does PostgreSQL think there is a conflict between the two serializable transactions?

Question

I'm trying to figure out how the serializable isolation level in PostgreSQL works. In theory and according to PostgreSQL's own documentation PostgreSQL should be smart enough to somehow detect serialization conflicts and automatically roll back offending transactions. Yet when I tried to play with serializable isolation level myself I stumbled upon a lot of false positives and started to doubt my own understanding of the concept of serializability or PostgreSQL's implementation of it. Below you can find one of the simplest examples of such false positives:

create table mytab(
    class integer,
    value integer not null
);

create index mytab_class_idx on mytab (class);

insert into mytab (class, value) values (1, 10);
insert into mytab (class, value) values (1, 20);
insert into mytab (class, value) values (2, 100);
insert into mytab (class, value) values (2, 200);

The table data is the following:

 class | value
-------+-------
     1 |    10
     1 |    20
     2 |   100
     2 |   200

Then I run two concurrent transactions. Step n comments in code show an order in which I execute the statements. Following advice from https://stackoverflow.com/a/42303225/3249257 I explicitly disabled sequential scan to force PostgreSQL to use an index:

SET enable_seqscan=off;

Transaction A:

begin; -- step 1
select sum(value) from mytab where class = 1; -- step 2
insert into mytab(class, value) values (3, 30); -- step 5
commit; -- step 7

Transaction B:

begin; -- step 3
select sum(value) from mytab where class = 2; -- step 4
insert into mytab(class, value) values (4, 300); -- step 6
commit; -- step 8

As I understand it, there shoudn't be any conflict between those two transactions. They don't touch the same rows. However, when I commit the second transaction it fails with this error:

[40001] ERROR: could not serialize access due to read/write dependencies among transactions
Detail: Reason code: Canceled on identification as a pivot, during commit attempt.
Hint: The transaction might succeed if retried.

What's going on here? Is my understanding of serializable isolation level flawed? Is it a failure of PostgreSQL's heuristics mentioned in this answer https://stackoverflow.com/a/50809788/3249257?

I'm using PostgreSQL 11.5 on x86_64-apple-darwin18.6.0, compiled by Apple LLVM version 10.0.1 (clang-1001.0.46.4), 64-bit.

Quick guess: Exclusive lock on entire table - https://wiki.postgresql.org/wiki/Lock_Monitoring and https://www.postgresql.org/docs/12/explicit-locking.html#LOCKING-DEADLOCKS — Lukasz Szozda, Dec 25 '19 at 15:11
What version are you using? Can you show us exactly how you interleave the commands? If I run the first 3 lines of A, then first 3 of B, then commit A, then commit B, I don't get the problem. — jjanes, Dec 25 '19 at 16:01
@LukaszSzozda it wasn't exclusive lock on the entire table, but your comment has greatly helped me to find out what's going on here. I've posted my answer below. — Denis Stafichuk, Dec 25 '19 at 16:53

score 3 · Accepted Answer · answered Dec 25 '19 at 16:43

The problem here is with predicate locks (SIReadLock) that are used by PostgreSQL to figure out whether there is a conflict between concurrent transactions. If you run the query bellow during the course of transactions' execution, you will see these locks:

select relation::regclass, locktype, page, tuple, pid from pg_locks
where mode = 'SIReadLock';

In this case, the issue was with page locks on the mytab_class_idx index. If the concurrent transactions happen to acquire a lock for the same page of mytab_class_idx relation, serialization conflict occurs. If they acquire locks for different pages, they both commit successfully.

If there is not enough data like in the question above, index entries for all rows will fall on the same page and then a serialization conflict will inevitably occur. For big enough tables serialization conflicts will happen rarely, though not as rare as they could.

Why does PostgreSQL think there is a conflict between the two serializable transactions?

1 Answers1