Thread: Stupid question on Read Committed Isolation Level
http://www.postgresql.org/docs/7.3/static/transaction-iso.html#XACT-READ-COMMITTED Reading this: " BEGIN;UPDATE accounts SET balance = balance + 100.00 WHERE acctnum = 12345;UPDATE accounts SET balance = balance - 100.00WHERE acctnum = 7534;COMMIT; If two such transactions concurrently try to change the balance of account 12345, we clearly want the second transaction to start from the updated version of the account's row. Because each query is affecting only a predetermined row, letting it see the updated version of the row does not create any troublesome inconsistency. " What happens if I abort on the first transaction? If I'm reading this right, if Trans2 does the exact same as above, and COMMITs before Trans1 Aborts, the value of balance becomes +200 (Trans2 + Trans1) ... but what happens when Trans1 ABORTS? Trans2 believes its COMMIT worked, but ABORTng Trans1 will rollback to the original value, no? Or am I reading that section wrong? :( ---- Marc G. Fournier Hub.Org Networking Services (http://www.hub.org) Email: scrappy@hub.org Yahoo!: yscrappy ICQ: 7615664
On Thu, Jan 29, 2004 at 01:33:48PM -0400, Marc G. Fournier wrote: > What happens if I abort on the first transaction? If I'm reading this Doesn't matter, because your second transaction doesn't read any of the changes you're making there--until (and if) that first one commits. The second transaction simply doesn't care if the the first has been aborted or is still running. It would if the transaction level were READ UNCOMMITTED, but with postgres we don't need to worry about that. > right, if Trans2 does the exact same as above, and COMMITs before Trans1 > Aborts, the value of balance becomes +200 (Trans2 + Trans1) ... but what > happens when Trans1 ABORTS? Trans2 believes its COMMIT worked, but > ABORTng Trans1 will rollback to the original value, no? Trans2's commit did work, and it never did influence Trans1. And even if it did, no matter because Trans1 never happened. In this kind of application of course you would want to use SERIALIZABLE instead--and that deals with paradoxes by failing the COMMIT. Jeroen
On Thu, Jan 29, 2004 at 06:54:21PM +0100, Jeroen T. Vermeulen wrote: > On Thu, Jan 29, 2004 at 01:33:48PM -0400, Marc G. Fournier wrote: > > > What happens if I abort on the first transaction? If I'm reading this AFAICS the part about not having inconsistencies refers only to the spectre of 'balance' being changed between its two conceptual accesses in "SET balance = balance + 100.00". The risk that remains is that the two updates could see a combined state of the two relevant rows that may never have existed at the same time--which doesn't matter for this simple example. This is explained in the text, but it remains a little murky IMHO. Jeroen
"Marc G. Fournier" <scrappy@hub.org> writes:
> What happens if I abort on the first transaction? If I'm reading this
> right, if Trans2 does the exact same as above, and COMMITs before Trans1
> Aborts, the value of balance becomes +200 (Trans2 + Trans1) ... but what
> happens when Trans1 ABORTS? Trans2 believes its COMMIT worked, but
> ABORTng Trans1 will rollback to the original value, no?
If trans2 is the second to get to the row, it will *wait* until trans1
either commits or aborts, and then use the new or old version of the row
accordingly. The scenario you are thinking of can't happen.
regards, tom lane
On Thu, 29 Jan 2004, Jeroen T. Vermeulen wrote: > On Thu, Jan 29, 2004 at 01:33:48PM -0400, Marc G. Fournier wrote: > > > What happens if I abort on the first transaction? If I'm reading this > > Doesn't matter, because your second transaction doesn't read any of the > changes you're making there--until (and if) that first one commits. The > second transaction simply doesn't care if the the first has been aborted > or is still running. It would if the transaction level were READ > UNCOMMITTED, but with postgres we don't need to worry about that. Wait, did you read what I had originally posted? According to the docs for what I read: "If two such transactions concurrently try to change the balance of account 12345, we clearly want the second transaction to start from the updated version of the account's row" To me, I read this as the first transaction has not yet committed, but the second sees its changes ... so if second commitst, and first hasn't yet, second commits with seconds changes + firsts changes, but what if first aborts? ---- Marc G. Fournier Hub.Org Networking Services (http://www.hub.org) Email: scrappy@hub.org Yahoo!: yscrappy ICQ: 7615664
On Thu, 29 Jan 2004, Tom Lane wrote: > "Marc G. Fournier" <scrappy@hub.org> writes: > > What happens if I abort on the first transaction? If I'm reading this > > right, if Trans2 does the exact same as above, and COMMITs before Trans1 > > Aborts, the value of balance becomes +200 (Trans2 + Trans1) ... but what > > happens when Trans1 ABORTS? Trans2 believes its COMMIT worked, but > > ABORTng Trans1 will rollback to the original value, no? > > If trans2 is the second to get to the row, it will *wait* until trans1 > either commits or aborts, and then use the new or old version of the row > accordingly. The scenario you are thinking of can't happen. Thank you, that answers it ... so, simplistically, since they are both hitting the same row, there is a pseudo-commit lock on that row by the first transaction ... ---- Marc G. Fournier Hub.Org Networking Services (http://www.hub.org) Email: scrappy@hub.org Yahoo!: yscrappy ICQ: 7615664
Would this not create the potention for a dead lock if transaction1 is never completed, and still active for an indefinate period of time? On Thu, 2004-01-29 at 14:06, Tom Lane wrote: > "Marc G. Fournier" <scrappy@hub.org> writes: > > What happens if I abort on the first transaction? If I'm reading this > > right, if Trans2 does the exact same as above, and COMMITs before Trans1 > > Aborts, the value of balance becomes +200 (Trans2 + Trans1) ... but what > > happens when Trans1 ABORTS? Trans2 believes its COMMIT worked, but > > ABORTng Trans1 will rollback to the original value, no? > > If trans2 is the second to get to the row, it will *wait* until trans1 > either commits or aborts, and then use the new or old version of the row > accordingly. The scenario you are thinking of can't happen. > > regards, tom lane > > ---------------------------(end of broadcast)--------------------------- > TIP 7: don't forget to increase your free space map settings -- Chris Bowlby <chris@pgsql.com> PostgreSQL Inc.
Chris Bowlby <chris@pgsql.com> writes:
> Would this not create the potention for a dead lock if transaction1 is
> never completed, and still active for an indefinate period of time?
If trans1 later waits (directly or indirectly) for trans2, we'll detect
the deadlock and abort one xact or the other to clear it. If trans1 is
idle because the client is asleep at the wheel, that could be
unfortunate, but it's not a deadlock.
regards, tom lane
On Thu, Jan 29, 2004 at 02:07:25PM -0400, Marc G. Fournier wrote: > > "If two such transactions concurrently try to change the balance of > account 12345, we clearly want the second transaction to start from the > updated version of the account's row" > > To me, I read this as the first transaction has not yet committed, but the > second sees its changes ... so if second commitst, and first hasn't yet, > second commits with seconds changes + firsts changes, but what if first > aborts? There's the rub--it doesn't say the part about "has not yet committed," although I can see how you could read it that way. Jeroen
On Thu, 29 Jan 2004, Jeroen T. Vermeulen wrote: > On Thu, Jan 29, 2004 at 02:07:25PM -0400, Marc G. Fournier wrote: > > > > "If two such transactions concurrently try to change the balance of > > account 12345, we clearly want the second transaction to start from the > > updated version of the account's row" > > > > To me, I read this as the first transaction has not yet committed, but the > > second sees its changes ... so if second commitst, and first hasn't yet, > > second commits with seconds changes + firsts changes, but what if first > > aborts? > > There's the rub--it doesn't say the part about "has not yet committed," > although I can see how you could read it that way. I would say that "two such transactions concurrently" heavily implies such, no? :) ---- Marc G. Fournier Hub.Org Networking Services (http://www.hub.org) Email: scrappy@hub.org Yahoo!: yscrappy ICQ: 7615664
Tom Lane <tgl@sss.pgh.pa.us> writes: > "Marc G. Fournier" <scrappy@hub.org> writes: > > What happens if I abort on the first transaction? If I'm reading this > > right, if Trans2 does the exact same as above, and COMMITs before Trans1 > > Aborts, the value of balance becomes +200 (Trans2 + Trans1) ... but what > > happens when Trans1 ABORTS? Trans2 believes its COMMIT worked, but > > ABORTng Trans1 will rollback to the original value, no? > > If trans2 is the second to get to the row, it will *wait* until trans1 > either commits or aborts, and then use the new or old version of the row > accordingly. The scenario you are thinking of can't happen. I'm not clear if the original poster is specifically asking about this scenario or trying to understand in what scenario where READ COMMITTED can produce paradoxical results. In the example given I think there's no problem because the first part of the transaction is an update, which effectively serializes the two transactions. However, if the first query in one of the transactions is a SELECT then READ COMMITTED has the possibility of "phantom" updates, where the update appears to be lost. So if you do something like: x = (select balance from a); update a set balance = x+100; and both transactions execute this in read committed mode, then it's possible that they both see the original balance. One will increase the balance by 100, but the second will only set the balance again based on the original balance. It is for applications like this that SERIALIZABLE mode becomes necessary. -- greg
On Thu, Jan 29, 2004 at 03:27:06PM -0400, Marc G. Fournier wrote: > > I would say that "two such transactions concurrently" heavily implies > such, no? :) Like I said, the text tries to explain it--but it remains murky!