Re: Quick question re: locks and triggers

From: Louis Davidson (dr_dontspamme_sql_at_hotmail.com)
Date: 08/06/04 

Date: Fri, 6 Aug 2004 10:16:59 -0500

Ok, I know what your concern is because it drove me batty for a long while.
The problem is what is locked, and for how long. Consider this scenario:

TableX has insert trigger TableXInsertTrigger that includes the following
statement:

if (select sum(value) from TableX) > 10
 begin
    raiserror 50000 'No more than 10'
    rollback tran
 end
A row is inserted. This new row is created, but is exclusively locked.

TableXInsertTrigger fires

The statement above fires, doing a table scan and locking the entire table.
However since you are in READ COMMITTED mode, locks should not be held from
this select statement, as such they will be released as soon as they are
used. It might be that it does not take a table lock, but rather a row lock
per row, one at a time reading the value for the aggregation, and then
releasing it and taking another.

So you do not end up with an exclusive table lock, just an exclusive row
lock, with a transitory shared lock on the table.

Now, notice one thing. This trigger would prevent other users from
inserting a row into the TableX table, since it has a row lock, no other
user can take even a shared table lock. This is why you have to be really
careful when building triggers.

This is why we have to keep our transactions very very short when using
triggers and READ COMMITTED isolation level. The only (important to this
discussion) locks that held after they are used are EXCLUSIVE locks. So
lets say you had the following statements in the trigger instead of the
other:

if (select sum(value) from TableY) > 10
 begin
    raiserror 50000 'No more than 10'
    rollback tran
 end

Now, we are checking another table, and after we do the check we hold no
locks on that table. Any other user can change the values in TableY without
any knowledge that you need the value to be greater than 10, since you will
hold no locks. This same phenomena is true for FOREIGN KEY constraints.
The minimum timing and likelihood of this sort of thing happening make it
very very unlikely, which is why we use READ COMMITTED as our default. If
your system is life or death, then you will want to step up the isolation
level to SERIALIZABLE, or at least use holdlock hints on the critical
tables.

Concurrency is FUN! 

-- 
----------------------------------------------------------------------------
Louis Davidson (drsql@hotmail.com)
Compass Technology Management
Pro SQL Server 2000 Database Design
http://www.apress.com/book/bookDisplay.html?bID=266
Note: Please reply to the newsgroups only unless you are
interested in consulting services.  All other replies will be ignored :)
"Steve" <anonymous@discussions.microsoft.com> wrote in message
news:125501c47b8a$223db830$a501280a@phx.gbl...
> Thankyou all for your responses.
>
> Can I please just confirm the following then...
> 1) I start a transaction at default readcommitted isolation
> 2) I do an insert into myTable with no locking hints
> 3) the newly inserted row is exclusively locked
> 4) trigger starts it's work, holding the lock on that row.
>
> Now whilst the trigger is running, it is still possible
> for another user to insert a new row into myTable.
> Correct ??
>
> I am thinking of the scenario whereby the insert trigger
> queries myTable itself in order to determine if the new
> row is acceptable. As such there must not be any new rows
> added during that query or the trigger might end up
> allowing an invalid row to be added. For instance if the
> trigger was checking for duplicate names in myTable.
> (could be done with a unique property on a column but just
> an example). If 2 users both did an insert at almost the
> same instant, the second insert must wait for the 1st to
> finish in order to include the (possibly) newly inserted
> row in it's own trigger query.
>
> I'm guessing that you would need to insert with a tablock
> hint to achieve this ?? ie suspend any writes to myTable
> until the insert completes.
>
> thanks again
> Steve
>

Relevant Pages

  • Re: Trigger blocks our AS400 applications
    ... indeed, when I take a record in lock for update, ... another session can read the record. ... SECOND TIME with a lock for update when the trigger is active ??? ...
    (comp.sys.ibm.as400.misc)
  • Re: Trigger blocks our AS400 applications
    ... indeed, when I take a record in lock for update, ... another session can read the record. ... SECOND TIME with a lock for update when the trigger is active ??? ...
    (comp.sys.ibm.as400.misc)
  • Re: Trigger refinement for a Springfield 1884 45-70 trapdoor
    ... The problem is not the trigger. ... The sear holds the tumbler and hammer till you press the ... If you take the lock out of the gun and cock the hammer. ... at the notch in the tumbler with the sear holding it. ...
    (rec.guns)
  • Re: NFS Locking Issue
    ... to am-utils running into some race condition the other problem is related to throughput, freebsd is slower than linux, and while freebsd/nfs/tcp is faster on Freebsd than udp, on linux it's the same. ... If you can help to produce simple test cases to reproduce the bugs you're seeing, ... First, architectural issues, some derived from architectural problems in the NLM protocol: for example, assumptions that there can be a clean mapping of process lock owners to locks, which fall down as locks are properties of file descriptors that can be inheritted. ... Once you've established whether it can be reproduced with a single client, you have to track down the behavior that triggers it -- normally, this is done by attempting to narrow down the specific program or sequence of events that causes the bug to trigger, removing things one at a time to see what causes the problem to disappear. ...
    (freebsd-stable)
  • 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)
Loading