Isolation Levels in SQL Server

Want to learn more about isolation levels in SQL Server? We’re here to help!

• Implementing Snapshot or Read Committed Snapshot Isolation in SQL Server: A Guide – This blog post walks you through what to consider to use optimistic locking in SQL Server.

CRAIG FREEDMAN’S BLOG POSTS ON ISOLATION LEVELS

I can’t recommend these posts by Craig Freedman enough. They are truly required reading if you’re interested in isolation levels in SQL Server.

• Read Committed Isolation Level 
• Serializable vs Snapshot Isolation Level  – These both allow a very high level of isolation, but they are not exactly functionally equivalent. This post demonstrates the difference with a great example using black and white marbles. Learn how this can impact your coding.

Notable Bugs/ Fixes

• KB2812884: Index size increases significantly after you rebuild the index online and RCSI is enabled
• KB2806979: You experience poor performance when you run a query against an RCSI-enabled table in SQL Server 2012
• Connect Item: RCSI/SI doesn’t work with rows larger than 8046 bytes (8060 bytes is expected for in-row data)

SQL Standards

• SQL 92 Standard
• SQL 2003 late draft from the Whitemarsh Information Systems Corporation

Erin Stellato

• Isolation Levels and the Effects on SQL Statements

Michael J Swart

• A highly entertaining and invormative series on the Concurrent Transaction Phenomena. Start here.

Row-Versioning Whitepaper by Kimberly L. Tripp, Neal Graves

• SQL Server 2005 Row Versioning-based Transaction Isolation. This contains sample code, comparison to Oracle Snapshot, Table with possible phenomena, and a table of ALL sql server isolation levels and when they are best suited for an application.

Conor Cunningham

• Conor vs. Isolation Level Upgrade on UPDATE/DELETE Cascading RI

Alex Kuznetsov

• “Understanding Snapshot Isolation” and “When Snapshot Isolation Breaks Code” chapters in Defensive Database Programming (free eBook)

Lubor Kollar

• Previously committed rows might be missed if NOLOCK hint is used 

Itzik Ben-Gan

• Quaere Verum – Clustered Index Scans – Part III 

Paul Randal

• When can allocation order scans be used? 

Paul White

• The Case of the Missing Shared Locks 

Xavier Shay

• A Gentle Introduction to Isolation Levels

Books Online

• Understanding the Available Isolation Levels http://technet.microsoft.com/en-us/library/cc546518.aspx  <- Contains a section on Update Conflicts in Snapshot Isolation with repro code.
• Concurrency Effects http://msdn.microsoft.com/en-us/library/ms190805.aspx
• Set Transaction Isolation Level http://msdn.microsoft.com/en-us/library/ms173763.aspx
• Isolation Levels in the Database Engine http://msdn.microsoft.com/en-us/library/ms189122.aspx
• Key-Range Locking http://msdn.microsoft.com/en-us/library/ms191272.aspx
• Locking Hints http://msdn.microsoft.com/en-us/library/ms189857.aspx
• Query Hints http://msdn.microsoft.com/en-us/library/ms181714.aspx
• Lock Compatibility (Database Engine) http://msdn.microsoft.com/en-us/library/ms186396.aspx

Other References

• Isolation (database systems) http://en.wikipedia.org/wiki/Isolation_(database_systems)
• Transaction Isolation in PostGresSQL http://www.postgresql.org/docs/8.1/static/transaction-iso.html

More Tools for Slow SQL Servers

sp_Blitz®: Free SQL Server Health Check – You’ve inherited a SQL Server from somebody, and you have no idea why it’s slow. sp_Blitz® gives you a prioritized list of health and performance issues, plus gives you URLs for more details about each issue.

Our Free 6-Month DBA Training Plan – Every Wednesday, you get an email with our favorite free SQL Server training resources. We start at backups and work our way up to performance tuning.

SQL Critical Care® – Don’t have time to learn the hard way? We’re here to help with our quick, easy process that gets to the root cause of your database health and performance pains. Contact us for a free 30-minute sales consultation.