Re: Apparent bug in transaction processing in serializable mode

=?ISO-8859-2?Q?Jan_Hlavat=FD?= <Jan(dot)Hlavaty(at)code(dot)cz> writes:
> On one connection, I perform select on record with for update clause.
> This is supposed to lock the record with update type lock against other
> concurrent updates.
> Then I update that record with new number. All this should do is upgrade
> update lock to exclusive one.

You seem to be operating from a number of incorrect assumptions about
how Postgres' concurrency model works. For starters, there's only one
kind of row lock, not two kinds. But more fundamentally, you are trying
to use serializable mode in a way that's really only appropriate for
read-committed mode. The idea of serializable mode is that a
transaction fails (and should be retried from the top) if it finds
itself wanting to update any concurrently-updated row. To do otherwise
would violate the very notion of serializability, namely that you don't
see any concurrently-made database changes. For effective use of
serializable mode, you must adopt an optimistic locking philosophy: you
don't bother with "select for update", you just try to make your updates
and see if they succeed or not.

You might find it useful to look through the slides from my talk on
"PostgreSQL Concurrency Issues" last summer at OSCON 2002 --- see
http://conferences.oreillynet.com/cs/os2002/view/e_sess/2681

regards, tom lane