Sql server® 2012 t-sql fundamentals

 

CHAPTER 9

 

Transactions and Concurrency

 

 323

Summary of Isolation Levels

Table 9-3 provides a summary of the logical consistency problems that can or cannot happen in each 

isolation level and indicates whether the isolation level detects update conflicts for you and whether 

the isolation level uses row versioning.

TABLE 9-3

  Summary of Isolation Levels

Isolation Level

Allows 

uncommitted 

Reads?

Allows 

Non-

repeatable 

Reads?

Allows  

Lost 

updates?

Allows 

Phantom 

Reads?

Detects 

update 

Conflicts?

uses  

Row 

Versioning?

READ UNCOMMITTED

Yes

Yes

Yes

Yes

No

No

READ COMMITTED

No

Yes

Yes

Yes

No

No

READ COMMITTED SNAPSHOT

No

Yes

Yes

Yes

No

Yes

REPEATABLE READ

No

No

No

Yes

No

No

SERIALIZABLE

No

No

No

No

No

No

SNAPSHOT

No

No

No

No

Yes

Yes

Deadlocks

A deadlock is a situation in which two or more processes block each other. An example of a two-

process deadlock is when process A blocks process B and process B blocks process A. An example of 

a deadlock involving more than two processes is when process A blocks process B, process B blocks 

process C, and process C blocks process A. In either case, SQL Server detects the deadlock and inter-

venes by terminating one of the transactions. If SQL Server does not intervene, the processes involved 

would remain deadlocked forever.

Unless otherwise specified, SQL Server chooses to terminate the transaction that did the least work, 

because it is cheapest to roll that transaction’s work back. However, SQL Server allows you to set a ses-

sion option called DEADLOCK_PRIORITY to one of 21 values in the range –10 through 10. The process 

with the lowest deadlock priority is chosen as the deadlock “victim” regardless of how much work is 

done; in the event of a tie, the amount of work is used as a tiebreaker.

The following example demonstrates a simple deadlock. Then I’ll explain how you can mitigate 

deadlock occurrences in the system.

Open two connections and make sure that you are connected to the TSQL2012 database in 

both. Run the following code in Connection 1 to open a new transaction, update a row in the 

Production.Products table for product 2, and leave the transaction open.

USE TSQL2012; 

 

BEGIN TRAN; 

 

  UPDATE Production.Products 

    SET unitprice += 1.00 

  WHERE productid = 2;

www.it-ebooks.info

---

324  

Microsoft SQL Server 2012 T-SQL Fundamentals

Run the following code in Connection 2 to open a new transaction, update a row in the 

Sales.OrderDetails table for product 2, and leave the transaction open.

BEGIN TRAN; 

 

  UPDATE Sales.OrderDetails 

    SET unitprice += 1.00 

  WHERE productid = 2;

At this point, the transaction in Connection 1 is holding an exclusive lock on the row for product 2 in 

the Production.Products table, and the transaction in Connection 2 is now holding locks on the rows 

for product 2 in the Sales.OrderDetails table. Both queries succeed, and no blocking has occurred yet.

Run the following code in Connection 1 to attempt to query the rows for product 2 in the 

Sales.OrderDetails table and commit the transaction (remember to uncomment the hint if you 

are running the transaction against SQL Database).

  SELECT orderid, productid, unitprice 

  FROM Sales.OrderDetails -- WITH (READCOMMITTEDLOCK) 

  WHERE productid = 2; 

 

COMMIT TRAN;

The transaction in Connection 1 needs a shared lock to be able to perform its read. Because the 

other transaction holds an exclusive lock on the same resource, the transaction in Connection 1 is 

blocked. At this point, you have a blocking situation, not yet a deadlock. Of course, a chance remains 

that Connection 2 will end the transaction, releasing all locks and allowing the transaction in Connec-

tion 1 to get the requested locks.

Next, run the following code in Connection 2 to attempt to query the row for product 2 in the 

Product.Production table and commit the transaction.

  SELECT productid, unitprice 

  FROM Production.Products -- WITH (READCOMMITTEDLOCK) 

  WHERE productid = 2; 

 

COMMIT TRAN;

To be able to perform its read, the transaction in Connection 2 needs a shared lock on the row 

for product 2 in the Product.Production table, so this request is now in conflict with the exclusive lock 

held on the same resource by Connection 1. Each of the processes blocks the other—you have a 

deadlock. SQL Server identifies the deadlock (typically within a few seconds), chooses one of the two 

processes as the deadlock victim, and terminates its transaction with the following error.

Msg 1205, Level 13, State 51, Line 1 

Transaction (Process ID 52) was deadlocked on lock resources with another process and has been 

chosen as the deadlock victim. Rerun the transaction.

www.it-ebooks.info

---