Programming4us
         
 
 
SQL Server

SQL Server 2008 : Isolation Level Explained

12/3/2010 7:29:33 PM
Isolation levels are how SQL Server decides what level of locking to take when working with data. There are five isolation levels available for your transactions in SQL Server 2008: READ UNCOMMITED, READ COMMITED, REPEATABLE READ, SNAPSHOT, and SERIALIZABLE. Only one isolation level can be set for your connection, and that isolation level persists until the connection to SQL Server is broken or the isolation level is explicitly changed by using the SET TRANSACTION ISOLATION LEVEL statement.

If a stored procedure has a SET TRANSACTION ISOLATION LEVEL statement within it and the calling code uses a different isolation level, then the isolation level will be changed, and will revert back to the calling code isolation level upon completion of the stored procedure. If table locking hints are included within a query, those locking hints will override the default locking hints of the isolation level.

The READ UNCOMMITTED isolation level is similar to the WITH (NOLOCK) hint in that it allows you to perform dirty reads of the tables and pages that you are reading even if other transactions have modified those records. This is the least restrictive of the isolation levels. You can protect yourself from locking contention while preventing dirty reads by setting the READ_COMMITTED_SNAPSHOT option of the database to true, and using the READ COMMITTED isolation level, or by using the SNAPSHOT isolation level.

The READ COMMITTED isolation level protects the transactions from dirty reads. This is the default isolation level of SQL Server. The behavior of this isolation level will depend on the value of the READ_COMMITTED_SNAPSHOT setting of the database. If this setting is disabled then this isolation level will not allow statements to read pages that are locked for update by other transactions. If this setting is enabled then this isolation level will allow statements to read the prior version of the pages that are locked since backup pages will be written to the TEMPDB database while they are locked for update. If the setting is enabled and you wish to revert to the disabled locking behavior then you can use the WITH(READCOMMITTEDLOCK) table hint within your command.

The REPEATABLE READ isolation level is the next restrictive isolation level within SQL Server. When using this isolation level statements cannot read data that has been modified but not committed by other transactions. It also prevents data that has been selected by other transactions from being updated. The prevention of other transactions’ reading data is controlled by shared locks on the rows or data pages that are read, which prevents an update statement from being able to take its locks on those rows or data pages.

The SNAPSHOT that is the newest isolation level in SQL Server requires that all data used by the transaction will be the same version of the transaction at the beginning of the transaction as it is at the end of the transaction. This is done by SQL Server taking copies of the data it needs to work with and placing those copies of the data into the TEMPDB database so that other transactions using the READ COMMITTED isolation level can read them instead of the original data pages from within the user database. Before you can use the SNAPSHOT isolation level you must enable the ALLOW_SNAPSHOT_ISOLATION setting on the database. If your transaction will span more than one database, then this setting must be enabled on all the databases within the transaction or the transaction will return an error message. This isolation level was introduced with Microsoft SQL Server 2005 and cannot be used on SQL Server 2000 and below.

New & Noteworthy...: A New Isolation Level

The snapshot isolation isn’t new in SQL Server 2008, but it was new in SQL Server 2005. The addition of a new isolation level is a very large event. Microsoft has had only four transaction isolation levels since the Microsoft code base broke from the Sybase code base back in SQL Server 4.2 days. To this day Sybase still has only four transaction isolation levels.


The SERIALIZABLE isolation level is the most restrictive of the isolation levels. When using this isolation level no access to the data being used by this transaction is allowed, no matter the isolation level of the other transaction. No transaction may insert a value into the table that falls within the key range of the values being locked by the SERIALIZABLE transaction. This guarantees that any select statements that are performed within the database will return the exact same data every time they are run within the context of the transaction.

Tip

Use the SET TRANSACTION ISOLATION LEVEL statement to change the isolation level between the various isolation levels.


It is important to note that user-defined functions (UDFs) and CLR user-defined types (UDTs) cannot change the isolation of the current session via the SET TRANSACTION ISOLATION LEVEL command. In order to change the isolation level for the scope of these commands you must set the isolation level, then call the UDF or UDT, and then set the isolation level back.

