🗄️ The READ COMMITTED That Wasn’t What You Thought
The isolation level READ COMMITTED has two entirely different underlying implementations in SQL Server, and which one a database actually runs depends on a database-level setting, not anything in the query itself. With READ_COMMITTED_SNAPSHOT OFF – the server default for most on-premises installs – READ COMMITTED uses locking: a reader can block behind, or be blocked by, a writer touching the same rows. With it ON – the default for Azure SQL Database – READ COMMITTED uses row versioning instead: readers see a transactionally consistent snapshot and never block on writers, which is a materially different concurrency profile for the exact same isolation level name.
🔎 The Problem
-- Which behavior you get depends entirely on this database-level -- setting, not on anything in the SELECT itself: SELECT is_read_committed_snapshot_on FROM sys.databases WHERE name = DB_NAME(); -- Same query, same isolation level name, two different outcomes: SELECT * FROM Orders WHERE CustomerId = 123; -- OFF: can block behind an uncommitted UPDATE on this row. -- ON: reads the last committed version instead, no blocking.
âś… Fix: Check the Setting Before You Reason About Blocking
- Check sys.databases.is_read_committed_snapshot_on for the actual database you’re troubleshooting before reasoning about blocking behavior from first principles – don’t assume it matches whatever the default was on a different server or a different cloud offering.
- If code was written assuming row-versioning semantics (no blocking, no dirty reads), make that assumption explicit with SET TRANSACTION ISOLATION LEVEL SNAPSHOT plus ALLOW_SNAPSHOT_ISOLATION ON, rather than depending on a database-level default a future migration or restore could silently change.
- When moving a database between environments (on-prem to Azure SQL, or restoring a backup to a server with different defaults), explicitly check and, if necessary, set READ_COMMITTED_SNAPSHOT to match what the application was actually designed and tested against.
⚠️ Why This Is Easy to Miss
- Both configurations report the exact same isolation level name back to the application – logging, ORM configuration, and code review all see identical-looking settings while the server underneath behaves differently.
- The difference only becomes visible under real concurrent write load – a single developer running queries alone on a dev box will never trigger the blocking, or the absence of it, that production traffic eventually will.
READ COMMITTED is one name for two different concurrency models in SQL Server – and the setting that decides which one you’re running isn’t in your query, it’s in sys.databases.
