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

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

From
CN
Date:
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



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

From
"David G. Johnston"
Date:
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.

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

From
CN
Date:
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@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


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.

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

From
"David G. Johnston"
Date:
On Thu, Feb 9, 2017 at 4:09 AM, CN <cnliou9@fastmail.fm> wrote:
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.

​Correct.

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)?

​This executes in the exact same way your original example of this form, without the function wrapper, would.​


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;

​​
​The FOR UPDATE effects an explicit lock on the row acnt=9; doesn't matter whether a function is used for structural organization or not.  In your example it is also pointless since the UPDATE is still self-contained per the previous email.


It is customary on these lists to inline or bottom post like I have these two times.  Trimming no-longer-relevant context in the process is also appreciated.

David J.​