Sql server® 2012 t-sql fundamentals

 

CHAPTER 9

 

Transactions and Concurrency

 

 325

In this example, SQL Server chose to terminate the transaction in Connection 1 (shown here as 

process ID 52). Because you didn’t set a deadlock priority and both transactions did a similar amount 

of work, either transaction could have been terminated.

Deadlocks are expensive because they involve undoing work that has already been done. You can 

follow a few practices to mitigate deadlock occurrences in your system. 

Obviously, the longer the transactions are, the longer locks are kept, increasing the probability of 

deadlocks. You should try to keep transactions as short as possible, taking activities out of the trans-

action that aren’t logically supposed to be part of the same unit of work.

A deadlock happens when transactions access resources in inverse order. For example, in the 

example, Connection 1 first accessed a row in Production.Products and then accessed a row in 

Sales.OrderDetails, whereas Connection 2 first accessed a row in Sales.OrderDetails and then 

accessed a row in Production.Products. This type of deadlock can’t happen if both transactions 

access resources in the same order. By swapping the order in one of the transactions, you can 

prevent this type of deadlock from happening—assuming that it makes no logical difference to 

your application. 

The deadlock example has a real logical conflict because both sides try to access the same rows. 

However, deadlocks often happen when there is no real logical conflict, because of a lack of good in-

dexing to support query filters. For example, suppose that both statements in the transaction in Con-

nection 2 were to filter product 5. Now that the statements in Connection 1 handle product 2 and the 

statements in Connection 2 handle product 5, there shouldn’t be any conflict. However, if no indexes 

on the productid column in the tables support the filter, SQL Server has to scan (and lock) all rows in 

the table. This, of course, can lead to a deadlock. In short, good index design can help mitigate the 

occurrences of deadlocks that have no real logical conflict.

Another option to consider when mitigating deadlock occurrences is the choice of isolation level. The 

SELECT statements in the example needed shared locks because they ran under the READ COMMITTED 

isolation level. If you use the READ COMMITTED SNAPSHOT isolation level, readers will not need shared 

locks, and such deadlocks that evolve due to the involvement of shared locks can be eliminated. 

When you’re done, run the following code for cleanup in any connection.

UPDATE Production.Products 

  SET unitprice = 19.00 

WHERE productid = 2; 

 

UPDATE Sales.OrderDetails 

  SET unitprice = 19.00 

WHERE productid = 2 

  AND orderid >= 10500; 

 

UPDATE Sales.OrderDetails 

  SET unitprice = 15.20 

WHERE productid = 2 

  AND orderid < 10500;

www.it-ebooks.info

---