Database Management Simplified

Ross Group Inc  MemberzPlus  Blogs  |  News  |  Careers  |  Contact    Twitter CommitDBA LinkedIn CommitDBA

CommitDBA Blogs

See What we are Talking About!

CommitDBA Blog

George Wilson, CommitDBA Senior Database Administrator

SQL Server Isolation Levels

George Wilson, CommitDBA Senior SQL Server DBA

November 18, 2014

SQL Server Isolation Levels - What Are They? How, or Why, are They Used?

The best way to ensure that you get the predictable answer each time you select data from a database is to run each transaction serially; however, concurrency will not happen. There is a balance that needs to be reached between isolation and concurrency and SQL Server uses Isolation Levels to accomplish this balance.

Let’s say you are working in a t-shirt factory and you are the sales rep. You have a client on the phone that wants to order 150 blue t-shirts right away. You go and look it up on the computer and you see that there are 200 in inventory, so you tell him not a problem. Another sales rep has a customer that says he want 100 blue shirts, he looks and sees the same 200 shirts. Your order goes in first and when the other rep puts in his order, there are only 50 left.

What happened and how can the DBA prevent this from happening?

The issue is that the Isolation level had been changed from the default of SQL Server to one that allowed for Dirty Reads. There are 5 Isolation levels and here is how they affect transactions.

Isolation Level 1 - Read Uncommitted

The first one is Read Uncommitted or “Dirty Reads” and it does what it says, it reads uncommitted data. This is OK when you need to see what the database reports as current inventory. However, the true amount may be dynamically changing due to ongoing order transactions. The way Read Uncommitted works is not to issue “share” locks and it is not blocked by “exclusive” locks.

Generally this level is not used by production queries, however, there are times when developers or DBAs might need to run a query in production and they are not really concerned about the correctness of the data, they need to run it without locking data. Read Uncommitted is more of a troubleshooting Isolation Level.

Isolation Level 2 - Read Committed

The second level is Read Committed and this is also the Default value. The way Read Committed works depends on if Read_Committed_Snapshot is OFF (default) or ON. Read_Committed_Snapshot OFF won’t allow reads of uncommitted data. It also takes share locks that won’t allow for other transactions to update/delete the data until the read is completed. Read_Committed_Snapshot ON uses row versioning to present the data as it existed at the time of the transaction. However it does not take any locks so other transactions can change the data.

Isolation Level 3 - Repeatable Reads

The third level is Repeatable Reads. The difference is between Repeatable Reads and Read_Committed_Snapshot OFF is that the locks stay in place until the transaction is completed. However other transactions can INSERT rows that match the search criteria and if the search was re-executed the new data would be added to the result set.

Isolation Level 4 - Snapshot

The fourth level is Snapshot. It is like the Read_Committed_Snapshot ON with some additional requirements.

The Allow_Snapshot_Isolation Database Level must be ON and if you are trying to do cross database queries, then the other database must also have it on as well.

You cannot switch from Snapshot to another isolation level if you started out with a different level. However, if you start with Snapshot and then move to a different level, you can then go back to Snapshot.

If you start with Snapshot, and then make a change to data (while still in the same transaction) you will be able to read the changed data.

When a transaction is being rolled back Snapshot will request a lock if it is attempting to access data that is being rolled back. The Snapshot transaction is blocked until the roll back is completed. At this point the lock is granted and the Snapshot transaction releases the lock and continues with its requests.

Isolation Level 5 - Serializable

The fifth, and final, level is Serializable. This is the ideal world, where each transaction executes one at a time. However, it is the worst for concurrency. Serializable will put a range lock on a table that includes the key range for the search criteria. At the point no one can insert, delete or update any data that would fall within that range, until the transaction has completed.

If you have any questions please look at the Set Transaction Level in Books on Line or here at Technet’s SET TRANSACTION ISOLATION LEVEL (Transact-SQL)

Got database support needs?
Professional and Affordable DBA and Data Services

Contact us Today