Isolation Levels in SQL Server
Want to learn more about isolation levels in SQL Server? We’re here to help!
Kendra Little – Videos and Articles
- There’s Something About NOLOCK – This 30 minute video demonstrates some phenomena of the read uncommitted isolation level which may surprise you.
- 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.
- Isolation Levels Tag (multiple great posts are linked from here)
- 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)
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.
- “Understanding Snapshot Isolation” and “When Snapshot Isolation Breaks Code” chapters in Defensive Database Programming (free eBook)
- 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
- 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.