Browse DevX
Sign up for e-mail newsletters from DevX


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

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

Working Through an Example: The Default Behavior
To see the READ_COMMITTED_SNAPSHOT database option in action, let's contrast it with the default READ COMMITTED behavior. Assuming you have installed the AdventureWorks sample database with SQL Server 2005 Beta 2, open a new query window in the master database, and call it Query Window 1. You can detect whether the database has already enabled the READ_COMMITTED_SNAPSHOT option by executing the following query in Query Window 1:

There is a subtle and arguably more important benefit for the statement-level READ_COMMITTED_SNAPSHOT option: because the data returned from a SELECT statement is a consistent snapshot of the data as of the beginning of the statement, you are assured that the SELECT statement will return consistent aggregate values.

SELECT is_read_committed_snapshot_on FROM sys.databases WHERE name= 'AdventureWorks'

This statement queries the catalog view sys.databases in the master database. This catalog view replaces direct queries of the sys.databases table, and provides much more information. If the above query returns a 1, the database's READ COMMITTED isolation level uses row versioning; otherwise it will not.

Let's assume your query returns a 0. (If it doesn't, issue:


in Query Window 1 to turn READ_COMMITTED_SNAPSHOT off.)

Now let's show how an update can block a read. Open a new query window in Management Studio, and call it Query Window 2, choose the AdventureWorks database, and execute the following:

BEGIN TRANSACTION UPDATE AdventureWorks.Person.StateProvince SET IsOnlyStateProvinceFlag = 1 WHERE IsOnlyStateProvinceFlag = 0

For the time being, do not issue a COMMIT or ROLLBACK.

In a new third query window, which you call Query Window 3, select the AdventureWorks database and execute the following:

SELECT * FROM Person.StateProvince

Figure 1: You can use the legacy sp_lock stored procedure to see that SPID 57 is in a WAIT state, requesting a shared lock on the key value of a clustered index (a row of a clustered table).
Notice that your SELECT query is blocked because it is attempting to issue shared locks on the table and cannot. You can see this in a couple of ways. First, find out what the SPID number is that is blocked. You can find this on the bottom of the Query Window 3 in parentheses next to your SQL user name. Then you can issue the following in Query Window 1 to see the locks against that SPID:

EXEC sp_lock 57

Your output should look something like that in Figure 1. Notice that the status of the attempted KEY lock is WAIT, with a mode of S (for Shared), and the IndId (index ID) is 1.

The output tells you that SPID 57 is in a WAIT state while attempting to gain a shared lock on a key value, in this case the key of a clustered index.

However, using sp_lock is a legacy method of examining lock data, and in SQL Server 2005 Beta 2, you should really use the dynamic management view called sys.dm_tran_locks. This system view replaces sp_lock and returns much more data.

Figure 2: In SQL Server 2005, the dynamic management view sys.dm_tran_locks replaces sp_lock and provides similar information.
To inspect SPID 57, execute the following query in Query Window 1:

SELECT resource_type , request_spid , request_mode , request_status FROM sys.dm_tran_locks WHERE request_spid = 57

The full results from sys.dm_tran_locks have quite a number of columns, but the columns returned by the above query return sufficient information to see that SPID 57 is in a WAIT state, attempting to get a shared lock, as shown in Figure 2. (Unfortunately, at this time sys.dm_tran_locks does not show the index ID of the lock.)

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