Обсуждение: postgres transaction isolation when rollback
Hi,
I've been using Postgres 8.1.x (and 8.0.x) for the past year or so and have been really impressed and pleased with it so far.
I've read the Manual regarding transaction isolation and searched the lists but still could not figure out the following (which seems to me like a simple question):
Suppose I have two concurrent transactions similar to those in the manual [http://www.postgresql.org/docs/7.4/interactive/transaction-iso.html ]
Transaction 1
BEGIN;
UPDATE accounts SET balance = balance + 100.00 WHERE acctnum = 12345;
UPDATE accounts SET balance = balance - 100.00 WHERE acctnum = 7555;
COMMIT;
UPDATE accounts SET balance = balance + 100.00 WHERE acctnum = 12345;
UPDATE accounts SET balance = balance - 100.00 WHERE acctnum = 7555;
COMMIT;
Transaction 2
BEGIN;
UPDATE accounts SET balance = balance + 200.00 WHERE acctnum = 12345;
UPDATE accounts SET balance = balance - 200.00 WHERE acctnum = 7534;
COMMIT;
UPDATE accounts SET balance = balance + 200.00 WHERE acctnum = 12345;
UPDATE accounts SET balance = balance - 200.00 WHERE acctnum = 7534;
COMMIT;
(notice the difference in the amount added to the balance, 100 vs. 200)
Suppose now that the order is like so
transaction 1: UPDATE accounts SET balance = balance + 100.00 WHERE acctnum = 12345;
transaction 2: UPDATE accounts SET balance = balance + 200.00 WHERE acctnum = 12345;
transaction 1: transaction failure
transaction 1: transaction failure
transaction 2: UPDATE accounts SET balance = balance - 200.00 WHERE acctnum = 7534;
Would the balance of account 12345 increase by 200 or 300 ?
by transaction failure I mean that either
1) a rollback was issued
2) the transaction failed but no rollback occurred (especially, with respect to the following error: "current transaction is aborted, queries ignored until END of transaction block")
3) supposing there was some savepoint before the first UPDATE in the first transaction and a ROLLBACK TO SAVEPOINT was issued.
I appreciate your feedback.
Thanks.
-- Tomer Levinboim
"Tomer Levinboim" <levinboim.tomer@gmail.com> writes:
> Suppose now that the order is like so
> transaction 1: UPDATE accounts SET balance = balance + 100.00 WHERE acctnum
> = 12345;
> transaction 2: UPDATE accounts SET balance = balance + 200.00 WHERE acctnum
> = 12345;
> transaction 1: transaction failure
> transaction 2: UPDATE accounts SET balance = balance - 200.00 WHERE acctnum
> = 7534;
> Would the balance of account 12345 increase by 200 or 300 ?
200. Transaction 2 will block at its first UPDATE waiting to see if
transaction 1 commits or not, and will then use the appropriate version
of the row as the starting point for its update.
(I'm assuming you're speaking of READ COMMITTED rules here --- if T2
is SERIALIZABLE then the answer is different.)
regards, tom lane