Browse DevX
Sign up for e-mail newsletters from DevX


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

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

A Snapshot Isolation Backgrounder
As explained in Part I, the SNAPSHOT isolation level is one of two new ways that SQL Server 2005 Beta 2 allows you to govern database concurrency. In prior versions of SQL Server, the only way you could manage concurrency was by manipulating the standard four isolation levels, each of which had some disadvantages.

The lowest isolation level, READ UNCOMMITTED, maximizes concurrency because SELECT statements do not issue any locks on the data, shared or otherwise. It minimizes blocking due to shared locks, but it also allows your application to read uncommitted and therefore potentially inconsistent "dirty" data, data changes that may be rolled back.

The highest two isolation levels, REPEATABLE READ and SERIALIZABLE, have the least concurrency because shared locks are not released until the end of a transaction, and for SERIALIZABLE, shared locks are also issued on key ranges. These higher isolation levels result in the greatest chance of blocking, but also return the most consistent views of the data for transactions. (For more information about dirty reads, non-repeatable reads, and phantoms, see the section titled "Getting Started: SQL Server Isolation Levels in a Nutshell" in Part I.)

SQL Server's default isolation level, READ COMMITTED, takes a sort of middle way: concurrency is limited because SELECT statements do issue shared locks, and therefore may block or be blocked by transactions requiring exclusive locks. But those shared locks are released as soon as the query in a transaction is finished, and are not held to the end of a transaction. Although no uncommitted data is read in the READ COMMITTED isolation level, it is still possible for transactions to experience non-repeatable reads and phantoms. Blocking may occur, but much less often than with the higher REPEATABLE READ and SERIALIZABLE isolation levels.

The Snapshot Isolation Level
Enter the SNAPSHOT isolation level. As a true isolation level, SNAPSHOT is used within transactions and set at the session level with the SET TRANSACTION ISOLATION statement. During the course of a transaction, any changes to data that were read during the transaction are versioned: the original unchanged copy is stored in the versioning system in tempdb. During the remainder of the transaction, any SELECT statements read changed data from the version store and unchanged data directly from the database tables.

The great benefit of the new SNAPSHOT isolation level is that transactions no longer block each other because of SELECT statements. Transactions may still undergo blocking because of data change commands such as INSERT, UPDATE, and DELETE, because those commands still obtain locks. With SNAPSHOT isolation, you get the consistency of READ COMMITTED, and the concurrency of READ UNCOMMITTED. However, in order to use SNAPSHOT isolation, you must begin explicit transactions with the SET TRANSACTION ISOLATION LEVEL command. The SNAPSHOT isolation level has no application to SELECT statements outside explicit transactions.

Statement-Level Snapshot Isolation
The READ_COMMITTED_SNAPSHOT option extends the benefits of SNAPSHOT isolation level to all SELECT statements in a database by changing the way the READ COMMITTED isolation level behaves. It is a database option, not a session level setting like the SNAPSHOT isolation level. However, you do have to enable it with ALTER DATABASE, as in the following command:


And that's it: no other commands are required. (But note: there can be no other users in the database when you execute the ALTER DATABASE command with the READ_COMMITTED_SNAPSHOT option.) Automatically, the READ COMMITTED isolation level starts using row versioning, and SELECT statements no longer take shared locks.

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