Introduction SQL Server deadlocks typically involve UPDATE operations, but SELECT queries can also deadlock when they perform key lookups. This happens when a SELECT acquires a shared lock on a nonclustered index, then needs to acquire a shared lock on the clustered index (key lookup), while an UPDATE holds an exclusive lock on the clustered index and needs a shared lock on the nonclustered index.

Symptoms - Error 1205: `Transaction (Process ID) was deadlocked on lock resources` - Deadlock graph shows SELECT and UPDATE processes in a circular wait - Deadlocks occur under concurrent read/write load on the same table - Deadlock XML shows `objectlock` with `mode=S` and `mode=X` conflicts - Application retry logic masking the underlying index problem

Common Causes - Nonclustered index does not cover all columns needed by the SELECT query - Key lookup in the execution plan requiring access to the clustered index - UPDATE acquiring exclusive locks on the clustered index while SELECT waits - Missing covering index forcing the optimizer to use key lookups - High concurrency on tables with narrow nonclustered indexes

Step-by-Step Fix 1. **Enable deadlock trace flags to capture details": ```sql -- Enable deadlock graph capture DBCC TRACEON(1222, -1); DBCC TRACEON(1204, -1);

-- Or use Extended Events (recommended for SQL Server 2012+) CREATE EVENT SESSION [DeadlockCapture] ON SERVER ADD EVENT sqlserver.xml_deadlock_report ADD TARGET package0.event_file(SET filename = N'DeadlockCapture.xel'); ALTER EVENT SESSION [DeadlockCapture] ON SERVER STATE = START; ```

  1. 1.**Analyze the deadlock graph":
  2. 2.```sql
  3. 3.-- Query the Extended Events data
  4. 4.SELECT
  5. 5.CAST(event_data AS XML) AS deadlock_graph,
  6. 6.timestamp
  7. 7.FROM sys.fn_xe_file_target_read_file('DeadlockCapture*.xel', NULL, NULL, NULL);
  8. 8.`
  9. 9.**Create a covering index to eliminate the key lookup":
  10. 10.```sql
  11. 11.-- Before: Key lookup in execution plan
  12. 12.-- SELECT customer_id, order_date, total, shipping_address
  13. 13.-- FROM orders WHERE status = 'pending'

-- Find the missing columns SELECT OBJECT_NAME(object_id) AS table_name, name AS index_name, included_columns FROM sys.indexes WHERE object_id = OBJECT_ID('orders');

-- Create covering index CREATE NONCLUSTERED INDEX IX_orders_status_covering ON orders (status) INCLUDE (order_date, total, shipping_address); ```

  1. 1.**Verify the key lookup is eliminated":
  2. 2.```sql
  3. 3.SET STATISTICS IO ON;
  4. 4.SET STATISTICS TIME ON;

SELECT customer_id, order_date, total, shipping_address FROM orders WHERE status = 'pending';

-- Check execution plan: should show Index Seek only, no Key Lookup -- STATISTICS IO should show fewer logical reads ```

  1. 1.**Use NOLOCK hint if dirty reads are acceptable":
  2. 2.```sql
  3. 3.-- For reporting queries where stale data is acceptable
  4. 4.SELECT customer_id, order_date, total
  5. 5.FROM orders WITH (NOLOCK)
  6. 6.WHERE status = 'pending';
  7. 7.`

Prevention - Create covering indexes for frequently executed SELECT queries - Monitor deadlock graphs regularly and fix root causes, not symptoms - Use `READ COMMITTED SNAPSHOT` to reduce lock contention for reads - Keep transactions short to minimize lock hold time - Analyze query execution plans for key lookups during code review - Use Database Engine Tuning Advisor to identify missing indexes - Implement deadlock retry logic in the application as a safety net