Re: Question: update and transaction isolation

Peter Eisentraut <peter_e(at)gmx(dot)net> writes:
> Tom Lane writes:
>> The reason it works in read-committed mode is that the second guy to
>> arrive at the row will observe that the row has an update in progress;
>> will block waiting for the previous updater to commit or abort; and if
>> commit, will use the updated version of the row as the starting point
>> for his update. (This is what the EvalPlanQual ugliness in the executor
>> is all about.)

> Isn't that a violation of the principle that transactions in read
> committed mode will look at the data that was committed *before* the
> statement had begun?

Hey, I didn't design it. Complain to Vadim ...

But actually, SELECT FOR UPDATE also violates the principle you allege,
and must do so if it's to be useful at all. The results you get are
whatever's in the row after it's been locked, not what was in the row
at the instant of statement start. UPDATE is essentially behaving in
the same way.

To my mind, full SERIALIZABLE mode is the only approach that can be
explained in terms of simple notions like "you see only the data that
existed at time T". Read-committed mode is conceptually much dirtier,
even though it's often simpler to use in practice.

regards, tom lane