AI Advice - blocking

This is the full AI Advice output from sp_BlitzCache's blocking/deadlocking personality, analyzing usp_DeadlockSide1. The statement analyzed was UPDATE DeadlockTest WITH (HOLDLOCK) SET Val = 1 WHERE ID = 1.

Summary

The procedure dbo.usp_DeadlockSide1 begins a transaction, updates ID=1 with WITH (HOLDLOCK), then does WAITFOR DELAY 5 seconds inside the transaction, then updates ID=2, then commits.

That pattern is a textbook recipe for blocking and a two-way deadlock when another session touches the same two rows in the opposite order (ie., updates ID=2 first, waits, then updates ID=1).

Primary issues: long-held locks due to WAITFOR inside a transaction, misuse of HOLDLOCK, and likely inconsistent lock ordering across callers.

Findings

1) Long-held locks because WAITFOR is inside the transaction

Evidence: BEGIN TRAN; UPDATE ... WHERE ID = 1; WAITFOR DELAY '00:00:05'; UPDATE ... WHERE ID = 2; COMMIT.

Contention risk: The exclusive lock taken to update ID=1 is held for the duration of the transaction. The WAITFOR holds that lock an extra 5 seconds before the second update, increasing the window where other sessions requesting the same row (or the second row) will block. If another session holds the second row and later requests the first, deadlock is likely.

Recommended fixes:

• Move WAITFOR (or any non-transactional delay or slow work) outside the transaction so locks are held only for the actual DML: begin transaction → do DML → commit → WAITFOR.
• Best: do both updates inside the transaction only if they must be atomic; otherwise commit between them and move WAITFOR out.
• Minimize work inside the transaction. Acquire the transaction as late as possible and commit as early as possible.

2) Misuse of HOLDLOCK on the UPDATE

Evidence: UPDATE DeadlockTest WITH (HOLDLOCK) ...

Contention risk: HOLDLOCK is equivalent to SERIALIZABLE semantics for the duration of the transaction (it tells SQL Server to hold shared locks until transaction end). For updates it can force more restrictive locks (range/key-range or longer-held locks) than necessary. It increases the lock footprint and lock duration, which magnifies blocking and deadlock windows.

Recommended fixes:

• Remove WITH (HOLDLOCK) unless you explicitly need serializable behavior or range protection. The UPDATE already grabs the exclusive lock needed to change the row; HOLDLOCK is usually unnecessary for a targeted single-row clustered key update.
• If you must acquire locks to coordinate multiple statements, use explicit consistent locking (see next item) rather than HOLDLOCK by default.
• If you want to reduce lock retention, prefer leaving the default isolation (READ COMMITTED) or use snapshot isolation at the DB level if appropriate for your workload (note: changing isolation has wider effects).

3) Inconsistent resource access order (lock ordering) — likely cause of deadlocks

Evidence/Inference: Stored procedure name usp_DeadlockSide1 suggests there is (or may be) a 'side 2' that updates ID=2 then ID=1. The code updates ID=1 then ID=2 and waits between them.

Contention risk: If another procedure updates ID=2 first, then waits, then tries to update ID=1, you get the classic two-session deadlock: session A holds X lock on row1 and waits for X on row2; session B holds X on row2 and waits for X on row1.

Recommended fixes:

• Enforce a consistent ordering of resources across all code paths. For example, always update rows in increasing ID order (ID=1 then ID=2) across all procedures.
• If changes to callers are difficult, serialize access with an application-level lock (sp_getapplock) so only one side proceeds at a time:

BEGIN TRAN
EXEC sp_getapplock @Resource = 'DeadlockTest_Updates', 
     @LockMode = 'Exclusive', @LockTimeout = 10000;
-- safe to do updates in any order here
COMMIT

• Or, acquire intent/update locks on both keys in a fixed order before doing updates:

SELECT ... WITH (UPDLOCK, ROWLOCK) WHERE ID IN (1,2) ORDER BY ID

This obtains locks in a stable order, then you perform the UPDATEs.

4) Lock escalation / broader-than-needed locking potential

