Re: Theoretical Basis for SELECT FOR UPDATE

From: vc <>
Date: 30 Sep 2005 05:36:39 -0700
Message-ID: <>

Tony Andrews wrote:
> 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.

The usual technique is to use an additional column containing a timestamp so that one did not need to check all the column values. Besides, the timestamp can be used to prevent updates during some resonable period, like a couple of second. Clearly, this kind of locking is advisory in nature.

> 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.

This statement is unclear. Please elaborate on "integrity to be maintained at statement boundaries". The only way for a set of transactions to behave in a consistent way is to be SERAILIZABLE (not to be confused with serial execution). Received on Fri Sep 30 2005 - 14:36:39 CEST

Original text of this message