Deadlock graph with a lock on an index on a seemingly unrelated table

Question

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:

For the SELECT

For the UPDATE

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

Answer 1

The UPDATE query has an X lock on a key on "dbo.ACCOUNTS" blocking the SELECT from getting an S lock.

The SELECT query has an S lock on a key of htt_customers_overlay_ultra. The UPDATE query has a U lock on the same key and is blocked trying to convert that to an X lock.

The execution plan for the UPDATE doesn't feature Accounts at all so there is no obvious reason for it to have a key lock on Accounts. The Update transaction starts 0.01 seconds before the batch does. 2013-01-13 08:49:30.213 vs 2013-01-13 08:49:30.223.

Perhaps there was a preceding statement in a different batch (hence not shown in the deadlock graph) that actually started the transaction and acquired the mysterious X lock.