Let's assume the transaction isolation level is the default "read
committed" and the balance of acnt# 9 is 5 now.
The first transaction does this:
UPDATE accounts SET balance=balance+2 WHERE acnt=9;
The second transaction executes the following command at the same time
when the first transaction is still in progress (not commits yet):
UPDATE accounts SET balance=balance-1 WHERE acnt=9;
Which one of the following scenarios will happen?
1. PostgreSQL automatically detects this as a conflict and aborts
transaction 2.
2. Transaction 1 automatically locks the target row and therefore blocks
transaction 1. Transaction 2 waits until transaction 1 completes and
then it continues its execution. The result is the correct balance $6.
I propose this scenario (2) because the documentation
https://www.postgresql.org/docs/9.6/static/explicit-locking.html#LOCKING-DEADLOCKS
reads:
"Note that deadlocks can also occur as the result of row-level locks
(and thus, they can occur even if explicit locking is not used)."
Note the words "even if explicit locking is not used".
3. Transaction 1 reads balance $5 and then writes $7. Before transaction
1 commits, transaction 2 reads $5 and writes and commits $4 before
transaction 1. Transaction 1 commits $7, which is the final unexpected
result.
Best Regards,
CN
--
http://www.fastmail.com - IMAP accessible web-mail