Re: Theoretical Basis for SELECT FOR UPDATE

From: David Cressey <>
Date: Fri, 30 Sep 2005 19:24:10 GMT
Message-ID: <uhg%e.6672$>

[Quoted] "Marshall Spight" <> wrote in message
> Hi all,
> 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.

Here is what I think the purpose of SELECT FOR UPDATE might be.

In an ordinary SELECT, you want a shared read lock on the rows you've touched. This prevents another concurrent transaction from updating the rows, but doesn't prevent another concurrent transaction from reading the same rows.

When you go to UPDATE a given row, you now have to upgrade your lock from a shared read lock to an exclusive write lock. If another transaction already has a read lock, you are now blocked. Two transactions, both doing updates in the context of a stream of SELECTed rows, could easily deadlock.

By SELECT FOR UPDATE, you take out the higher level lock earlier. This reduces concurrency, but also reduces deadlocks.

Anyway, that's what I THINK the theory is about.

On a tangential subject, I always liked DEC Rdb's RESERVING clause in the SET TRANSACTION. This allowed a transaction to patiently wait for the resources it needs at transaction start time, before it already owns a lot of locks. It's not perfect, but I got a lot better freedom from locking problems when I used it than when I didn't.

AFAIK, the RESERVING clause is entirely an extension of SQL peculiar to DEC (until 1994).

> 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 think a good case can be made for cursors being unnecessary. But I think that, given current programming languages and current DBMS platforms, it's premature to prohibit them. Received on Fri Sep 30 2005 - 21:24:10 CEST

Original text of this message