SQL Server 2012 : Isolation Levels (part 2) - Repeatable Read Isolation Level,Snapshot Isolation Level, Isolation Levels in ADO.NET

3. Repeatable Read Isolation Level

As the name suggests, the repeatable read isolation level prevents nonrepeatable reads. It does so by placing locks on the data that was used in a query within a transaction. As you might expect, you pay a higher price in terms of concurrent transactions blocking each other, so you should use this isolation level only when necessary. The good news, however, is that a concurrent transaction can add new data that matches the WHERE clause of the original transaction. This is because the first transaction will place a lock only on the rows it originally read into its result set. In other words, a transaction using this isolation level acquires read locks on all retrieved data but does not acquire range locks.

If you examine this pattern closely, you’ll see that although nonrepeatable reads are avoided when using this isolation level, phantom reads can still occur. They can occur under the following circumstances:

1. Transaction 1 begins.

2. Transaction 1 reads all rows with, say, TestColumn = 100.

3. Transaction 2 begins.

4. Transaction 2 inserts a new row with TestID = 2, TestColumn = 100.

5. Transaction 2 commits.

6. Transaction 1 runs an UPDATE query and modifies TestColumn for the rows where TestColumn = 100. This also ends up updating the row that transaction 2 inserted.

7. Transaction 1 commits.

Because shared locks are not released until the end of the transaction, concurrency is lower than when using the read committed isolation level, so care must be taken to avoid unexpected results.

4. Serializable Isolation Level

A transaction running at the serializable isolation level will not permit dirty reads, phantom reads, or nonrepeatable reads. This isolation level places the most restrictive locks on the data being read or modified, keeping your data perfectly clean. This might sound like an isolation level that gives you perfect isolation behavior, but there is a good reason why you should seldom use this isolation level. In a sense, this is the perfect transaction, but transactions will block other running transactions, thereby affecting concurrent performance or even creating deadlocks. Thus even if this transaction will keep your data perfectly clean, it will severely affect system performance. In most practical situations, you can get away with a lower isolation level.

5. Snapshot Isolation Level

In all of the isolation levels described earlier, it seems that concurrent performance is traded for logical sanctity of data. Because a transaction locks the data it is working on, other transactions that attempt to work with the same data are blocked until the first transaction commits or rolls back.

Of course, the traditional way of getting around this problem is to allow dirty reads (and hence incorrect data) or to simply reduce the duration of transactions. But neither of these solutions allows you to read logically consistent data while offering nonblocking concurrent behavior.

Application architectures frequently present circumstances in which even the smallest transactions become a problem or transactions end up modifying so much data that their duration cannot be kept small. To get around this issue, a new isolation level was introduced in SQL Server 2005: the snapshot isolation level. This isolation level gives you consistent reads without blocking.

Transactions running under the snapshot isolation level do not create shared locks on the rows being read. In addition, repeated requests for the same data within a snapshot transaction guarantee the same results, thus ensuring repeatable reads without any blocking. This sounds like the best of both worlds—the responsiveness of read uncommitted combined with the consistency of repeatable read. However, you pay a price.

This nonblocking, repeatable read behavior is made possible by storing previously committed versions of rows in the tempdb database. As a result, other transactions that were started before the write in the current transaction and that have already read the previous version will continue to read that version. Because the previous version is being read from tempdb, the write can occur in a nonblocking fashion and other transactions will see the new version. The obvious problem, of course, is the increased overhead on the tempdb database. For this reason, SQL Server requires you to enable the snapshot isolation level before you can use it. You shouldn’t arbitrarily enable snapshot isolation on databases. But after testing, if you decide that your database needs this isolation level, you can enable it by using the following statement:

ALTER DATABASE MyDB
 SET ALLOW_SNAPSHOT_ISOLATION ON

As with all isolation levels, once you enable snapshot isolation for a database, you can use it on individual connections by using the SET TRANSACTION ISOLATION LEVEL statement, as follows:

SET TRANSACTION ISOLATION LEVEL SNAPSHOT

6. Read Committed Snapshot Isolation Level

