Locking and Concurrency - SQL Server 2012 Internals and Troubleshooting - page 175

Lock escalation can be prevented by setting a table option to disallow it, or by forcing queries to take
out table locks to start with. Ideally, you should let the system escalate locks as required, and only
consider this kind of action when the number of escalations (monitored through Lock:Escalation
events) becomes signii cantly higher than expected (compared to a benchmark of your system in a
healthy state). You can also use trace l ags (1211 and 1224) to disable lock escalation.
DEADLOCKS
Ideally, despite locks, your database system will allow a lot of users at once, and each transaction
will get in, make the single change needed, and get out again; but locks inevitably mean blocking,
and when transactions need to do multiple operations, this locking can even lead to deadlocks.
Although your application users will report that the application has deadlocked, this kind of
behavior does not actually mean a deadlock has occurred. When a deadlock has been detected, the
Database Engine terminates one of the threads, resolving the deadlock. The terminated thread gets a
1205 error, which conveniently suggests how to resolve it:
Error 1205 : Transaction (Process ID) was deadlocked on resources with another
process and has been chosen as the deadlock victim. Rerun the transaction.
Indeed, rerunning the transaction is often the best course of action here, and hopefully your applica-
tion or even your stored procedure will have caught the error, recognized that it is a 1205, and tried
the transaction again. Let's consider how a deadlock occurs, though.
It's quite straightforward really — one transaction locks a resource and then tries to acquire a lock
on another resource but is blocked by another transaction. It won't be able to i nish its transaction
until such time as this second transaction completes and therefore releases its locks. However, if the
second transaction does something that needs to wait for the i rst transaction, they'll end up waiting
forever. Luckily this is detected by the Database Engine, and one of the processes is terminated.
When diagnosing these kinds of problems, it's worth considering that there are useful trace events
such as Lock:Deadlock and Deadlock graph events. This enables you to see which combination of
resources was being requested, and hopefully track down the cause. In most cases, the best option is
to help the system get the quickest access to the resources that need updating. The quicker a transac-
tion can release its resources, the less likely it is to cause a deadlock. However, another option is to
lock up additional resources so that no two transactions are likely to overlap. Depending on the situ-
ation, a hint to lock an entire table can sometimes help by not letting another transaction acquire
locks on parts of the table, although this can also cause blocking that results in transactions overlap-
ping, so your mileage may vary.
ISOLATION LEVELS
Isolation levels determine how much transactions can see into other transactions, and can range
from not-at-all to plenty. Understanding what the isolation levels do so that you can see how they
prevent the concurrency side-effects described earlier can help you i nd an appropriate compromise
between locking down too much and providing the necessary protection for your environment.