Re: [HACKERS] Stupid question on Read Committed Isolation Level
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! ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Stupid question on Read Committed Isolation Level
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 ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Stupid question on Read Committed Isolation Level
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 ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] Stupid question on Read Committed Isolation Level
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 [EMAIL PROTECTED] 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 [EMAIL PROTECTED] PostgreSQL Inc. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Stupid question on Read Committed Isolation Level
Chris Bowlby [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] Stupid question on Read Committed Isolation Level
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 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] Stupid question on Read Committed Isolation Level
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: [EMAIL PROTECTED] Yahoo!: yscrappy ICQ: 7615664 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Stupid question on Read Committed Isolation Level
Tom Lane [EMAIL PROTECTED] writes: Marc G. Fournier [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