Snapshot isolation prevents readers from being blocked by writers by providing readers with data from a previously committed version. Over the duration of the transaction, you are thus assured of repeatable reads. However, this method of ensuring a repeatable read incurs additional overhead and bookkeeping for the SQL Server Database Engine that might not be necessary in all situations. Thus, SQL Server offers a slight modification to the read committed isolation level that provides nonrepeatable reads over the duration of the transaction that are not blocked by transaction writers. This modification is called the read committed snapshot isolation level. This isolation level guarantees consistency of the data over the duration of a read query within a transaction but not over the entire transaction that holds the reader. The obvious advantage over read committed snapshot as compared to read committed is that your readers do not get blocked. When they request data, they are offered either a previous state of data (before any write operations) or the new state of data (after write operations), depending on the state of other concurrently running transactions, but they are never required to wait until other concurrent transactions release their locks on the data being requested.

To use the read committed snapshot isolation level, you must first enable it at the database level by using the following T-SQL command:

USE master
GO

ALTER DATABASE MyDB
 SET READ_COMMITTED_SNAPSHOT ON

Notice the USE master statement to switch away from the MyDB database. The ALTER statement waits until there are no active connections to MyDB (if you still have open query windows with active connections to MyDB from an earlier example, the ALTER statement will hang until you close those windows). Once you have enabled the read committed snapshot isolation level on a database, all queries using the read committed isolation level will exhibit snapshot-like behavior. Although this isolation level will give you snapshot-like behavior, you will not be able to perform repeatable reads over the duration of a transaction.

Note

When FILESTREAM was first introduced in SQL Server 2008, neither snapshot isolation nor read committed snapshot isolation levels could be used with FILESTREAM-enabled databases. That limitation was removed with the release of SQL Server 2008 R2, and FILESTREAM is now compatible with snapshot isolation.

7. Isolation Levels in ADO.NET

There is a slight mismatch between the isolation levels defined in ADO.NET (which calls SQL Server from your .NET application), compared with the isolation levels in SQL Server itself. This is because ADO.NET was not written exclusively for SQL Server, and is a generic data access technology that supports Oracle and other databases as well.

Note

The isolation levels defined in ADO.NET 2.0 under the System.Data.IsolationLevel enumeration are as follows:

• Chaos Pending changes from more highly isolated transactions cannot be overwritten. This setting is not supported in SQL Server or Oracle.

• ReadUncommitted Similar to read uncommitted in SQL Server, this level means that no shared locks are placed and no exclusive locks are honored.

• ReadCommitted As with read committed in SQL Server, shared locks are held while the data is being read by the transaction. This avoids dirty reads, but you might still get nonrepeatable reads and phantom reads.

• RepeatableRead Shared locks are placed on all data that is used in the predicate (criterion) of the query. Again, as with repeatable read in SQL Server, dirty reads and nonrepeatable reads are not possible, but phantom reads are.

• Snapshot Similar to the snapshot isolation level in SQL Server, this isolation level provides a snapshot of earlier data while offering repeatable reads with nonblocking selects. Do not confuse this level with the read committed snapshot isolation level in SQL Server, which must be enabled at the database level.

• Serializable This can be considered an ideal transaction type to use, in which exclusive locks are placed on data. This prevents other users from reading or modifying the data. Keep in mind that there are always trade-offs, and exclusive locks should not be held for long periods of time.

• Unspecified This is a catchall isolation level for databases that support isolation levels not covered by the other choices or for scenarios in which the isolation level cannot be accurately determined.

The System.Data.IsolationLevel enumeration can be used with both implicit and explicit ADO.NET transactions. 

Setting the Isolation Level for Explicit ADO.NET Transactions

You can specify an isolation level for an explicit ADO.NET transaction as a parameter to the BeginTransaction method. For instance, the following code snippet begins a transaction with the ReadUncommitted isolation level:

SqlTransaction tran = conn.BeginTransaction(IsolationLevel.ReadUncommitted);

In this example, any SqlCommand with its Transaction property set to tran will not honor any exclusive locks and will let you perform dirty reads on data being held by other transactions.

Setting the Isolation Level for Implicit ADO.NET Transactions

Implicit ADO.NET transactions are recommended over explicit ADO.NET transactions. To set the isolation level for implicit ADO.NET transactions, create a new TransactionOptions object, and set its IsolationLevel property to the desired enumeration value. Then pass the TransactionOptions object to the constructor for the TransactionScope object. For example:

var tso = new TransactionOptions();
tso.IsolationLevel = IsolationLevel.ReadUncommitted;
using (var ts = new TransactionScope(tso))
{
   // ... update data
   // ... update data
   ts.Complete();
}