deadlock retries

Summary information

Default value

5

Range of values

0–2147483647

Status

Dynamic

Display level

Intermediate

Required role

System Administrator

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 4-4 illustrates the following scenario:

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 4-4: Deadlocks during page splitting in a clustered index

sp_sysmon reports on deadlocks and retries. See the Performance and Tuning Guide for more information.