Re: Quick question re: locks and triggers

From: Hugo Kornelis (hugo_at_pe_NO_rFact.in_SPAM_fo)
Date: 08/05/04 

Date: Thu, 05 Aug 2004 16:09:32 +0200

On Thu, 5 Aug 2004 06:17:22 -0700, Steve wrote:

>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

Hi Steve,

The lock is not held by the insert statement or by the trigger, but by the
transaction. The lock will only be released when the transaction is either
committed or rolled back. Since the execution of the trigger is always
part of the same transaction as the execution of the statement that fired
the trigger, the answer is: yes, the lock will be held until the trigger
has finished.

Best, Hugo 

-- 
(Remove _NO_ and _SPAM_ to get my e-mail address)

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: 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)
  • 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)