
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.00 WHERE 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

Thu, 29 Jan 2004 13:33:48 -0400 (AST)
"Marc G. Fournier" <scrappy@hub.org>
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


Thu, 29 Jan 2004 18:54:21 +0100
"Jeroen T. Vermeulen" <jtv@xs4all.nl>
Re: 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


Thu, 29 Jan 2004 19:04:04 +0100
"Jeroen T. Vermeulen" <jtv@xs4all.nl>
Re: Stupid question on Read Committed Isolation Level
"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

Thu, 29 Jan 2004 13:06:59 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: Stupid question on Read Committed Isolation Level 
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

Thu, 29 Jan 2004 14:07:25 -0400 (AST)
"Marc G. Fournier" <scrappy@postgresql.org>
Re: Stupid question on Read Committed Isolation Level
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

Thu, 29 Jan 2004 14:09:07 -0400 (AST)
"Marc G. Fournier" <scrappy@postgresql.org>
Re: 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" <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.


Thu, 29 Jan 2004 14:16:23 -0400
Chris Bowlby <chris@pgsql.com>
Re: Stupid question on Read Committed Isolation Level
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

Thu, 29 Jan 2004 13:21:34 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: 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


Thu, 29 Jan 2004 19:34:57 +0100
"Jeroen T. Vermeulen" <jtv@xs4all.nl>
Re: 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: scrappy@hub.org           Yahoo!: yscrappy              ICQ: 7615664

Thu, 29 Jan 2004 15:27:06 -0400 (AST)
"Marc G. Fournier" <scrappy@postgresql.org>
Re: Stupid question on Read Committed Isolation Level
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


30 Jan 2004 00:22:34 -0500
Greg Stark <gsstark@mit.edu>
Re: 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!


Fri, 30 Jan 2004 12:47:54 +0100
"Jeroen T. Vermeulen" <jtv@xs4all.nl>
Re: Stupid question on Read Committed Isolation Level