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

On Thu, Feb 9, 2017 at 4:09 AM, CN <cnliou9(at)fastmail(dot)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.​