Evidence: Clustered Index Update plan — row seeks by PK are being used. With only two rows the immediate risk is low, but HOLDLOCK and larger multi-row updates could allow escalation or key-range locks.

Contention risk: If the transaction ends up touching many rows (or HOLDLOCK causes range locks), SQL Server could escalate to page/table locks or hold key-range locks, which would increase blocking dramatically.

Recommended fixes:

• Avoid hinting HOLDLOCK which can create range locks.
• If you do need to limit lock scope to row-level, consider adding ROWLOCK (but use carefully). Use only after measuring.
• More importantly: reduce transaction duration to lower chance of escalation under concurrent load.

5) Isolation level concerns

Evidence: HOLDLOCK acts like SERIALIZABLE; no DB-level snapshot instance present in plan.

Contention risk: Serializable-like behavior increases lock duration and key-range locking; default READ COMMITTED is less aggressive. Enabling READ_COMMITTED_SNAPSHOT (RCSI) or using SNAPSHOT isolation reduces reader-writer blocking, but does not remove writer-writer exclusive lock conflicts (which are the root of the update-update deadlock here).

Recommended fixes:

• Prefer READ_COMMITTED_SNAPSHOT to reduce read-blocking from transactions, but realize it won't prevent two-updater deadlocks.
• Use snapshot isolation only if you understand the implications (tempdb usage, semantics).

6) Multiple cached plans warning

Evidence: 'Multiple Plans (2)'. Plan creation time: two plans within a few hours.

Contention risk: If different plans are created under different SET options or parameterization, they may use different lock hints or ordering if the application issues different batches; inconsistent behavior can make reproduction and fixes harder — but not typically a direct cause of deadlocks.

Recommended fixes:

• Ensure consistent SET options across callers (ARITHABORT, QUOTED_IDENTIFIER) so plan reuse is consistent. This is housekeeping — it reduces plan churn and makes behavior predictable.

Concrete Recommended Changes (Ordered by Impact)

1. Move the WAITFOR outside the transaction. This is the highest-impact fix to reduce lock duration and blocking.

BEGIN TRAN; UPDATE ID=1; UPDATE ID=2; COMMIT; WAITFOR ...
-- OR do WAITFOR before BEGIN TRAN if you must wait before updating.

2. Remove WITH (HOLDLOCK) unless absolutely required. Plain UPDATE on a clustered PK row takes X lock on the row; HOLDLOCK adds serializable-style holding of shared locks and may cause range locks.

3. Enforce consistent access order across code that touches the same rows/keys. If you control the other side(s), make them update the same keys in the same order (ie., always ID=1 then ID=2).

4. If reordering callers is impossible or you need an immediate fix, serialize logical operations with sp_getapplock:

EXEC sp_getapplock @Resource = 'DeadlockTest_Update_Group', 
     @LockMode = 'Exclusive', @LockTimeout = 10000;
-- perform updates
EXEC sp_releaseapplock @Resource = 'DeadlockTest_Update_Group';

5. If you must ensure both locks are acquired before modifying, acquire UPDLOCK in a consistent order:

SELECT 1 FROM DeadlockTest WITH (UPDLOCK, ROWLOCK) WHERE ID = 1;
SELECT 1 FROM DeadlockTest WITH (UPDLOCK, ROWLOCK) WHERE ID = 2;
UPDATE DeadlockTest SET Val = 1 WHERE ID IN (1,2);  -- now both keys locked

Always acquire the SELECT/locks in the same order everywhere.

6. Consider enabling READ_COMMITTED_SNAPSHOT to reduce reader-writer blocking (not a fix for writer-writer deadlocks).

7. Monitor and measure after changes. Look at deadlock graph traces (extended events) to confirm the exact deadlock victim chain and verify the fix prevents the cycle.

Why These Recommendations Are Safe for This Case

The real problem here is lock duration and inconsistent ordering — removing unnecessary HOLDLOCK and moving WAITFOR out of the transaction removes the long window where locks are held, which is the principal contributor to deadlocks. Enforcing a stable access order (or using sp_getapplock) eliminates the circular wait condition that produces a deadlock.