[SOLVED] Does PostgreSQL read committed isolation level guarantee 2 transactions: subtract 3 and subtract 5 together always result 10

Does PostgreSQL read committed isolation level guarantee 2 transactions: subtract 3 and subtract 5 together always result 10 - 8 = 2?

Does PostgreSQL read committed isolation level guarantee 2 transactions: subtract 3 and subtract 5 together always result 10 - 8 = 2 ?

i.e. PostgreSQL

Origin_value = 10
1 Tr = 1) read Origin_value  2) subtract 3  3) commit
2 Tr = 1) read Origin_value  2) subtract 5  3) commit

is the result

Origin_value = 2

guaranteed?

Solution

If you read and update the row with the same statement:

UPDATE tab SET value = value - 3 WHERE pkey = 42;

it is guaranteed that your transactions cannot cause an anomaly, that is, the result will always be two.

If you first SELECT the value and then UPDATE it with a second SQL statement, you can end up with a “lost update” anomaly. To protect yourself from that, you can either use REPEATABLE READ isolation or stay on READ COMMITTED and use SELECT ... FOR NO KEY UPDATE.