Triggers causing deadlocks

Question

Post reply

Triggers causing deadlocks

msilkett

SSC Enthusiast

Points: 164
More actions
December 1, 2003 at 3:02 pm

#65581

Has anyone ever encountered problems with a trigger causing deadlocks? I have many tables that have triggers that simply add a modified date and modified by user to a row. One of these tables creates deadlocks when the trigger fires. I have traced the sql of the processes involved in the deadlock and I know it's the trigger causing the problem because of the inserted table being used in the sql. Any ideas?

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

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

ray_higdon SSCarpal Tunnel Points: 4589 More actions · Answer 1

It's probably non-optimal code in the trigger, you might want to post the trigger code for suggestions.

Ray Higdon MCSE, MCDBA, CCNA

------------

Ray Higdon MCSE, MCDBA, CCNA

msilkett SSC Enthusiast Points: 164 More actions · Answer 2

The trigger is pretty straight-forward. The table is for an OLTP app that saves some user settings. When I remove the trigger, the deadlocks stop. Here is the code:

CREATE Trigger modtr_resource_personalization

ON resource_personalization

FOR insert, update

AS

Update resource_personalization

Set last_mod_dt = getdate(),

modified_by = SESSION_USER

From inserted i

WHERE resource_personalization.resource_name = i.resource_name

And resource_personalization.option_name = i.option_name

And resource_personalization.id = i.id

The update that it is colliding with is very simple too:

UPDATE resource_personalization SET value = @P0 WHERE id=@P1 AND option_name='defaultPage' AND resource_name = 'web_app'

Thanks for the help.

simon_hyan Ten Centuries Points: 1177 More actions · Answer 3

Thanks for the good question.

2 solutions:

1> Remove the trigger.

Alter the query as

Update UPDATE resource_personalization

SET value = @P0,

last_mod_dt = getdate(),

modified_by = SESSION_USER

WHERE id=@P1 AND

option_name='defaultPage' AND

resource_name = 'web_app'

2>Keep the trigger

Create an other table with id, option_name and resource_name

Remove option_name, resource_name from resource_personalization table

Alter trigger to fire on the new table

BTW, I did a test, but didn't find any deadlock problem.

Edited by - simon_hyan on 12/02/2003 12:00:32 PM

Alamir SSCertifiable Points: 6085 More actions · Answer 4

please take care of this :

open enterprise manager , right lick on your server and choose "properties" , go "server settings" tab, and UNcheck "allow triggers to be fired which fire other triggers ( nested triggers )"

because this may lead to deadlock ...

I hope this help u

Alamir Mohamed

Alamir_mohamed@yahoo.com

Alamir Mohamed
Alamir_mohamed@yahoo.com