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');
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?