PostgreSQL Serialisation failure on different ids
6
votes
1
answer
1125
views
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?
Asked by Yury Oparin
(63 rep)
Aug 24, 2019, 04:23 PM
Last activity: Aug 28, 2019, 06:38 AM
Last activity: Aug 28, 2019, 06:38 AM