>>> Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> "Kevin Grittner" <Kevin(dot)Grittner(at)wicourts(dot)gov> writes:
>> Would it make any sense to roll back and generate a
>> SERIALIZATION_FAILURE?
>
> If that's what you want then you run the transaction in serializable
> mode. The point of doing it in READ COMMITTED mode is that you
> don't want such a failure.
Wait a minute -- there is not such guarantee in PostgreSQL when you
start using WITH UPDATE on SELECT statements in READ COMMITTED mode.
By starting two transactions in READ COMMITTED, and having each do two
SELECTs WITH UPDATE (in opposite order) I was able to generate this:
ERROR: deadlock detected
DETAIL: Process 4945 waits for ShareLock on transaction 20234373;
blocked by process 5185.
Process 5185 waits for ShareLock on transaction 20233798; blocked by
process 4945.
So, wouldn't it be better, if it's actually feasible to detect the
problem situation, to make this another situation where SELECT FOR
UPDATE can cause serialization failures? That would allow
applications to count on getting the rows in the requested order if
the query completes successfully. If existing documentation doesn't
already cover the possibility of serialization failures when using FOR
UPDATE, it should. If we need to document something around the issue
of this thread, that seems like the place to do it.
-Kevin