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?