Re: What happens to concurrent update to the same row?

On Wed, Feb 8, 2017 at 9:58 PM, CN <cnliou9(at)fastmail(dot)fm> wrote:

> 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.
>
>
​Both transactions are touching the same single row - a deadlock cannot
happen.​

The answer, IIRC, is #2 (easy enough to test this if you don't want to
trust my memory).

The single update statement will hold a lock while reading balance and will
not release it until the change has been committed or rolled back.

Explicit locking (i.e., SELECT ... FOR UPDATE) is needed if you, the user,
break this atomicity by reading via select and then attempting an update
using that value.

David J.