=?ISO-8859-1?Q?St=E9phane_Cazeaux?= <[EMAIL PROTECTED]> 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 ---------------------------(end of broadcast)--------------------------- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html