Sql server® 2012 t-sql fundamentals

 

CHAPTER 9

 

Transactions and Concurrency

 

 333

2-5a

If you’re doing the exercises against an on-premises SQL Server instance, run the following code to 

set the SNAPSHOT isolation level in the TSQL2012 database (enabled in SQL Database by default):

ALTER DATABASE TSQL2012 SET ALLOW_SNAPSHOT_ISOLATION ON;

2-5b

Run the following code in Connection 1 to open a transaction, update rows in Sales.OrderDetails, and 

query it.

BEGIN TRAN; 

 

  UPDATE Sales.OrderDetails 

    SET discount += 0.05 

  WHERE orderid = 10249; 

 

  SELECT orderid, productid, unitprice, qty, discount 

  FROM Sales.OrderDetails 

  WHERE orderid = 10249;

2-5c

Run the following code in Connection 2 to set the isolation level to SNAPSHOT and query 

Sales.OrderDetails. Notice that you’re not blocked—instead, you get an earlier, consistent ver-

sion of the data that was available when the transaction started (with discount values of 0.00).

SET TRANSACTION ISOLATION LEVEL SNAPSHOT; 

 

BEGIN TRAN; 

 

  SELECT orderid, productid, unitprice, qty, discount 

  FROM Sales.OrderDetails 

  WHERE orderid = 10249;