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 5-4 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-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.
