Database Reference
In-Depth Information
Decreasing the Number of Resources Accessed
A deadlock involves at least two resources. A session holds the first resource and then requests the second resource.
The other session holds the second resource and requests the first resource. If you can prevent the sessions (or at least
one of them) from accessing one of the resources involved in the deadlock, then you can prevent the deadlock. You
can achieve this by redesigning the application, which is a solution highly resisted by developers late in the project.
However, you can consider using the following features of SQL Server without changing the application design:
•
Convert a nonclustered index to a clustered index.
SELECT
statement.
•
Use a covering index for a
Convert a Nonclustered Index to a Clustered Index
As you know, the leaf pages of a nonclustered index are separate from the data pages of the heap or the clustered
index. Therefore, a nonclustered index takes two locks: one for the base (either the cluster or the heap) and one for
the nonclustered index. However, in the case of a clustered index, the leaf pages of the index and the data pages of the
table are the same; it requires one lock, and that one lock protects both the clustered index and the table because the
leaf pages and the data pages are the same. This decreases the number of resources to be accessed by the same query,
compared to a nonclustered index. But, it is completely dependent on this being an appropriate clustered index.
There's nothing magical about the clustered index that simply applying it to any column would help. You still need to
assess whether it's appropriate.
Use a Covering Index for a SELECT Statement
You can also use a covering index to decrease the number of resources accessed by a
SELECT
statement. Since a
SELECT
statement can get everything from the covering index itself, it doesn't need to access the base table. Otherwise,
the
SELECT
statement needs to access both the index and the base table to retrieve all the required column values.
Using a covering index stops the
SELECT
statement from accessing the base table, leaving the base table free to be
locked by another session.
Minimizing Lock Contention
You can also resolve a deadlock by avoiding the lock request on one of the contended resources. You can do this when
the resource is accessed only for reading data. Modifying a resource will always acquire an exclusive (X) lock on the
resource to maintain the consistency of the resource; therefore, in a deadlock situation, identify the resource accesses
that are read-only and try to avoid their corresponding lock requests by using the dirty read feature, if possible. You
can use the following techniques to avoid the lock request on a contended resource:
•
Implement row versioning.
•
Decrease the isolation level.
•
Use locking hints.
