Sql server® 2012 t-sql fundamentals

 

CHAPTER 9

 

Transactions and Concurrency

 

 335

2-6d

Run the following code in Connection 2, which is now running under the READ COMMITTED 

SNAPSHOT isolation level because the database flag READ_COMMITTED_SNAPSHOT is turned 

on. Notice that you’re not blocked—instead, you get an earlier, consistent version of the data 

that was available when the statement started (with discount values of 0.00).

BEGIN TRAN; 

 

  SELECT orderid, productid, unitprice, qty, discount 

  FROM Sales.OrderDetails 

  WHERE orderid = 10249;

2-6e

Go to Connection 1 and commit the transaction.

COMMIT TRAN;

2-6f

Go to Connection 2, query the data again, and commit the transaction; notice that you get the new 

discount values of 0.05.

  SELECT orderid, productid, unitprice, qty, discount 

  FROM Sales.OrderDetails 

  WHERE orderid = 10249; 

 

COMMIT TRAN;

2-6g

Run the following code for cleanup.

UPDATE Sales.OrderDetails 

  SET discount = 0.00 

WHERE orderid = 10249;

Close all connections.

2-6h

If you are running against an on-premises SQL Server instance, change the database flags back to the 

defaults, disabling isolation levels based on row versioning.

ALTER DATABASE TSQL2012 SET ALLOW_SNAPSHOT_ISOLATION OFF; 

ALTER DATABASE TSQL2012 SET READ_COMMITTED_SNAPSHOT OFF;

Exercise 3 (steps 1 through 7) deals with deadlocks.

www.it-ebooks.info

---

336  

Microsoft SQL Server 2012 T-SQL Fundamentals

3-1

Open two new connections. (This exercise will refer to them as Connection 1 and Connection 2.)

3-2

Run the following code in Connection 1 to open a transaction and update the row for product 2 in 

Production.Products.

BEGIN TRAN; 

 

  UPDATE Production.Products 

    SET unitprice += 1.00 

  WHERE productid = 2;

3-3

Run the following code in Connection 2 to open a transaction and update the row for product 3 in 

Production.Products.

BEGIN TRAN; 

 

  UPDATE Production.Products 

    SET unitprice += 1.00 

  WHERE productid = 3;

3-4

Run the following code in Connection 1 to query product 3. You will be blocked. (Remember to un-

comment the hint if you are connected to SQL Database.)

  SELECT productid, unitprice 

  FROM Production.Products -- WITH (READCOMMITTEDLOCK) 

  WHERE productid = 3; 

 

COMMIT TRAN;

3-5

Run the following code in Connection 2 to query product 2. You will be blocked, and a deadlock error 

will be generated either in Connection 1 or Connection 2.

  SELECT productid, unitprice 

  FROM Production.Products -- WITH (READCOMMITTEDLOCK) 

  WHERE productid = 2; 

 

COMMIT TRAN;

www.it-ebooks.info

---