January 13, 2011 at 4:18 pm
I am trying to pinpoint the cause of a deadlocking issue in one of our tables' triggers.
DISCLAIMER: I didn't write the code and it's not something that can quickly be changed
So the situation arose out of an off-hours job that runs a bunch of updates to this one particular table. The developers told me it's multi-threaded so I think this is why I'm seeing this most from this one job and not so much in typical day-to-day production (although it does occasionally pop up).
The table is updated and an update trigger is fired. The update trigger does two things: 1) fires off an update process in a loop for each record being updated via a stored procedure (i'll call it TrProcess1) and 2) fires off a second procedure to update some other data in a different table (i'll call it TrProcess2)
Based on the deadlock output's process list and resource list what looks like is happening is that in SPID1 TrProcess1 is updating a bunch of unrelated tables then calling another procedure that updates a bunch of unrelated tables and then fires a single update to one of the tables that we always see is part of the deadlock (TDeadlock1). It bounces out of TrProcess1 fine and then goes into TrProcess2. TrProcess2 is trying to do an update to an unrelated table but that update joins with the original table that the trigger belongs to and it's locked by another process SPID2.
SPID2 is following a similar path but doesn't seem to get as far along before the deadlock occurs. It looks like SPID2 goes into the update trigger, fires the first process TrProcess1 and then gets deadlocked because table TDeadlock1 is locked by SPID1. This doesn't make any sense because SPID1 should be done with it by now and it's the only place in the whole sequence of queries in the trigger that updates that table.
The locks being taken out are X key locks on the clustered indexes.
I guess my two questions are
1) Shouldn't the X lock on TDeadlock1 be released as soon as the update has completed?
2) Is the order of execution in the trigger different?
I was thinking of either putting a NOLOCK hint on the update to the unrelated table that joins with the original table so a share lock isn't taken out or maybe using a ROWLOCK hint on the update to TDeadlock1.
I'd post the code but it's really way too long to feasibly post here so I tried to be as descriptive as possible.
Let me know what other information would be necessary to troubleshoot this issue.
January 14, 2011 at 7:38 am
1) Nope, the locks are usually held for the duration of the transaction in case of rollback. If you've got a cursor in a trigger (oy), then the transaction is probably sitting on the resources for quite a while.
2) NOLOCK is not going to help locking on UPDATE, INSERT or DELETE. These things must lock in order to perform the actions. You could look at READ COMMITTED SNAPSHOT isolation as a mechanism to reduce blocking while the inserts are occurring, but if the order of required locks on these resources is different, you're still likely to get the deadlock.
I can't tell you if the order is different without seeing the code, but it sounds like it might be.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply