skip to Main Content

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


  1. There is no serialization problem. T2 is logically after T1, and no anomaly occurs.

    Login or Signup to reply.
  2. Your code is working properly

    Please check the db<>fiddle code.

    Login or Signup to reply.
Please signup or login to give your own answer.
Back To Top
Search