Sql-server – Deadlock graph with a lock on an index on a seemingly unrelated table

deadlocklockingsql server

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

Best Answer

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.

Related Solutions

Sql-server – SQL Server Deadlock on two updates due to index lock order

The optimal index for those two queries is not far from the existing definition of the IK_HTT_ACTION_LOG_1 index (add ACTION_UUID as an INCLUDE to the improved index below):

CREATE INDEX nc1
ON dbo.HTT_ACTION_LOG
(
    TRANSITION_UUID,
    STATUS,
    ACTION_SEQ
);

The first query is:

UPDATE dbo.HTT_ACTION_LOG
SET [STATUS] = 'ABORTED', 
    CLOSED = GETUTCDATE()
WHERE
    TRANSITION_UUID = '{F53ADDDA-E46B-4726-66D8-D7B640B66597}'
    AND [STATUS] = 'OPEN';

Giving the following execution plan:

Update 1

The second query can be expressed this way:

UPDATE ToUpdate 
SET [STATUS] = 'RUNNING {36082BCD-EB52-4358-E3D3-4D96FD5B9F0F} 1360094342'
FROM
(
    SELECT TOP (1)
        hal.[STATUS]
    FROM dbo.HTT_ACTION_LOG AS hal
    WHERE
        hal.transition_uuid = '{F53ADDDA-E46B-4726-66D8-D7B640B66597}'
        AND hal.[STATUS] = 'OPEN'
    ORDER BY
        hal.ACTION_SEQ ASC
) AS ToUpdate;

Giving this execution plan:

Update 2

Both queries now access the same resources in the same order, while locking many fewer rows on the read side of the plan. The execution engine will automatically take UPDLOCKs when reading the new index, providing the serialization you are looking for.

Sql-server – SQL Server Index Update Deadlock

Is there a way to prevent the deadlock while maintaining the same queries?

The deadlock graph shows that this particular deadlock was a conversion deadlock associated with a bookmark lookup (an RID lookup in this case):

Deadlock graph

As the question notes, the general deadlock risk arises because the queries may obtain incompatible locks on the same resources in different orders. The SELECT query needs to access the index before the table due to the RID lookup, whereas the UPDATE query modifies the table first, then the index.

Eliminating the deadlock requires removing one of the deadlock ingredients. The following are the main options:

Avoid the RID lookup by making the nonclustered index covering. This is probably not practical in your case because the SELECT query returns 26 columns.
Avoid the RID lookup by creating a clustered index. This would involve creating a clustered index on the column Proposal. This is worth considering, though it appears this column is of type uniqueidentifier, which may or may not be a good choice for a clustered index, depending on broader issues.
Avoid taking shared locks when reading by enabling the READ_COMMITTED_SNAPSHOT or SNAPSHOT database options. This would require careful testing, especially with respect to any designed-in blocking behaviours. Trigger code would also require testing to ensure the logic performs correctly.
Avoid taking shared locks when reading by using the READ UNCOMMITTED isolation level for the SELECT query. All the usual caveats apply.
Avoid concurrent execution of the two queries in question by using an exclusive application lock (see sp_getapplock).
Use table lock hints to avoid concurrency. This is a bigger hammer than option 5, as it may affect other queries, not just the two identified in the question.

Can I somehow take an X-Lock on the index in the update transaction before the update to ensure the table and index access are in the same order

You can try this, by wrapping the update in an explicit transaction and performing a SELECT with an XLOCK hint on the nonclustered index value before the update. This relies on you knowing for certain what the current value in the nonclustered index is, getting the execution plan right, and correctly anticipating all the side-effects of taking this extra lock. It also relies on the locking engine not being smart enough to avoid taking the lock if it is judged to be redundant.

In short, while this is feasible in principle, I do not recommend it. It is too easy to miss something, or to outsmart oneself in creative ways. If you really must avoid these deadlocks (rather than just detecting them and retrying), I would encourage you to look instead at the more general solutions listed above.

Best Answer

Related Solutions

Sql-server – SQL Server Deadlock on two updates due to index lock order

Sql-server – SQL Server Index Update Deadlock

Related Question