Sql server® 2012 t-sql fundamentals

 

CHAPTER 9

 

Transactions and Concurrency

 

 319

When you’re done, run the following code for cleanup.

UPDATE Production.Products 

  SET unitprice = 19.00 

WHERE productid = 2;

Conflict Detection

The SNAPSHOT isolation level prevents update conflicts, but unlike the REPEATABLE READ and 

SERIALIZABLE isolation levels that do so by generating a deadlock, the SNAPSHOT isolation level 

fails the transaction, indicating that an update conflict was detected. The SNAPSHOT isolation 

level can detect update conflicts by examining the version store. It can figure out whether another 

transaction modified the data between a read and a write that took place in your transaction. 

The following example demonstrates a scenario with no update conflict, followed by an example of 

a scenario with an update conflict.

Run the following code in Connection 1 to set the transaction isolation level to SNAPSHOT, open a 

transaction, and read the row for product 2.

SET TRANSACTION ISOLATION LEVEL SNAPSHOT; 

 

BEGIN TRAN; 

 

  SELECT productid, unitprice 

  FROM Production.Products 

  WHERE productid = 2;

You get the following output.

productid   unitprice 

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

2           19.00

Assuming you have made some calculations based on what you read, run the following code while 

still in Connection 1 to update the price of the product you queried previously to 20.00, and commit 

the transaction.

  UPDATE Production.Products 

    SET unitprice = 20.00 

  WHERE productid = 2; 

   

COMMIT TRAN;

No other transaction modified the row between your read, calculation, and write; therefore, there 

was no update conflict and SQL Server allowed the update to take place.

Run the following code to modify the price of product 2 back to 19.00.

UPDATE Production.Products 

  SET unitprice = 19.00 

WHERE productid = 2;

www.it-ebooks.info

---

320  

Microsoft SQL Server 2012 T-SQL Fundamentals

Next, run the following code in Connection 1, again, to open a transaction, and read the row for 

product 2.

BEGIN TRAN; 

 

  SELECT productid, unitprice 

  FROM Production.Products 

  WHERE productid = 2;

You get the following output, indicating that the price of the product is 19.00.

productid   unitprice 

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

2           19.00

This time, run the following code in Connection 2 to update the price of product 2 to 25.00.

UPDATE Production.Products 

  SET unitprice = 25.00 

WHERE productid = 2;

Assume that you have made calculations in Connection 1 based on the price of 19.00 that you 

read. Based on your calculations, try to update the price of the product to 20.00 in Connection 1.

  UPDATE Production.Products 

    SET unitprice = 20.00 

  WHERE productid = 2;

SQL Server detected that this time another transaction modified the data between your read and 

write; therefore, it fails your transaction with the following error.

Msg 3960, Level 16, State 2, Line 1 

Snapshot isolation transaction aborted due to update conflict. You cannot use snapshot isolation 

to access table 'Production.Products' directly or indirectly in database 'TSQL2012' to update, 

delete, or insert the row that has been modified or deleted by another transaction. Retry the 

transaction or change the isolation level for the update/delete statement.

Of course, you can use error handling code to retry the whole transaction when an update conflict 

is detected.

When you’re done, run the following code for cleanup.

UPDATE Production.Products 

  SET unitprice = 19.00 

WHERE productid = 2;

Close all connections. Note that if all connections aren’t closed, your example results might not 

match those in the chapter examples.

www.it-ebooks.info

---