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.


RoutePlan has a trigger on it to log historical changes:


The SP doing the INSERT into Routeplan is:


The SP doing the SELECT is:


And the View CurrentAndPriorApprovers_View is defined as


And here is the full trace log info



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

 owner id=processInserting mode=X

 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 id=processSelecting mode=S
 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

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;
  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:

UPDATE ... WHERE key = @key;
  INSERT(key) VALUES(@key);

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:

| improve this answer | |
  • 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

the IF EXISTS (SELECT ... WHERE key = @key) is throwing a Shared lock "s" on table to read. It actually holding the shared lock for the transaction in the SP.

On insert the lock gets upgraded to "IX" intent exclusive.

The other Sp obtains a "RangeS-S" and needs to Upgrade this or get a new lock "s" . This then causes a deadlock since there is a pending "IX" which stops the "S" from being granted and the IX cannot be granted due to existing "RangeS-S".

if you remove the if exists and do as previous commentor mentions then the Initial "S" lock will not be done but a simple "IX" will be there which will wait for and "S" or "RangeS-S" to finish before being granted.

Also your Query to read the data you should validate if it makes sense to make it "Snapshot Isolation Read Uncommitted" as it's a basic report /search that goes back to some widget .. which means that the if the data being brought back to thew widget is not 100% accurate and never will be anyways. ex. If 1 Ms after the search the data changes does it matter from the perspective of that Widget/SP ? if the answer is nope than reading non committed data is OK and you do not need to have serialized. I'm also assuming you don't specify the dbconnection isolation level in the front end side and just use default . Default is Serialized which will cause issues.

| improve this answer | |

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.