I have table account
create table account (
user_id int primary key,
acc int,
amt int
);
with the next data
user_id | acc | amt
---------+------+-----
727 | 2620 | 10
777 | 2620 | 100
and I want to insert new row based on existing with conreate acc, and if there would be concurrent insert with this acc I want to rollback transaction
I tried to use serializable isolation level:
T1
begin;
set transaction isolation level serializable;
insert into account (user_id, acc, amt)
values (-1, 2630, (select sum(sub.amt)
from account sub where sub.acc = 2620));
-- here T2 made an insert, but i didn't get expected serialization error
commit;
T2
insert into account(user_id, acc, amt)
values(737, 2620, 100);
and as result there was no conflict, but should be. What am I missing?
2
Answers
There is no serialization problem. T2 is logically after T1, and no anomaly occurs.
Your code is working properly
Please check the db<>fiddle code.