Browse DevX
Sign up for e-mail newsletters from DevX


Snapshot Isolation in SQL Server 2005, Part II : Page 4

In addition to the transaction-based Snapshot isolation level, SQL Server 2005 Beta 2 also introduces a statement-level variation of the Read Committed isolation level called Read_Committed_Snapshot.




Building the Right Environment to Support AI, Machine Learning and Deep Learning

The Example with Statement-Level Snapshot Enabled
Now let's try the same thing in the READ_COMMITTED_SNAPSHOT mode. In Query Window 2, roll back the transaction:


Figure 3: With READ_COMMITTED_SNAPSHOT enabled, even though the underlying data has been changed in an uncommitted transaction, a SELECT statement is not blocked.
Now change the database contexts of Query Windows 2 and 3 to be the master database, so that the ALTER DATABASE in Query Window 1 runs OK. Execute the following to set the option on:


Now return to Query Window 2, select the AdventureWorks database, and re-execute the transaction with the UPDATE statement. Then in Query Window 3, execute the SELECT statement. Note that this time it is not blocked; the SPID has no locks and no WAIT state. Further, note that the IsOnlyStateProvinceFlag column shows values of both 0 and 1, which is correct, because the transaction has not been committed, as you can see in Figure 3.

Figure 4: The uncommitted transaction shows the changes to the data.
The SELECT statement has returned data from the versioning system for those rows that have changed values, rather than placing any shared locks on the table. The SELECT statement has not been blocked by the locks on the table due to the UPDATE statement. To verify that the data has really changed, in Query Window 2, execute

SELECT * FROM Person.StateProvince

You'll now see the values of all ones in the IsOnlyStateProvince column, as shown in Figure 4.

The READ_COMMITTED_SNAPSHOT setting has increased the concurrency of the system because the SELECT statement does not need shared locks to read consistent and committed data. Put another way, you've gotten the concurrency increase equivalent to issuing a NOLOCK hint (or the READ UNCOMMITTED isolation level) without reading any uncommitted data and without leaving the READ COMMITTED isolation level.

Figure 5: You can use the sys.dm_tran_version_store() system function to obtain a list of the rows currently being versioned.
Monitoring the Row Versions
You can inspect the row versions using other dynamic management views. In the example using READ_COMMITTED_SNAPSHOT, you can use the sys.dm_tran_version_store() dynamic management function to observe the rows that have been versioned due to the transaction in Query Window 2, as shown in Figure 5.

Comment and Contribute






(Maximum characters: 1200). You have 1200 characters left.



Thanks for your registration, follow us on our social networks to keep up-to-date