does postgres reconcile transactions

user_1291986 0
Viewed 105

I'm wondering about the behaviour of updates and if they are reconciled between transactions:

for example, if Alice has a starting balance of 500, and two transactions have executed simultaneously (on two different connections):

UPDATE Account SET balance = balance - 100 WHERE name = 'Alice';

... long time ...

UPDATE Account SET balance = balance + 100 WHERE name = 'Bob';

and

UPDATE Account SET balance = balance - 50 WHERE name = 'Charlie';

... long time ...

UPDATE Account SET balance = balance + 50 WHERE name = 'Alice';

Will Postgres ensure that Alice's balance will always be 450? or will it sometimes be 550 or 400 depending on how the transactions have executed?

For example:

This is a big assumption but I'm assuming that for ACID, when a transaction starts, it will freeze the state of the database for that given transaction:

  1. Transaction B starts, when the balance is 500
  2. Transaction A starts before B finishes and when the balance is 500. It subtracts 100 -> Balance in A: 400.
  3. Transaction B adds 50 (in parallel to A) to the balance it sees had in (1.) -> Balance in B: 550
  4. Transaction B finishes and commits.
  5. Transaction A finishes and commits.

When the two transactions complete - A has the balance being 400 and Transaction B has the balance being 550.

So, the database needs to know that two updates have been made to the same place and reconcile that.

postgresql
1 Answers

The result will always be 450. UPDATE statements are atomic in PostgreSQL, and there can be no lost updates.

The reason is that PostgreSQL takes an exclusive lock on a row it updates, and if it has to wait to get the lock, it will re-read the row after acquiring the lock. With the default READ COMMITTED isolation level, that guarantees that the transaction will see the most recent value, and that there cannot be a race condition that loses updates. (With REPEATABLE READ, you would get a serialization error.)

The documentation describes this in no uncertain terms.

user_64643080
Related