|
CHAPTER 9
Transactions and Concurrency
317
The
SNAPSHOT
Isolation Level
Under the SNAPSHOT isolation level, when the reader is reading data, it is guaranteed to get the last
committed version of the row that was available when the transaction started. This means that you
are guaranteed to get committed reads and repeatable reads, and also guaranteed not to get phantom
reads—just as in the SERIALIZABLE isolation level. But instead of using shared locks, this isolation
level relies on row versioning. As mentioned, snapshot isolation levels incur a performance penalty,
mainly when updating and deleting data, regardless of whether or not the modification is executed
from a session running under one of the snapshot-based isolation levels. For this reason, to allow your
transactions to work with the SNAPSHOT isolation level in an on-premises SQL Server instance (this
behavior is enabled by default in SQL Database), you need to first enable the option at the database
level by running the following code in any open query window.
ALTER DATABASE TSQL2012 SET ALLOW_SNAPSHOT_ISOLATION ON;
The following example demonstrates the behavior of the SNAPSHOT isolation level. Run the fol-
lowing code from Connection 1 to open a transaction, update the price of product 2 by adding 1.00
to its current price of 19.00, and query the product’s row to show the new price.
BEGIN TRAN;
UPDATE Production.Products
SET unitprice += 1.00
WHERE productid = 2;
SELECT productid, unitprice
FROM Production.Products
WHERE productid = 2;
Here the output of this code shows that the product’s price was updated to 20.00.
productid unitprice
----------- ---------------------
2 20.00
Note that even if the transaction in Connection 1 runs under the READ COMMITTED isolation
level, SQL Server has to copy the version of the row before the update (with the price of 19.00) to
tempdb. That’s because the SNAPSHOT isolation level is enabled at the database level. If someone
begins a transaction using the SNAPSHOT isolation level, they can request the version before the up-
date. For example, run the following code from Connection 2 to set the isolation level to SNAPSHOT,
open a transaction, and query the row for product 2.
SET TRANSACTION ISOLATION LEVEL SNAPSHOT;
BEGIN TRAN;
SELECT productid, unitprice
FROM Production.Products
WHERE productid = 2;
www.it-ebooks.info
318
Microsoft SQL Server 2012 T-SQL Fundamentals
If your transaction had been under the SERIALIZABLE isolation level, the query would have been
blocked. But because it is running under SNAPSHOT, you get the last committed version of the row
that was available when the transaction started. That version (with the price of 19.00) is not the cur-
rent version (with the price of 20.00), so SQL Server pulls the appropriate version from the version
store, and the code returns the following output.
productid unitprice
----------- ---------------------
2 19.00
Go back to Connection 1 and commit the transaction that modified the row.
COMMIT TRAN;
At this point, the current version of the row with the price of 20.00 is a committed version. How-
ever, if you read the data again in Connection 2, you should still get the last committed version of the
row that was available when the transaction started (with a price of 19.00). Run the following code in
Connection 2 to read the data again, and then commit the transaction.
SELECT productid, unitprice
FROM Production.Products
WHERE productid = 2;
COMMIT TRAN;
As expected, you get the following output with a price of 19.00.
productid unitprice
----------- ---------------------
2 19.00
Run the following code in Connection 2 to open a new transaction, query the data, and commit
the transaction.
BEGIN TRAN
SELECT productid, unitprice
FROM Production.Products
WHERE productid = 2;
COMMIT TRAN;
This time, the last committed version of the row that was available when the transaction started is
the one with a price of 20.00. Therefore, you get the following output.
productid unitprice
----------- ---------------------
2 20.00
Now that no transaction needs the version of the row with the price of 19.00, a cleanup thread that
runs once a minute can remove it from tempdb the next time it runs.
www.it-ebooks.info
Do'stlaringiz bilan baham: | 
