Re: computational model of transactions


"Marshall" <marshall.spight@xxxxxxxxx> wrote in message
news:1154846237.701348.259970@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
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



.

Relevant Pages

  • Re: Tansaction/locking MSSQL2000
    ... >transaction behavior in single statements [not a BEGIN TRANSACTION ... >tables I need to lock so there is not a race later]? ... If the table is already held by an exclusive lock ... When the transaction is finished (due to ROLLBACK/COMMIT, releasing the ...
    (microsoft.public.sqlserver.programming)
  • Re: Tansaction/locking MSSQL2000
    ... Are you saying that ALL select statements acquire a shared TABLE lock on ALL ... with the SET outcome after the SELECT transaction begins. ... > If the table is already held by an exclusive lock ... > When the transaction is finished (due to ROLLBACK/COMMIT, releasing the ...
    (microsoft.public.sqlserver.programming)
  • Re: Quick question re: locks and triggers
    ... Yeah, without an exclusive lock held during the trigger, if you decided to ... held for the length of the transaction in terms of blocking other WRITERS to ...
    (microsoft.public.sqlserver.programming)
  • Re: Quick question re: locks and triggers
    ... >An insert statement holds an exclusive lock whilst the new ... What happens to the lock when an AFTER ... >INSERT trigger is involved ?? ... The lock will only be released when the transaction is either ...
    (microsoft.public.sqlserver.programming)
  • Re: Working Transactions somehow started not to work
    ... Transaction and locking are not properties of recordsets but are properties ... Opening a served-based cursor will put a lock on ... If a connection is closed, ... SL> and are not associated necessarily with server-based cursors. ...
    (microsoft.public.access.adp.sqlserver)