Isolation Levels 101: Concurrency Issues

The other day I had a client complaining about slowness, queries erroring out, blocking and processes stopping; you know the usual complaints. So, like any other DBA I pulled out my handy dandy toolbox of queries to determine exactly what was going on with the server.

Upon reviewing their current running sessions, I noticed that the Isolation Level for a simple UPDATE transaction was “Serializable” and all other transactions were “Read Committed”. And this Serializable transaction was blocking all other transactions.

Now, let me preference the rest of this blog series with: in all my SQL experiences, I have yet had to change the isolation levels on a transaction or database level. Which means, I have never studied this topic in depth. So, this got me thinking, what are the different isolation levels and why would anyone ever change from the default isolation, which is “Read Committed”?

Typically, when I attempt to learn something, I like to write it down. Because this is a lot of information, I am going to present my learning in 3 parts: Concurrency Issues and how SQL works, Isolation Levels and how they help resolve Concurrency issues, and some helpful scripts to test and figure all this out.

So, let’s start at the beginning. The purpose of an Isolation level is to provide SQL server with a mechanism to protect data against specific concurrency problems when multiple CRUD (create, read, update, delete) operations occur on the same data.

Concurrency Problems

Data is intended to be read, modified, created or deleted over time. Most databases have multiple connections doing all these things at the same time. Managing all these concurrent connections SQL server must decide which statements gets precedence. SQL typically operates on a “first-in, first-out” method of handling queries. But what those queries do can determine the fate of queries in line to be executed. And these decisions can cause data concurrency issues. The following concurrency “phenomena” have been identified by ISO/ANSI standard for SQL-92 as possible results for concurrent transactions.

Lost Updates

It is possible for two or more transactions to modify the same row. While the subsequent transactions are reading the data, the 1st transaction commits its changes. Then the subsequent transaction makes its changes and commits, thereby possibly losing the changes committed by the 1st transaction. In this scenario, the last transaction always wins.

Dirty Read

A dirty read is when a SELECT statement will read against a table that is currently being modified and not yet committed to the database. By default, SQL Server will protect against this on all isolation levels, except for Read Uncommitted or by using NOLOCK in your queries.

Non-Repeatable Read

This form of concurrency problem can happen when a specific dataset (using the same WHERE clause) is accessed multiple times within the same transaction and some of the rows in the dataset are updated between the 1st and 2nd access. This will product two different result sets.

Phantom Reads

Very similar to non-repeatable reads, a phantom read concurrency is when two queries in the same transaction, against the same table with the same WHERE clause produces two different result sets because a 3rd query has changed data between the first and second SELECT statements.

How to combat these Concurrency Problems?

For most DBAs, the integrity of their data is, or should be, the most important aspect of their job. It quite literally can be a “job killer”. Fortunately for us, Microsoft has built SQL Server with several levels to isolate the data during CRUD operations to ensure these concurrency problems are eliminated. These Isolation Levels are called Read Uncommitted, Read Committed, Repeatable, Serializable, Snapshot.

The following script when executed will display all current sessions and their Isolation Levels. It is always good to know what your connections are doing to SQL.

select
	s.session_id
	,s.status
	,db_name(s.database_id) [Database]
	,case 
	     when s.transaction_isolation_level = 1 then 'Read Uncommitted'
	     when s.transaction_isolation_level=2 then 'Read Committed'
	     when s.transaction_isolation_level=3 then 'Repeatable'
	     when s.transaction_isolation_level=4 then 'Serializable'
	     when s.transaction_isolation_level=5 then 'Snapshot'
	     else 'Unspecified'
	end [Transaction Isolation Level]
	,s.host_name
	,s.cpu_time
	,s.deadlock_priority
from sys.dm_exec_sessions s
where s.session_id>50

In the case of my client, there was one UPDATE transaction that was using Serializable Isolation Level, it was this session that was BLOCKING all other transactions from completing. But why was the transaction Serializable and why was it blocking for so long of a period?

Not knowing the details behind Serializable Isolation Level, it was time for some more learning. I will discuss the different Isolation levels in my next post. Until then…

Advertisements