Re: [NOVICE] 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;