SQL Server Isolation Levels

Isolation levels are something that are often overlooked when designing a database from scratch and by the time it is decided that a different isolation level is warranted it’s often too late to update all of that old code to ensure it works correctly. It’s by far better to start with the correct setting to make sure your code performs it’s best.

First, let’s take a look at the different isolation levels available in SQL Server (Microsoft documentation);

Read Uncommitted 

This is the isolation that allows for the fastest performance, but at a cost. Using this setting, select queries are able to read any data in the database even if it’s being modified by another transaction. This means that you can read incorrect values very easily. This may not be a concern for your application and the performance benefits might outweigh the costs but beware, this is a risky setting unless you’re sure what you’re doing.

Read Committed

This is the SQL Server default. This level stops data from being read while it is being modified and avoiding those dirty reads that uncommitted introduces. Statements that have multiple transactions can have data modified by other statements between these transactions so that’s something to be careful of. If this is a concern you can use;

Repeatable Read

This is like Read Committed but also specifies that if a transaction has read any data then another transaction can not modify or delete that data until the current transaction is completed. This means you’re going to have less concurrency (more blocking) because your writers are going to be blocked by your reading processes.

Serializable

This is the most restrictive of the isolation levels. Range locks are taken out for all statements (SELECT included) and not released until the transaction is completed. Range locks are much wider than row locks so we’re going to have more data locked. 

Snapshot

Now, this is the interesting one. For this setting, your SELECT queries will take the latest committed data. This means that if a query is currently in the process of modifying data and a SELECT wants to read it then the SELECT will use the last known good data. This setting enables the Version Store which is stored in tempdb to store all of this ‘last known good’ data. The Version Store is an additional cost to the database but it’s one that’s worth considering for the performance improvement of your read queries.

Now go and find out what isolation levels you’re using and whether this is the correct one for you.

WP Twitter Auto Publish Powered By : XYZScripts.com
%d bloggers like this: