Re: Theoretical Basis for SELECT FOR UPDATE

From: Tony Andrews <>
Date: 30 Sep 2005 04:42:21 -0700
Message-ID: <>

Marshall Spight wrote:
> I am interested to hear people's opinions on the validity
> of SELECT FOR UPDATE. It is not something that I've ever
> had to use, or even felt the need for, but I don't confuse
> that fact with any kind of proof or disproof of the technique's
> essentiality.
> Also, if I understand correctly, TTM doesn't like it at all,
> either. It appears "RM Proscription 7" forbids the technique.
> I give a fair bit of weight to TTM, but I don't necessarily
> agree with everything it says, nor do I believe that the
> solutions it proposes to problems are the only or even
> the best solutions. In this case, they simply disallow it,
> but don't provide any alternative, which suggests that they
> feel it is simply unnecessary.
> I am interested to hear people's experiences, and especially
> any insight into the "true nature" of cursors and/or select
> for update, and whether it is in fact necessary.

I would say that SELECT FOR UPDATE is a valid facility in a DBMS that supports multi-statement transactions, even when cursors are not being used. It provides a means of locking data that was selected earlier in the transaction so that subsequent operations can use the selected data without risking violation of ACID properties (e.g. because another user deleted that row).

It is not necessarily the only way - for example we could perhaps add "AND EXISTS (SELECT ...)" to the WHERE clause of each subsequent operation to ensure that the data is still there and has not been changed - i.e. optimistic rather than pessimistic locking.

I believe the TTM is against transactions also, requiring integrity to be maintained at statement boundaries. If that were so, there would be no requirement for SELECT FOR UPDATE. Received on Fri Sep 30 2005 - 13:42:21 CEST

Original text of this message