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.