Recently we ran into some deadlocks in our performance test environment that resulted in some good analysis and resolution techniques. In this post, those techniques will be presented in an overly simplified way to be used only for reference. Thorough analysis and learning needs to happen on each of these techniques before they are implemented.

Different types of deadlocks:

• Bookmark deadlock or Keylook up deadlock or RDI lookup deadlock
• Serializable range scan deadlock
• Cascading constraint deadlock
• Intra-query parallelism deadlock
• Reverse object order deadlock

We’ll look at each and possible resolution ideas:

Bookmark Deadlock or Key Lookup Deadlock or RDI Lookup Deadlock

These generally occur between a SELECT statement and UPDATE/INSERT/DELETE statement. Usually SELECT statement is selected as victim to be rolled back.

• These could be resolved by adding covering index to the nonclustered index, so key or RID or Bookmark lookup could be eliminated.
• Resist the urge to use NOLOCK hints in SELECT statement. On the surface it seems like a good approach, but its a bad practice.
• Resist the urge to keep adding more and more indexes. Maintain a safe balance of indexes for optimal performance.

Serializable Range Scan Deadlock

The isolation level SERIALIZABLE is the most restrictive isolation level. It results in using range locks. When locks from higher compatibility level (shared-shared, shared-update, etc) are escalated to lower compatibility level (exclusive) between two processes that are waiting on each other, then this type of deadlock occurs. There are no each techniques for this, so try not to use SERIALIZABLE isolation level unless it is absolutely necessary.

See if

• SELECT operation could be performed without such range locks.
• This transaction required SERIALIZABLE isolation level. How about READ COMMITTED (with SNAPSHOT), if it helps.

Cascading Constraint Deadlock

In a parent-child table scenario, if a record from parent table is being deleted or updated, then necessary locks child table records become necessary. This prevents orphan records in child tables. If two different processes, individually perform operations on related records these two tables, then they end up deadlocking with each other.

To prevent:

• Always deal with records in child table, before manipulating parent records
• Resist the urge to use WITH DELETE CASCADE option on parent tables.
• Make sure necessary non-clustered indexes are created on the foreign key in child table

Intra-Query Parallelism Deadlock

This is the most interesting deadlock of all. When a large workload/query is executed, sometimes Sql Server carries out the work using multi-threading. These threads end up blocking each other resulting in deadlocks. There is no much you can do, in these scenarios. In resource list, multiple exchangeEvents will be captured, each having their own ecid, kpid but the same SPID.

See if you could:

• Carry out the operations using lesser degree of parallelism.

Reverse Object Order Deadlock

As the name suggests, this occurs when two different processes hold locks on different resources, while requesting lock on second resource in opposite order. This is not good. Code change would be necessary to fix this issue.

See if you could:

• Access resources (tables, indexes, etc) in the same order in each process. This sounds outlandish and hard to believe, but in reality business processes always happen in a particular order (with some exceptions). So always maintain the order in data access as well.