Isolation Levels and the Effects on Select Statements

Database locking is a fundamental concept in SQL Server that every Database Administrator and Database Developer should understand.  SQL Server has a pessimistic locking model because it was built for an OLTP environment and assumes that updates will occur frequently.  For a developer, this is important to understand in terms of concurrency: how many users can access the same data simultaneously.  In some cases, you may want concurrency to be high (many people can access the same data at the same time); in other cases you may want concurrency to be low (only one person can access the data at any given time).  A developer must know what level of concurrency is needed, the effects that concurrency has in the database (locks) and how concurrency affects user activity and processing.  A DBA must also understand concurrency, the locking that occurs in a database, how locking can lead to blocking and how to troubleshoot blocking issues.

The isolation level for a database connection directly affects the type of lock that is taken for a read operation, and therefore affects concurrency.  I bring this up because I’ve seen people inadvertently create blocking in a database because they do not understand properties for their database connection.  I don’t want to repeat Books Online, but let’s take a minute to review the more common isolation levels in SQL Server.

READ COMMITTED

This is the default isolation level in SQL Server.  When you initiate any connection to SQL Server (e.g. from Management Studio), this isolation level will be used.  In a READ COMMITTED isolation level:

• If data is being modified by a transaction, any subsequent select statement that occurs before the modification is complete will wait until the transaction commits or rolls back to read the data. (Note: this statement has been modified since the original post, please see Isolation Levels and the Effects on Select Statements, Part II for more details.)
• Select statements create shared locks which are released as soon as the data is read. If a shared lock exists, data cannot be modified until the lock is released (for more information see Lock Modes in BOL).  Under the right circumstances, this can lead to blocking.

Lead blocker

READ UNCOMMITTED

This the most optimistic isolation level in SQL Server, and in order to utilize it, the SET TRANSACTION ISOLATION LEVEL statement must be executed when a connection is made.  In a READ UNCOMMITTED isolation level:

• If data is being modified by a transaction, any subsequent select statement that occurs before the modification is complete will read the modified copy of the data. This is known as a dirty read.

Note: You can create the same behavior by using the NOLOCK hint in queries.  That is not something I would recommend, and NOLOCK is another debate for another time.

A dirty read

SNAPSHOT

This isolation level was added in SQL Server 2005, and is considered a good hybrid of READ COMMITTED and READ UNCOMMITTED as it requires that data that is read during a select statement will be the same value that existed when the transaction started.  In a SNAPSHOT isolation level:

• If an insert, update or delete statement does not commit before the select statement begins, then the output from the select will show the original values.
• Locks are not taken when reading data (unless a database is being recovered).

This isolation level requires setting the ALLOW_SNAPSHOT_ISOLATION database option to ON.

What to remember

As a DBA, it’s important to recognize that when you open Management Studio (SSMS) and open a query window to a database and start to query that database, you can affect the type of locks being taken.  If you run a select statement which takes 10 minutes to return data, you may block other activity in the database.  If you’re troubleshooting a problem, you might make it worse.  If you’re just looking at data, or writing some reports against production, you might create a problem.

What you can do

If you’re writing reports, don’t do it against production.  A good DBA restores backups on a regular basis.  Restore a recent backup of production and write reports against it.  Don’t have the space to restore production?  Utilize a test, development, QA or UA environment and write the reports there.  If the database is not comparable in size and/or data distribution to production, at some point you will need to do performance testing so you can make sure the reports are optimized.  At that time, you really want to find the space to restore that recent production backup, even if it’s only available to you for a couple days of testing.  In six months, you do not want to explain why the reports you wrote are causing performance issues in production.

If you’re just selecting data to troubleshoot or look at data, and are not concerned about dirty reads, you can change the isolation level in SSMS.  You must do this each time you open a new query window by executing

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

For subsequent queries issued in that session, the isolation level will be READ UNCOMMITTED. Again, realize that this must be set for each new query window you open.

Alternatively, you can change the default isolation for SSMS connections. I don’t recommend doing this for every SSMS installation in your environment.  I would only change this on the SSMS installed on my own machine, to which only I access, so that I am the only person affected if I start to see unexpected results.

Within SSMS, select Tools | Options. Then select Query Execution | SQL Server | Advanced. In the SET TRANSACTION ISOLATION LEVEL drop down, change the value to READ UNCOMMITTED. Select OK. All new sessions will have an isolation level of READ UNCOMMITTED.

Changing default isolation in SSMS

What about Oracle?

As you may or may not know, the application which I support can also run on Oracle.  There are numerous differences between SQL Server and Oracle, including isolation levels.  Oracle has an optimistic locking model because it assumes that users spend more time reading data than modifying it.  Oracle utilizes two isolation levels to manage data concurrency and consistency: READ COMMITTED and SERIALIZABLE (see Data Concurrency and Consistency for more information).

The default isolation level is READ COMMITTED and when you initiate any connection to Oracle (e.g. from SQLPlus), this isolation level will be used.  In the READ COMMITTED isolation level:

• When a select statement is issued in Oracle, there are no locks taken on the rows being read.

This behavior is very different than SQL Server. A select statement in Oracle should never block a data modification, and a data modification should not block a select statement either.  Within the Oracle community, the phrase is simply: Readers don’t block writers and writers don’t block readers.  In a SQL Server database, you can create the same behavior by using the SNAPSHOT isolation level.

It's Oracle, no one is bothering anyone else

As such, I have seen only two blocking issues in Oracle in all the years I’ve worked with it, but I’ve seen numerous blocking issues in SQL Server.  In Oracle, both issues occurred because the connection was lost during the middle of an update, and the lock taken by the update blocked subsequent updates to that same data.

Additional resources

In this post I wanted to focus on what a DBA should remember when connecting to a production database, as it relates to isolation level.  I have merely touched upon the impact of isolation level, and if you want to read more, I highly recommend Kendra Little’s isolation levels resource page for more information.