Re: computational model of transactions

"Marshall" <marshall.spight_at_gmail.com> wrote in message news:1154846237.701348.259970_at_h48g2000cwc.googlegroups.com...
> Brian Selzer wrote:
>>
>> A compiler can tell the difference between x = 10 and x = x + 5, why
>> can't a
>> dbms?
>
> A fair question.
>
> It seems to me easy enough to tell if an UPDATE statement references
> any attributes on the right side of the equals sign.
>
>
>> The system should be able to detect whether or not the new value depends
>> on
>> the previous value. The first UPDATE statement above does not, the
>> second
>> does.
>>
>> As an aside, it is not really necessary that the system detect this: but
>> the
>> developer must, because in a concurrent environment the difference in the
>> semantics of replacement and modification has ramifications that can
>> affect
>> the appropriate choice of transaction isolation level.
>>
>> > While we're talking manipulations: what about
>> > INSERT and DELETE? Are there variants of
>> > those, too? Are those supposed to be handled
>> > differently in transaction context?
>> >
>>
>> I haven't given this much attention, but at first glance, no, I don't
>> think
>> so.
>
> 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.

Note that a serializable transaction isolation level is not sufficient in this case. 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.

Still, I don't think that there need be variants of INSERT or DELETE statements.

>
> Marshall
>
Received on Sun Aug 06 2006 - 10:31:43 CEST