Re: Question about conccurrency control and Insert

=?ISO-8859-1?Q?St=E9phane_Cazeaux?= <stephane(dot)cazeaux(at)netcentrex(dot)net> writes:
> I'm ok about this, but, if I try exactly the same scenario, where I
> replace the INSERT by a DELETE, the first SELECT of the client 2 won't
> return any row. This is the same behaviour with an UPDATE. If client 1
> updates the row and commits, the first SELECT of client 2 will return
> the updated row. Why isn't it the same behaviour with INSERT ?

A row inserted after your statement starts is not visible, period.

When SELECT FOR UPDATE encounters a row that would be visible to a
normal select (ie, it existed at the start of the statement), then
it tries to acquire a row lock on that row. A byproduct of that is
waiting for any other transaction that had already locked the row.
When the other transaction is gone, then what you get back is the
*latest version* of the row (or no row, if the other guy deleted it).
This is a special exception to the ordinary row visibility rules,
which is made on the grounds that if you SELECT FOR UPDATE you had
better get back the latest data, else you might update the row
incorrectly. (Consider for example that the row represents a bank
account, and the different transactions are adding or subtracting
amounts in the balance field. You'd better add to the latest balance
even if it shouldn't theoretically be visible to you.)

Another way of thinking about it is that if you SELECT FOR UPDATE
a particular row, and then look at it again later in the same
transaction, you're guaranteed to see the same data (other than any
changes you made yourself of course). Ordinary SELECTs in read
committed mode don't act that way.

This is all documented btw: see second para in
http://www.postgresql.org/docs/7.3/static/transaction-iso.html#XACT-READ-COMMITTED

regards, tom lane