Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> comp.databases.theory -> Re: Theoretical Basis for SELECT FOR UPDATE

Re: Theoretical Basis for SELECT FOR UPDATE

From: vc <boston103_at_hotmail.com>
Date: 30 Sep 2005 05:36:39 -0700
Message-ID: <1128083799.008542.251310@g49g2000cwa.googlegroups.com>


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 - 07:36:39 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US