Blocking lock on INSERT trigger

Posted on 2005-03-04
Medium Priority
Last Modified: 2008-02-07

Within a FOR INSERT trigger, I would like to know how to perform an UPDATE on the row which is being inserted without causing a blocking lock to occur.

1) INSERT statement locks table/row
2) trigger fires
3) UPDATE statement in trigger waits for lock to be realeased to perform update on row which inserted
4) block!

Anyone know how I can make the update possible in the trigger without the block?

Thanks in advance
Question by:batdan
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
LVL 43

Accepted Solution

Eugene Z earned 750 total points
ID: 13458817
try to use Hint  'Nolock' (but read about it first in BOL)


But - check if you have indexes in the table - and what indexes

Author Comment

ID: 13459077
Thanks for the link, makes interesting reading.  Unfortunaltely I had mistaken the cause of the block and simplified the example I gave (the code above atually works).  

The trigger actually call a DTS package with :

xp_cmdshell 'DTSRUN ....'

which is where things are being blocked.  I'll get back to you with further info asap, but will award points if I manage to fix it meantime.

LVL 69

Expert Comment

by:Scott Pletcher
ID: 13460467
SQL "knows" the trigger is part of the same transaction that did the INSERT and so will not allow the INSERT and UPDATE to block each other.  

Author Comment

ID: 13474733
Good point, but it seems that the DTS has no idea it's part of the transaction and therefore goes throwing locks around like there's no tomorrow when it performs a select on the trigger table. This is where my problem's coming from.

The problem should be adequately solved by using EugeneZ's link (NOLOCK), or indeed by separating out some SQL from the DTS into the trigger and passing global variables into the command line DTS call instead, seeing as this scenario would be "managed" by SQL Server.

Thanks for the responses.


Featured Post

Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question