If you are using the BULK INSERT statement, the BCP command-line command, and you are using the .NET bulk data loading methods, these commands will block and be blocked by transactions that are using SNAPSHOT, READ UNCOMMITTED, and READ COMMITTED (when row versioning is being used).

In addition to changing the isolation level through SET TRANSACTION ISOLATION LEVEL you can edit the query options within SQL Server Management Studio. You can do this by right–clicking on the white space in the query window and selecting Query Options from the context menu. On the Execution | Advanced page you can adjust the Transaction Isolation Level as shown in Figure 1.

Figure 1. Query Options


You should be very careful when adjusting this setting. When you change this setting it affects only the current session, which could change the way the locks are done when running the statements in this state, and in other states.

Other -----------------
- SQL Server 2008 : ACID
- SQL Server 2008 : Transactions Explained
- SQL server 2008 : Handling Errors (part 3)
- SQL server 2008 : Handling Errors (part 2)
- SQL server 2008 : Handling Errors (part 1)
- SQL Server 2008 : Indexing for Performance - Putting It All Together (part 5) - Filtered Indexes
- SQL Server 2008 : Indexing for Performance - Putting It All Together (part 4) - Indexing JOIN Criteria
- SQL Server 2008 : Indexing for Performance - Putting It All Together (part 3) - Covering Your Queries
- SQL Server 2008 : Indexing for Performance - Putting It All Together (part 2) - Clustered Index Seeks
- SQL Server 2008 : Indexing for Performance - Putting It All Together (part 1)
- SQL Server Integration Services : Logged and Nonlogged Operations
- SQL Server Integration Services : Using bcp (part 5)
- SQL Server Integration Services : Using bcp (part 4)
- SQL Server Integration Services : Using bcp (part 3)
- SQL Server Integration Services : Using bcp (part 2) - Fundamentals of Exporting and Importing Data
- SQL Server Integration Services : Using bcp (part 1)
- SQL Server Integration Services : Connection Projects in Visual Studio
- SQL Server Integration Services : The Package Execution Utility (part 3) - The dtutil Utility
- SQL Server Integration Services : The Package Execution Utility (part 2) - Running Packages
- SQL Server Integration Services : The Package Execution Utility (part 1)
 
 
Most View
- Windows Azure Storage : Account Operations
- Windows 7: Working with Device Security Policies
- SharePoint 2010 : Get Started with Your Personal Site
- SharePoint 2010 : Content Management - Configuring content deployment
- Implementing Windows Vista’s Internet Security and Privacy Features (part 10) - Working with Email Safely and Securely - Obtaining Another Person’s Public Key
- Windows Server 2008: Using Event Viewer for Logging and Debugging (part 1)
- Windows Phone 7 : Adding GPS Info to Pictures
- Windows7: Managing Network Connections (part 2) - Enabling Automatic IP Addressing
- Sysinternals License Information
- Lists and Libraries in Windows SharePoint Services 3.0 (part 1)
Top 10
- Implementing Edge Services for an Exchange Server 2007 Environment : Utilizing the Basic Sender and Recipient Connection Filters (part 3) - Configuring Recipient Filtering
- Implementing Edge Services for an Exchange Server 2007 Environment : Utilizing the Basic Sender and Recipient Connection Filters (part 2)
- Implementing Edge Services for an Exchange Server 2007 Environment : Utilizing the Basic Sender and Recipient Connection Filters (part 1)
- Implementing Edge Services for an Exchange Server 2007 Environment : Installing and Configuring the Edge Transport Server Components
- What's New in SharePoint 2013 (part 7) - BCS
- What's New in SharePoint 2013 (part 6) - SEARCH
- What's New in SharePoint 2013 (part 6) - WEB CONTENT MANAGEMENT
- What's New in SharePoint 2013 (part 5) - ENTERPRISE CONTENT MANAGEMENT
- What's New in SharePoint 2013 (part 4) - WORKFLOWS
- What's New in SharePoint 2013 (part 3) - REMOTE EVENTS