Sql server® 2012 t-sql fundamentals

 

CHAPTER 9

 

Transactions and Concurrency

 

 311

BEGIN TRAN; 

 

  UPDATE Production.Products 

    SET unitprice += 1.00 

  WHERE productid = 2; 

 

  SELECT productid, unitprice 

  FROM Production.Products 

  WHERE productid = 2;

Note that the transaction remains open, meaning that the product’s row is locked exclusively by 

Connection 1. The code in Connection 1 returns the following output showing the product’s new 

price.

productid   unitprice 

----------- --------------------- 

2           20.00

In Connection 2, run the following code to set the isolation level to READ UNCOMMITTED and 

query the row for product 2.

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; 

 

SELECT productid, unitprice 

FROM Production.Products 

WHERE productid = 2;

Because the query did not request a shared lock, it was not in conflict with the other transaction. 

This query returned the state of the row after the change, even though the change was not committed.

productid   unitprice 

----------- --------------------- 

2           20.00

Keep in mind that Connection 1 might apply further changes to the row later in the transaction 

or even roll back at some point. For example, run the following code in Connection 1 to roll back the 

transaction.

ROLLBACK TRAN;

This rollback undoes the update of product 2, changing its price back to 19.00. The value 20.00 

that the reader got was never committed. That’s an example of a dirty read.