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