PostgreSQL Serialisation failure on different ids

Serializable isolation mode can be used to avoid race conditions when upserting equal ids. So for create table u(uid int primary key, name text); if we run two similar transactions T1 and T2:

begin isolation level serializable;
select * from u where uid = 1;

And then continue with for T1 and T2:

insert into u (uid, name) values (1, 'A');

After commit; only the first succeeds while the other throws a serialisation failure.

This is a very neat feature of the mode to handle unique key violations in complex transactions and not resorting to a specific 'hack' insert ... on conflict. However even if uids are different, say uid = 2 and uid = 3, both transactions T1 and T2 still won't be able to commit.

How could it be possible? Supposedly they create different predicate SIReadlocks and select uses index scan. Where's the trick?

The reason is that the table is completely empty.

See the following code in _bt_first in src/backend/access/nbtree/nbtsearch.c:

if (!BufferIsValid(buf))
{
    /*
     * We only get here if the index is completely empty. Lock relation
     * because nothing finer to lock exists.
     */
    PredicateLockRelation(rel, scan->xs_snapshot);
[...]
    return false;
}
else
    PredicateLockPage(rel, BufferGetBlockNumber(buf),
                      scan->xs_snapshot);

Normally, the index scan would put an SIRead lock on the index page where the index entry should be, but since the index is empty, PostgreSQL resorts to putting an SIRead lock on the whole table.

Now since both transactions do that, you get a serialization error at the end of one of them, because the writes conflict with the table-wide read lock.

If the table were not empty, you would notice that concurrent transactions like these sometimes succeed, because they affect different index pages. If the affected uids are sufficiently close together that they are on the same index page, you would still experience a “false positive” serialization error. This is documented:


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. In particular, it is possible to see unique constraint violations caused by conflicts with overlapping Serializable transactions even after explicitly checking that the key isn't present before attempting to insert it.

To see what kind of SIReadLock PostgreSQL uses, inspect pg_locks.

StackExchange Database Administrators Q#246179, answer score: 4