Re: Quick question re: locks and triggers

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

Date: Thu, 5 Aug 2004 11:08:17 -0500

Yeah, without an exclusive lock held during the trigger, if you decided to
rollback your transaction, you might have to wait for another user to
release their locks on your object before you could roll back. Clearly not
what would be desired.

> > ie: is it therefore impossible for another process to
> > insert until the trigger has finished it's work ??

I think the question here is slightly different that the other question
asked. The exclusive lock is only on the row being inserted, not the entire
table. If you are seeing table locks when you do updates, inserts, or
delete, then you are having problems with indexing, in that the entire table
had to be searched for some criteria. Shouldn't happen on an insert without
a trigger or check constraints involving UDF's that access tables.

So the exclusive row (or very seldomly page) lock is seldom a problem being
held for the length of the transaction in terms of blocking other WRITERS to
the table. Any other users trying to read that row in a SELECT statement
are going to have to cool their jets until the trigger(s) have completed,
and all transactions (not just the automatic implicit one caused by the
modification statement) have been ended and @@trancount = 0 

-- 
----------------------------------------------------------------------------
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 :)
"Tibor Karaszi" <tibor_please.no.email_karaszi@hotmail.nomail.com> wrote in
message news:uVaFXJveEHA.3792@TK2MSFTNGP09.phx.gbl...
> Yes, the trigger will hold the lock. It would be very difficult for MS to
implement rollback functionality in
> a trigger if it didn't...
>
> -- 
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
>
> "Steve" <anonymous@discussions.microsoft.com> wrote in message
news:09de01c47aee$8afa6f90$a601280a@phx.gbl...
> > Hi all,
> >
> > Just learning about triggers and have a quick question.
> > An insert statement holds an exclusive lock whilst the new
> > row is written. What happens to the lock when an AFTER
> > INSERT trigger is involved ?? Does the trigger hold the
> > exclusive lock until it has terminated and the result
> > returned to the client ??
> >
> > ie: is it therefore impossible for another process to
> > insert until the trigger has finished it's work ??
> >
> > thanks
> > Steve
> >
>
>

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: computational model of transactions
    ... It seems to me easy enough to tell if an UPDATE statement references ... the appropriate choice of transaction isolation level. ... transactions to obtain a shared lock, but not the exclusive lock required to ...
    (comp.databases.theory)
  • 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: Quick question re: locks and triggers
    ... TableX has insert trigger TableXInsertTrigger that includes the following ... It might be that it does not take a table lock, ... or at least use holdlock hints on the critical ...
    (microsoft.public.sqlserver.programming)
  • 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)