Re: computational model of transactions

"vc" <boston103_at_hotmail.com> wrote in message news:1154877934.479609.82870_at_i3g2000cwc.googlegroups.com...
>
> 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.
>

True. But the point of (2) was to avoid the error, and it seems unproductive to prevent one type of error while at the same time introducing the possibility of another.

>>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:50:19 CEST