Re: computational model of transactions

Brian Selzer wrote:
> "Marshall" <marshall.spight_at_gmail.com> wrote in message
[...]
> > How about
> >
> > INSERT into Table (a) values (1) where not exists (select * from Table
> > where a = 1);
> >
> > in two separate concurrent transactions?
> >
>
> Two things.
>
> (1) You can get away without the select here, assuming that *a* is the key,
> one will fail with a primary key violation. If you can't live with the
> error, then (2) in SQL Server, you would issue
>
> INSERT into Table (a) values (1)
> where not exists (select * from Table WITH(UPDLOCK, HOLDLOCK) where a =
> 1);
>
> WITH(UPDLOCK, HOLDLOCK) applies an update range-lock, which permits other
> transactions to obtain a shared lock, but not the exclusive lock required to
> write. Only the transaction holding the update lock can obtain an exclusive
> lock. The range lock is necessary to block other transactions from
> inserting the row in the case that it doesn't exist.

The locking hints may be unnecessary -- one could just use SERIALIZABLE.
>
> Note that a serializable transaction isolation level is not sufficient in
> this case.

Au contraire, it is. Sometimes, one transaction may fail due to a deadlock depending on select/insert timing, sometimes both can complete, but in every case the db will be left in a consistent state.  Using UPDLOCK will make your transaction mixture execution SERIAL thus negatively impacting concurrecy. However, one may make a case for a purely serial execution (which would avoid deadlocks) depending on one's application requirements.

>It's possible for each select to obtain a shared range-lock on
> the same resource, but then neither can transition to an exclusive lock
> because it is blocked by the other, causing a deadlock.

Right.

>
> Still, I don't think that there need be variants of INSERT or DELETE
> statements.
>
> >
> > Marshall
> >
Received on Sun Aug 06 2006 - 17:24:26 CEST