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
