What is the proper isolation level needed in this postgres query with concurrent transaction?

Question

I have 2 concurrent transactions

on table X with the following column

id
count

and

table Y with the following column

id
somefield

currently there is only 1 row in tableX

id=1
count=0

and a million row in tableY

transaction 1

Begin
 select * where id = 1 from tableX
 select count(*) from tableY
 update set count = count + 1 from tableX
END

transaction 2

Begin
 select * where id = 1 from tableX
 select count(*) from tableY
 update set count = count + 1 from tableX
END

If isolation level is REPEATABLE READ , postgres will snapshot id=1

and the result of both transaction will be count=1 instead of count=2

Should i use SERIALIZABLE isolation to lock the row instead? But if I use SERIALIZABLE isolation it locks the entire tableY will millions of row

is there more fine grain lock available in postgres?

Answer 1

If isolation level is REPEATABLE READ , postgres will snapshot id=1 and the result of both transaction will be count=1 instead of count=2

That is bit unclear how did you come to such conclusion and whether you have checked that or not. The phenomenon, you are asking about, is actually called (P4) Lost Update and it is forbidden in RR isolation level, regardless how you perform updates:

-- tx1
tx1=> begin transaction isolation level repeatable read;
BEGIN
tx1=*> update c set count=1 where id=1;
UPDATE 1
tx1=*>
-- tx2
tx2=> begin transaction isolation level repeatable read;
BEGIN
tx2=*> update c set count=2 where id=1;
... WAITING ...
-- tx1
tx1=*> commit;
COMMIT
tx1=> select * from c;
 id | count 
----+-------
 1 | 1
(1 row)
-- tx2
ERROR: could not serialize access due to concurrent update
tx2=!> rollback;
ROLLBACK

OR

-- tx1
tx1=> begin transaction isolation level repeatable read;
BEGIN
tx1=*> update c set count=count+1 where id=1;
UPDATE 1
tx1=*>
-- tx2
tx2=> begin transaction isolation level repeatable read;
BEGIN
tx2=*> update c set count=count+1 where id=1;
... WAITING ...
-- tx1
tx1=*> commit;
COMMIT
-- tx2
ERROR: could not serialize access due to concurrent update
tx2=!> rollback;
ROLLBACK

The actual purpose of your update statement, based on DB state (someones call it atomic update or atomic write), is to maintain consistent state in DB even using RC isolation level:

-- tx1
tx1=> begin; 
BEGIN
tx1=*> select * from c;
 id | count 
----+-------
 1 | 3
(1 row)
tx1=*> update c set count=count+1 where id=1;
UPDATE 1
tx1=*> select * from c;
 id | count 
----+-------
 1 | 4
(1 row)
-- tx2
tx2=> begin; 
BEGIN
tx2=*> select * from c;
 id | count 
----+-------
 1 | 3
(1 row)
tx2=*> update c set count=count+1 where id=1;
... WATING ...
-- tx1
tx1=*> commit;
COMMIT
-- tx2
UPDATE 1
tx2=*> select * from c;
 id | count 
----+-------
 1 | 5
(1 row)
tx2=*> commit;
COMMIT