update trigger allways gives deadlock in multi-user setup

Question

update trigger allways gives deadlock in multi-user setup

Leo Smulders

SSC Enthusiast

Points: 128
More actions
June 7, 2005 at 5:34 pm

#87744

Hi,
I've been bothered with a nasty deadlock, related to the mere use of an update trigger.
*** PROBLEM DESCRIPTION ***
Consider a table that has a tracking column [MODIFIED_DATE] on it:
create table link (
    recno int identity not null,
    recno1 int not null,
    recno2 int not null,
    objectno varchar(255),
    :
    :
    :
    modified_date datetime null constraint [df_link_modified_date] default (getdate())
)
[modified_date] is a column in which the 'date/time of last change' is recorded.
This LINK table has an update trigger on it that takes care of updating the modified_date column:
CREATE TRIGGER TR_UPD_LINK_AUT ON dbo.LINK
FOR UPDATE
AS
BEGIN
     IF @@ROWCOUNT = 0
         RETURN
     UPDATE LINK SET
         MODIFIED_DATE = GetDate()
         FROM INSERTED
         WHERE LINK.RECNO = INSERTED.RECNO
END
This works perfectly, in a single-user situation.
In multi-user situation, however, i frequently get deadlocks.

*** TEST SETUP ***
Here's some basic test setup that produces the deadlock:
-- step 1: insert records (preparative for update-test 1)
DECLARE @RECNO1 INT
SET @RECNO1 = 1
WHILE @RECNO1 <= 10000
BEGIN
    INSERT INTO LINK (RECNO1, RECNO2, RECNO3, OBJECTNO)
        VALUES (@RECNO1, 1, 900, '')
    SET @RECNO1 = @RECNO1 + 1
END
-- step 2: insert records (preparative for update-test 2)
DECLARE @RECNO1 INT
SET @RECNO1 = 1
WHILE @RECNO1 <= 10000
BEGIN
    INSERT INTO LINK (RECNO1, RECNO2, RECNO3, OBJECTNO)
        VALUES (@RECNO1, 2, 900, '')
    SET @RECNO1 = @RECNO1 + 1
END
-- step 3: update-test 1:
DECLARE @RECNO1 INT
SET @RECNO1 = 1
WHILE @RECNO1 <= 10000
BEGIN
    update link set objectno = recno where recno3 = 900 and recno1 = @recno1 and recno2 = 1
    SET @RECNO1 = @RECNO1 + 1
END
-- step 4: update-test 2:
DECLARE @RECNO1 INT
SET @RECNO1 = 1
WHILE @RECNO1 <= 10000
BEGIN
    update link set objectno = recno where recno3 = 900 and recno1 = @recno1 and recno2 = 2
    SET @RECNO1 = @RECNO1 + 1
END
The records updated are chosen such that the same record is never updated twice, neither in a single update-test,
nor for the two processes together.
Step 1 and step 2 can be done either one before the other or simultaneously, it does not matter.
Step 3 and step 4 should be done 'as simultaneously as possible' from for instance two sql query analyzer windows.

*** INVESTIGATIVE REMARKS/OBSERVATIONS ***
This test setup ALLWAY gives me a deadlock error. Sometimes at the first, sometimes at the x-hundredth,....,
sometimes at the 5300-th update.
Using trace flags 3604 and 1204, I was able to determine from the sql server error log, that the deadlock was mostly involved around an index i had on recno3. After removing that index, the deadlock would still appear.
No matter what I tried, the deadlock keeps occurring (although less frequently the more i strip from table and trigger code):
- removed all indexes that were mentioned in the wait-for graph
- removed the auto statistics, disabled their auto creation and auto updating.
- removed the code from the update trigger that actually updates the field.
Even with just this code in the update trigger
CREATE TRIGGER TR_UPD_LINK_AUT ON dbo.LINK
FOR UPDATE
AS
BEGIN
if (select trigger_nestlevel()) = 1
  return
if (select trigger_nestlevel()) <> 1
        return
END
deadlock will still occur. (i know i know, this is quite silly code, it doesn't do anything useful).
- Only the following really empty code body does make the deadlock (apparently/seemingly) go away:
CREATE TRIGGER TR_UPD_LINK_AUT ON dbo.LINK
FOR UPDATE
AS
    return
Of course this is totally useless for a trigger.
**** Finally ***
Can anybody shed some light on this issue?
Any help would be greatly appreciated.
Leo Smulders
Software developer

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Brian Selzer SSC-Addicted Points: 412 More actions · Answer 1

Why don't you try an instead of update trigger? Replace the MODIFIED_DATE with GETDATE() in the UPDATE statement. Since the instead of update eliminates one of the update statements, the deadlocks should disappear.

Brian
MCDBA, MCSE+I, Master CNE

Johan Bijnens SSC Guru Points: 135369 More actions · Answer 2

"...create table link (

recno int identity not null,

recno1 int not null,

recno2 int not null,

objectno varchar(255),

:

modified_date datetime null constraint [df_link_modified_date] default (getdate())

) ..."

Put an index on your column recno !

Maybe even the primary key (which creates an index automaticaly).

This way, your trigger no longer will have to perform a full table scan to acheve its purpose !

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

rickreinicke Grasshopper Points: 18 More actions · Answer 3

Seems like I have trouble with triggers when I try to update the table that the trigger is for. Especially when it is wrapped in a transaction. In your case the trigger is on UPDATE OF LINK and "in" the trigger you are trying to update LINK.

Leo Smulders SSC Enthusiast Points: 128 More actions · Answer 4

rick, maybe you have problem of trigger recursion? if so you can prevent it by either turning it of on database

alter database yourdatabase set recursive_triggers off

or in trigger code:

if (select trigger_nestlevel()) > 1

return