Database Reference
In-Depth Information
Reducing the Chance of Deadlocks
Finally, there is some practical advice to help us deal with deadlocks.
1.
Optimize the queries. Scans introduced by non-optimized queries are the most common
cases of deadlocks. Right indexes not only improve the performance of the queries,
but they also reduce the number of rows that need to be read and locks that need to be
acquired. The large numbers of locks acquired by the query increases the chances of
collisions with the other sessions.
2.
Keep locks as short as possible. As you will recall, all exclusive (X) locks are held until
the end of the transaction. Make transactions short and try to update data as close to
the end of the transaction as possible. In our example, you can change the code and
swap around select and update statements. This would solve the particular deadlock
problem because there are no statements that can be blocked after the transaction
acquired exclusive (X) locks.
3.
Use the lowest transaction isolation level that provides the required data consistency.
This reduces the time shared (S) locks are held. Even if you swapped select and update
statements in our example, you could still have the deadlock in REPEATABLE READ or
SERIALIZABLE isolation levels. With those isolation levels, we would have shared (S)
locks held until the end of the transaction and they could block update statements. With
READ COMMITTED mode, shared (S) locks are released after a row is read and update
statements would not be blocked.
4.
Access the entities in the same order. For example, avoid the situation when one session
updates the Orders table, read from OrderItems table, while another session updates the
OrderItems table first, and read from Orders table after that.
5.
Avoid updating the row multiple times within the same transaction when multiple
indexes are involved . As you saw earlier in this chapter, SQL Server does not place
exclusive (X) locks on non-clustered index rows when index columns are not updated.
As a result, other sessions can place incompatible locks there and block subsequent
updates, which would lead to deadlocks.
Use retries logic. Wrap critical code into TRY..CATCH block and retry the action if
deadlock occurs. The error number for the exception caused by the deadlock is 1205.
The code in Listing 19-9 shows how you can do that.
6.
Listing 19-9. Using TRY..CATCH block to retry the operation in case of deadlock
-- Declare and set variable to track number of retries to try before exiting.
declare
@retry int = 5
-- Keep trying to update table if this task is selected as the deadlock victim.
while (@retry > 0)
begin
begin try
begin tran
-- some code that can lead to the deadlock
commit
end try
begin catch
 
Search WWH ::




Custom Search