Sql server® 2012 t-sql fundamentals

 

CHAPTER 9

 

Transactions and Concurrency

 

 321

The 

READ COMMITTED SNAPSHOT

 Isolation Level

The READ COMMITTED SNAPSHOT isolation level is also based on row versioning. It differs from 

the SNAPSHOT isolation level in that instead of providing a reader with the last committed version 

of the row that was available when the transaction started, a reader gets the last committed ver-

sion of the row that was available when the statement started. The READ COMMITTED SNAPSHOT 

isolation level also does not detect update conflicts. This results in logical behavior very similar to 

the READ COMMITTED isolation level, except that readers do not acquire shared locks and do not 

wait when the requested resource is exclusively locked.

To enable the use of the READ COMMITTED SNAPSHOT isolation level in an on-premises SQL Server 

database (the behavior is enabled by default in SQL Database), you need to turn on a different data-

base flag than the one required to enable the SNAPSHOT isolation level. Run the following code to 

enable the use of the READ COMMITTED SNAPSHOT isolation level in the TSQL2012 database.

ALTER DATABASE TSQL2012 SET READ_COMMITTED_SNAPSHOT ON;

Note that for this code to run successfully, this connection must be the only connection open to 

the TSQL2012 database.

An interesting aspect of enabling this database flag is that unlike with the SNAPSHOT isolation 

level, this flag actually changes the meaning, or semantics, of the READ COMMITTED isolation level 

to READ COMMITTED SNAPSHOT. This means that when this database flag is turned on, unless you 

explicitly change the session’s isolation level, READ COMMITTED SNAPSHOT is the default.

For a demonstration of using the READ COMMITTED SNAPSHOT isolation level, open two connec-

tions. Run the following code in Connection 1 to open a transaction, update the row for product 2, 

and read the row, leaving the transaction open.

USE TSQL2012; 

 

BEGIN TRAN; 

 

  UPDATE Production.Products 

    SET unitprice += 1.00 

  WHERE productid = 2; 

 

  SELECT productid, unitprice 

  FROM Production.Products 

  WHERE productid = 2;

You get the following output, indicating that the product’s price was changed to 20.00.

productid   unitprice 

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

2           20.00

www.it-ebooks.info

---

322  

Microsoft SQL Server 2012 T-SQL Fundamentals

In Connection 2, open a transaction and read the row for product 2, leaving the transaction open.

BEGIN TRAN; 

 

  SELECT productid, unitprice 

  FROM Production.Products 

  WHERE productid = 2;

You get the last committed version of the row that was available when the statement started 

(19.00).

productid   unitprice 

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

2           19.00

Run the following code in Connection 1 to commit the transaction.

COMMIT TRAN;

Now run the code in Connection 2 to read the row for product 2 again, and commit the transaction.

  SELECT productid, unitprice 

  FROM Production.Products 

  WHERE productid = 2; 

 

COMMIT TRAN;

If this code had been running under the SNAPSHOT isolation level, you would have gotten a price 

of 19.00; however, because the code is running under the READ COMMITTED SNAPSHOT isolation 

level, you get the last committed version of the row that was available when the statement started 

(20.00) and not when the transaction started (19.00).

productid   unitprice 

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

2           20.00

Recall that this phenomenon is called a non-repeatable read, or inconsistent analysis.

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

UPDATE Production.Products 

  SET unitprice = 19.00 

WHERE productid = 2;

Close all connections. If you ran this demo in an on-premises SQL Server instance, open a new con-

nection and run the following code to disable the isolation levels that are based on row versioning in 

the TSQL2012 database.

ALTER DATABASE TSQL2012 SET ALLOW_SNAPSHOT_ISOLATION OFF; 

ALTER DATABASE TSQL2012 SET READ_COMMITTED_SNAPSHOT OFF;

www.it-ebooks.info

---