Re: Theoretical Basis for SELECT FOR UPDATE

Misha Dorman wrote:
> vc wrote:
> > SELECT FOR UPDATE is not needed for databases with a locking
> > concurrency scheduler (SQL Sever, etc) and consequently not
> > implemented there.

There are important differences between SELECT FOR UPDATE and the UPDLOCK hint.

1. Neither is required for the locking database, because such database can abort a non-serializble transaction history through a deadlock (assuming the SERIALIZED i.l.). A typical MV database, like Oracle, cannot abort some non-serializable histories and the way to eliminate such histories would be through the use of the S.F.U.
2. A typical locking database has a slightly relaxed i.l. (in comparison to SERIALIZABLE), REPEATABLE READS, which is implemented without needing any hints. Oracle, for example, does not lock any rows during a select operation and does not have the RR i.l, so the S.F.U allows to emulate RR .

>
> So why does SQL Server (6.5 up) provide
> SELECT .. FROM tablename (UPDLOCK)
> ?
>
> It is (as someone else pointed out) useful to avoid some forms of
> deadlock (due to the promotion of S to X locks).

It would be me ;) Again, strictly speaking, the UPDLOCK is not required because a locking database ensures consistent results without it, and it's up to the programmer to decide whether retrying a failed transaction is better or worse in comparison to making conflicting transactions run serially. Received on Thu Oct 06 2005 - 18:39:46 CDT