Browse DevX
Sign up for e-mail newsletters from DevX


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

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

Costs and Benefits of Statement-Level Snapshots
Like the SNAPSHOT isolation level, one of the major benefits of the database option READ_COMMITTED_SNAPSHOT is the elimination of blocking combined with reading only committed data. A Key Benefit: Consistency
There is another subtle but arguably the most 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.

For example, suppose your SELECT statement sums up certain values from one or more tables. You are assured by READ_COMMITTED_SNAPSHOT that the summary values will be based on a snapshot of the data consistent at the starting time of the SELECT statement. Even though the underlying data may change during the execution of the SELECT statement, those changes are not read by the SELECT statement because only the version of the data current at the start time of the query is read. Costs of Statement-Level Snapshots
Like the SNAPSHOT isolation level, the versioned rows for READ_COMMITTED_SNAPSHOT are kept in tempdb. Enabling this option database-wide implies that a considerable amount of activity in tempdb will be required to store and maintain those versions. This activity could potentially exceed that of the SNAPSHOT isolation level because the versioning is now applied database-wide. As a result, you must plan for increased space usage and increased I/O demands on the tempdb database.

SQL Server 2005 Beta 2 gives you two new uses for row versioning that can significantly increase database concurrency: the SNAPSHOT isolation level and the READ_COMMITTED_SNAPSHOT database option. You can use these two strategies independently. The SNAPSHOT isolation level applies only to transactions, and has no effect on the default READ COMMITTED isolation level. By way of contrast, the statement-level READ_COMMITTED_SNAPSHOT strategy is a database option that changes the behavior of the READ COMMITTED isolation level. It eliminates the blocking caused by SELECT statements, and does not allow reading of uncommitted data.

Both options are intriguing, but it may be that the statement-level READ_COMMITTED_SNAPSHOT approach contains subtle advantages. Your SELECT statements are guaranteed a consistent view of committed data without any changes to Transact-SQL coding. Table 1 illustrates how the READ_COMMITTED_SNAPSHOT option can eliminate deadlocking without any change to your code. Because SELECT statements are no longer blocked by transactions that change data, deadlocks caused by the interaction of shared locks and exclusive locks are eliminated. Because the UPDATE statements apply to different rows, they do not conflict. As the SELECT statements do not require any shared locks, no blocking between the transactions occurs, and the deadlock does not occur.

Ron Talmage is a principal mentor with Solid Quality Learning, and also heads Prospice LLC, a database consulting firm based in Seattle. He is a SQL Server MVP, PASS newsletter co-editor, and current president of the Pacific Northwest SQL Server Users Group. He also writes for SQL Server Professional and SQL Server Magazine.
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