Deadlocks in SQL Server. SQL Server has a deadlock detection routine, called a lock monitor, that regularly checks for the presence of deadlocks in SQL Server. Once a deadlock condition is detected, SQL Server selects one of the sessions participating in the deadlock as a victim to break the circular blocking. The victim is usually the process with the lowest estimated cost since this implies that process will be the easiest one for SQL Server to roll back. This operation involves withdrawing all the resources held by the victim session. SQL Server does so by rolling back the uncommitted transaction of the session picked as a victim.

Avoiding deadlocks

The following are some of the techniques you can use to avoid a deadlock:

Access resources in the same physical order => the first transaction will successfully acquire locks on the resources without being blocked by the second transaction.

Decrease the locking
• Convert a nonclustered index to a clustered index.
• Use a covering index for a SELECT statement

Minimize lock contention
• Implement row versioning through the READ_COMMITTED_SNAPSHOT isolation level or through the SNAPSHOT isolation level
• Decrease the isolation level
• Use locking hints: NOLOCK or READUNCOMMITTED.

Deadlock victim selection

The deadlock victim is chosen based on the following criteria:

  • The DEADLOCK_PRIORITY of the two sessions is compared and the lowest priority session is selected as the victim.
  • If both sessions have the same DEADLOCK_PRIORITY value, the transaction that is the least expensive to rollback (based on the log records that have been generated) is selected as the victim.
Subscribe to RSS - deadlocks