Sql-server – Should an INSERT cause an exclusive lock on a foreign key

deadlockforeign keylockingsql server

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

Best Answer

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:

Related Question