deadlock retries

Summary information
Default value	5
Range of values	0–2147483647
Status	Dynamic
Display level	Intermediate
Required role	System administrator
Configuration groups	Lock Manager, SQL Server Administration

deadlock retries specifies the number of times a transaction can attempt to acquire a lock when deadlocking occurs during an index page split or shrink.

For example, Figure 5-1 illustrates the following scenario:

Transaction A locks page 1007 and needs to acquire a lock on page 1009 to update the page pointers for a page split.
Transaction B is also inserting an index row that causes a page split, holds a lock on page 1009, and needs to acquire a lock on page 1007.

In this situation, rather than immediately choosing a process as a deadlock victim, Adaptive Server relinquishes the index locks for one of the transactions. This often allows the other transaction to complete and release its locks.

For the transaction that surrendered its locking attempt, the index is rescanned from the root page, and the page split operation is attempted again, up to the number of times specified by deadlock retries.

Figure 5-1: Deadlocks during page splitting in a clustered index

Graphic showing three levels of page chains and a resulting deadlock. The first page chain results in pages 1007 and 1009 splitting. Because of the splits, page 1009 requires a lock on page 1007, and page 1007 requires a lock on page 1009, resulting ina deadlock situatation.

sp_sysmon reports on deadlocks and retries. See the Performance and Tuning Series: Locking and Concurrency Control.