Re: [NOVICE] What happens to concurrent update to the same row?

On Wed, Feb 8, 2017 at 9:58 PM, CN <cnliou9@fastmail.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.

-- 
http://www.fastmail.com - A fast, anti-spam email service.