5

I'm working on a deadlock issue.

Process A is doing a simple INSERT into TableA which has a FK to TableB

Process B is doing a complicated SELECT on a join that includes TableA and TableB

I'll include the trace info below, but basically what I think is happening is that the insert to TableA is causing an Exclusive Lock (X) on the Primany INDEX of TableB due to TableA having a FK to TableB. We do have referential integrity enabled for that FK, but there is no update to TableB needed by doing an insert into TableA, so it seems odd to me that an exclusive lock is required just to check for the existence of the FK value.

Is this expected behavior? If so, is there anything I can do to mitigate this? I honestly wasn't expecting such a basic/vanilla insert to cause a deadlock.

Also, it's not my real question, but if you happen to know what "subresources=FULL" means I'd be interested to know.

EDIT: Just to be clear about the deadlock:

processInserting is inserting to TableA and has an X lock on the Primary Index on TableB (the foreign key for TableA). ProcessSelecting is waiting for a RangeS-S lock on this index.

processSelecting is selecting from a join of many tables including TableA and TableB and has a S lock on TableA (Becuase it's joining on it). ProcessInserting is waiting for an IX lock on this table.

EDIT 2: Giving some more details. The "select" query I was calling processSelecting is an extremely torturous query that uses a torturous view as part of the join, so it's kind of a mess to look at.

This is the DDL for the RoutePlan (TableA) and Form (TableB) tables.

http://pastebin.com/gWftciEG

RoutePlan has a trigger on it to log historical changes:

http://pastebin.com/WjvNxPFK

The SP doing the INSERT into Routeplan is:

http://pastebin.com/c6JqNFaX

The SP doing the SELECT is:

http://pastebin.com/micNhuPf

And the View CurrentAndPriorApprovers_View is defined as

http://pastebin.com/hHSzNJyc

And here is the full trace log info

http://pastebin.com/qenwhJcN

resource-list

keylock hobtid=72057594059882496 dbid=66 objectname=TableB indexname=PK_TableB id=lock204d08500 mode=X associatedObjectId=72057594059882496

owner-list
 owner id=processInserting mode=X

waiter-list
 waiter id=processSelecting mode=RangeS-S requestType=wait

objectlock lockPartition=0 objid=516196889 subresource=FULL dbid=66 objectname=TableA id=lock2044c5080 mode=S associatedObjectId=516196889

owner-list
 owner id=processSelecting mode=S
waiter-list
 waiter id=processInserting mode=IX requestType=convert
  • 2
    Can you post table structure, the .xdl file, and the queries that are deadlocking? What other queries are involved with either transaction? On their own, this should just lead to blocking, not deadlocking - one should wait for the other to complete, and then proceed, unless there is something else you're not telling us. – Aaron Bertrand Sep 25 '14 at 16:56
  • @AaronBertrand I added a bunch of pastebin files to show most of these details and I also included some description of how the deadlock is happening (how each are waiting on something from the other). The select query is truly painful. I don't have an XDL file, I just added the trace info and read the info out of the error log. I wasn't actively attached with the profiler, which I think is the only way to produce that file right? – chrismay Sep 25 '14 at 17:58
3

Here's my first recommendation to remove the deadlock (and this is a very common cause with the same resolution every time). Your RoutePlan_Save procedure currently has this logic:

IF EXISTS (SELECT ... WHERE key = @key)
  UPDATE ... WHERE key = @key;
ELSE
  INSERT(key) VALUES(@key);

So you actually have two queries here that require locks on the table, regardless of whether you are ultimately going to perform an insert or update. Stop doing that! What is the point of checking if a row exists, and then in a separate query, firing an update statement that again has to check if the row exists? This requires more resources, not less, than just trying to update the row not knowing whether it exists. So do this instead:

BEGIN TRANSACTION;
UPDATE ... WHERE key = @key;
IF @@ROWCOUNT = 0
BEGIN
  INSERT(key) VALUES(@key);
END
COMMIT TRANSACTION;

You can also isolate the other read-only processes by using a different isolation level that doesn't require them to wait for write operations to complete. NOLOCK / READ UNCOMMITTED is a popular one, but READ COMMITTED SNAPSHOT - assuming you can take the hit on tempdb - is way better IMHO. There are other alternatives, too. I happened to blog about this just yesterday:

  • Would this lead to the situation that caused our deadlock though? The lock happened on the insert statement, where it looked like it put an exclusive lock on the index of the foreign key on the table holding the foreign key. I assume this same lock would be required with either of these UPSERT operations because the INSERT is the same in both cases. And I agree that READ COMMITTED SNAPSHOTS would be preferred, but our DBA had said that we couldn't support READ COMMITTED SNAPSHOT because of the required IO. :( – chrismay Oct 1 '14 at 14:15
  • @chrismay Did he actually test it, or is this Chicken Little? – Aaron Bertrand Oct 1 '14 at 14:16
  • your comment was too fast for my edit! :) I hit enter for a new line and it submitted. I typed more stuff about the UPSERT that I'm very interested in. Can you look back at my last comment? Regarding the READ COMMITTED SNAPSHOT, I doubt he actually tested it, but we are getting new hardware shortly so he's put it off until then. – chrismay Oct 1 '14 at 14:20
  • I can't predict exactly what will happen without the tedious task of trying to reconstruct your exact table structure, data, and the statements that led to the deadlock. Could you meet me halfway and try this more logical approach, and see if you still get deadlocks? Can it hurt to try it? – Aaron Bertrand Oct 1 '14 at 14:23
  • Yea we will try this. We have tons of these types of UPSERT statements that were code generated so we'll look at changing them over thanks. – chrismay Oct 1 '14 at 15:39

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.