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

Thanks a lot!

Result #2 is my favorite one. Transaction being rolled back is also
acceptable. I only want to avoid the anomaly #3.

According to "Chapter 13. Concurrency Control" in the document, it looks
to me that locks (and also atomicity?) are not automatically placed to
multiple statements wrapped in a transaction.

Given this function

(version 1):

CREATE FUNCTION f(amount INTEGER) RETURNS VOID AS $$

BEGIN

--Do time consuming statements here.

UPDATE accounts SET balance=balance+amount WHERE acnt=9;

--more time consuming statements here

END $$ LANGUAGE PLPGSQL VOLATILE;

, is there any chance that I might get result #3 (either $7 or $4,
depending on the execution order of the two transactions concurrently
performed in two sessions)?

If it is, can the following version prevent such anomaly?

(version 2):

CREATE FUNCTION f(amount INTEGER) RETURNS VOID AS $$

DECLARE

v INTEGER;

BEGIN

--Do time consuming statements here.

SELECT 1 INTO v FROM accounts WHERE acnt=9 FOR UPDATE;

UPDATE accounts SET balance=balance+amount WHERE acnt=9;

--more time consuming statements here

END $$ LANGUAGE PLPGSQL VOLATILE;

Further, if the second version does not help anything, does the
following third version, which moves "SELECT ... FOR UPDATE" out from
the function, help?

(version 3):

--session #1:

BEGIN;

SELECT 1 FROM accounts WHERE acnt=9 FOR UPDATE;

SELECT f(2);

COMMIT;

--session #2:

BEGIN;

SELECT 1 FROM accounts WHERE acnt=9 FOR UPDATE;

SELECT f(-1);

COMMIT;

Best Regards,

CN

On Thu, Feb 9, 2017, at 01:33 PM, David G. Johnston wrote:

> 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.

>

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