deadlock retries

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.

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

For example, in the figure below:
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.

In this situation, rather than immediately choosing a process as a deadlock victim, SAP ASE 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.

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