3

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?

  • Sorry for confusion @jjanes. I get serialisation failure, which essentially hides unique key violation. – Yury Oparin Aug 24 at 18:08
  • @jjanes I use Postgres 11.5 with two psql connections open. After creating a table, in each terminal window I put: begin isolation level serializable; For T1 I add: select * from u where uid = 2; and for T2: select * from u where uid = 3; After they reveal 'no rows' result, I do T1: insert into u (uid, name) values (2, 'A'); and T2: insert into u (uid, name) values (3, 'B'); I commit T1, which succeeds, but T2 on commit; returns 'could not serialize access due to read/write dependencies among transactions'. – Yury Oparin Aug 24 at 18:13
  • @jjanes 100%. Do you do the same sequence of commands? – Yury Oparin Aug 24 at 18:38
  • Let us continue this discussion in chat. – Yury Oparin Aug 24 at 18:48
  • Your comment would do better as an edit than a comment. I can reproduce your results, but I don't know why it works that way. Making it easier to read might attract more attention. – jjanes Aug 24 at 20:03
1

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.

  • I could reproduce the problem and have modified the answer accordingly. – Laurenz Albe Aug 26 at 10:14
  • What an insight into the code, bravo! Unfortunately, even after populating u with insert into u(uid, name) select x.id, 'A' || x.id form generate_series(1,100000) AS x(id); and running ANALYZE u;, I still get a serialisation failure for uid = 100001 and uid=100002. – Yury Oparin Aug 26 at 12:25
  • Then they are locking the same index page. I'll update the answer to explain that better. – Laurenz Albe Aug 26 at 12:37
  • Index page locking in fact only happens with 'new' selects. So If we consider another case with select + update (instead of 'insert') even for adjacent uid = 1 and uid = 2, the first time the last transaction to commit will throw a serialisation failure on read/write dependencies like for the select + insert case, but a second time the same sequence of queries will result in both transactions succeeding. – Yury Oparin Aug 27 at 20:21
  • Actually, the whole index page gets 'loaded' and within it no false positives occur later. – Yury Oparin Aug 27 at 20:48

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.