Database Reference
In-Depth Information
Implement Row Versioning
Instead of attempting to prevent access to resources using a more stringent locking scheme, you could implement
row versioning through the
READ_COMMITTED_SNAPSHOT
isolation level or through the
SNAPSHOT
isolation level. The
row versioning isolation levels are used to reduce blocking, as outlined in Chapter 20. Because they reduce blocking,
which is the root cause of deadlocks, they can also help with deadlocks. By introducing
READ_COMMITTED_SNAPSHOT
with the following T-SQL, you can have a version of the rows available in
tempdb
, thus potentially eliminating the
contention caused by the lock in the preceding deadlock scenario:
ALTER DATABASE AdventureWorks2012
SET READ_COMMITTED_SNAPSHOT ON;
This will allow any necessary reads without causing lock contention since the reads are on a different version
of the data. There is overhead associated with row versioning, especially in
tempdb
and when marshaling data from
multiple resources instead of just the table or indexes used in the query. But that trade-off of increased
tempdb
overhead versus the benefit of reduced deadlocking and increased concurrency may be worth the cost.
Decrease the Isolation Level
Sometimes the (S) lock requested by a
SELECT
statement contributes to the formation of circular blocking. You can
avoid this type of circular blocking by reducing the isolation level of the transaction containing the
SELECT
statement
to
READ UNCOMMITTED
. This will allow the
SELECT
statement to read the data without requesting an (S) lock and thereby
avoid the circular blocking. However, reading uncommitted data carries with it a serious issue by returning bad data
to the client. You need to be in dire straits to consider this as a method of eliminating your deadlocks.
Also check to see whether the connections are setting themselves to be
SERIALIZABLE
. Sometimes online
connection string generators will include this option, and developers will use it completely by accident. MSDTC will
use serializable by default, but it can be changed.
Use Locking Hints
I absolutely do not recommend this approach. However, you can potentially resolve the deadlock presented in the
preceding technique using the following locking hints:
•
NOLOCK
•
READUNCOMMITTED
Like the
READ UNCOMMITTED
isolation level, the
NOLOCK
or
READUNCOMMITTED
locking hint will avoid the (S) locks
requested by a given session, thereby preventing the formation of circular blocking.
The effect of the locking hint is at a query level and is limited to the table (and its indexes) on which it is applied.
The
NOLOCK
and
READUNCOMMITTED
locking hints are allowed only in
SELECT
statements and the data selection part of
the
INSERT
,
DELETE
, and
UPDATE
statements.
The resolution techniques of minimizing lock contention introduce the side effect of a dirty read, which may
not be acceptable in every transaction. A dirty read can involve missing rows or extra rows because of page splits
and rearranging pages. Therefore, use these resolution techniques only in situations in which a low quality of data is
acceptable.
