Should an INSERT cause an exclusive lock on a foreign key?

Question

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

Answer 1

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:

• Bad habits : Putting NOLOCK everywhere

Answer 2

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.