Sql server® 2012 t-sql fundamentals

2-3d

Go to Connection 2 and notice that the update has finished.

2-3e

Run the following code for cleanup.

UPDATE Sales.OrderDetails 

  SET discount = 0.00 

WHERE orderid = 10249;

2-4

In this exercise, you will practice using the SERIALIZABLE isolation level.

2-4a

Run the following code in Connection 1 to set the isolation level to SERIALIZABLE and query 

Sales.OrderDetails.

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; 

 

BEGIN TRAN; 

 

  SELECT orderid, productid, unitprice, qty, discount 

  FROM Sales.OrderDetails 

  WHERE orderid = 10249;

www.it-ebooks.info

---

332  

Microsoft SQL Server 2012 T-SQL Fundamentals

2-4b

Run the following code in Connection 2 to attempt to insert a row to Sales.OrderDetails with the same 

order ID that is filtered by the previous query and notice that you are blocked.

INSERT INTO Sales.OrderDetails 

    (orderid, productid, unitprice, qty, discount) 

  VALUES(10249, 2, 19.00, 10, 0.00);

Note that in lower isolation levels (such as READ UNCOMMITTED, READ COMMITTED, or 

REPEATABLE READ), this INSERT statement wouldn’t have been blocked.

2-4c

Run the following code in Connection 1 to query Sales.OrderDetails again and commit the transaction.

  SELECT orderid, productid, unitprice, qty, discount 

  FROM Sales.OrderDetails 

  WHERE orderid = 10249; 

 

COMMIT TRAN;

You get the same result set that you got from the previous query in the same transaction, and 

because the INSERT statement was blocked, you get no phantom reads.

2-4d

Go back to Connection 2 and notice that the INSERT statement has finished.

2-4e

Run the following code for cleanup.

DELETE FROM Sales.OrderDetails 

WHERE orderid = 10249 

  AND productid = 2;

2-4f

Run the following code in both Connection 1 and Connection 2 to set the isolation level to the 

default.

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

2-5

In this exercise, you will practice using the SNAPSHOT isolation level.

www.it-ebooks.info

---