I have a deadlock graph from a deadlock where one process is doing a SELECT and one is doing an UPDATE. This seems like the classic case where the SELECT gets a NCI lock to perform a join and then a CI lock to retrieve all the data by lookup. And the UPDATE is using the CI lock to perform an update and then needs to lock an NCI because the update results in a status change and the NCI facilitates finding items by status.
The problem is that one of the locks the UPDATE wants is NOT on the table it is updating and I can't find why this is happening.
Here's the SELECT:
SELECT *,
RIGHT(c.CC_NUMBER, 4) AS CC_LAST_4,
DATEDIFF(ss, '1970-01-01', plan_started ) plan_started_epoch,
DATEDIFF(ss, '1970-01-01', plan_expires ) plan_expires_epoch
FROM customers c, accounts a, parent_cos pc, htt_customers_overlay_ultra u
WHERE c.customer_id = a.customer_id
AND u.customer_id = c.customer_id
AND a.cos_id=pc.cos_id
AND u.customer_id = 9300;
Here's the UPDATE:
UPDATE htt_customers_overlay_ultra SET plan_state = 'Active' WHERE customer_id = 9300;
But according to the deadlock graph, the UPDATE is acquiring a lock on ACCOUNTS.ACCOUNT0, which is the PK (CI) of the ACCOUNTS table. There are no foreign keys in the overlay table. There are some default constraints which I don't currently have permission to see.
I've looked at the deadlock graph in SSMS and in SQL Sentry Plan Explorer Pro and am none the wiser.
Here are the execution plans:
I'd like to find out why it is getting this lock, and then the best way to serialize these calls.
Things I am aware of which I have already advised the client which have bearing on the locks taken, but don't explain the seeming unrelated lock which is arising:
Remove * and identify the columns needed and alter the NCIs to become covering - this would potentially make the SELECT use fewer locks
Determine why the system is SELECTing the same data that another process is processing - this would potentially mitigate these two processes running at the same time at all
There is a table scan in the SELECT
X
key lock on "dbo.ACCOUNTS" from an earlier statement in the same transaction not shown in the graph?SELECT
has aU
lock on the PK index ofhtt_customers_overlay_ultra
-- why? That process has 0 log used.process589f948
) has aU
lock and is trying to convert it to anX
lock but is blocked by anS
lock held by theSELECT
(process5240988
)read_committed_snapshot
for db.