Skip to content
  • Home
  • About
  • No Access
  • Privacy Policy
Bits of .NET
Bits of .NET
  • Asp.Net Core
  • C#
  • SQL
  • JavaScript
  • CSS
  • About
  • ErcanOPAK.com
  • No Access
  • Privacy Policy

Bits of .NET

Daily micro-tips for C#, SQL, performance, and scalable backend engineering.

  • Asp.Net Core
  • C#
  • SQL
  • JavaScript
  • CSS
  • About
  • ErcanOPAK.com
  • No Access
  • Privacy Policy
SQL

SQL Server: Fix a Query Reading Uncommitted Data Because READ_COMMITTED_SNAPSHOT Isn’t What You Assumed

- 11.10.26 - ErcanOPAK

🗄️ 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.

— Database Engineer

Related posts:

SQL “Parameter Sniffing Hell” — The REAL Fix Nobody Uses

SQL “Parameter Sniffing Hell” — The REAL Fix Nobody Uses

SQL Server: Fix a Query That Runs Instantly in SSMS but Times Out From the App Because ARITHABORT De...

SQL Server: Fix a Query That Runs Instantly in SSMS but Times Out From the App Because ARITHABORT De...

SQL Deletes Lock Tables

SQL Deletes Lock Tables

Views: 6

Post navigation

Kubernetes: Fix a HorizontalPodAutoscaler That Never Scales Down Because of the Default Stabilization Window
Windows 11: Fix Bluetooth Headset Audio That Drops to Mono Call Quality the Moment the Microphone Activates

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

November 2026
M T W T F S S
 1
2345678
9101112131415
16171819202122
23242526272829
30  
« Oct    

Most Viewed Posts

  • How to make theater mode the default for Youtube (1,055)
  • Get the User Name and Domain Name from an Email Address in SQL (980)
  • How to add default value for Entity Framework migrations for DateTime and Bool (977)
  • Get the First and Last Word from a String or Sentence in SQL (857)
  • How to select distinct rows in a datatable in C# (854)
  • How to enable, disable and check if Service Broker is enabled on a database in SQL Server (653)
  • Add Constraint to SQL Table to ensure email contains @ (609)
  • Average of all values in a column that are not zero in SQL (570)
  • How to use Map Mode for Vertical Scroll Mode in Visual Studio (547)
  • Find numbers with more than two decimal places in SQL (475)

Recent Posts

  • C#: Fix a ConditionalWeakTable Entry Vanishing Too Soon
  • Docker: Fix a Stale COPY Layer After Editing .dockerignore
  • Photoshop: Fix a Pattern Overlay Scaling Wrong on a High-DPI File
  • SQL Server: Fix a Computed Column Index Skipped by Implicit Cast
  • HTML5: Fix a required Input Inside a Hidden Tab Blocking Submit
  • SQL Server: Fix ROW_NUMBER Ties Reordering Between Identical Runs
  • C#: Fix an Enum HasFlag Returning True for the Zero Value
  • WordPress: Fix Featured Images Vanishing After a CDN Migration
  • Photoshop: Fix Content-Aware Fill Smearing a Repeating Pattern
  • Asp.Net Core: Fix Antiforgery Tokens Rejected Behind a Reverse Proxy

Most Viewed Posts

  • How to make theater mode the default for Youtube (1,055)
  • Get the User Name and Domain Name from an Email Address in SQL (980)
  • How to add default value for Entity Framework migrations for DateTime and Bool (977)
  • Get the First and Last Word from a String or Sentence in SQL (857)
  • How to select distinct rows in a datatable in C# (854)

Recent Posts

  • C#: Fix a ConditionalWeakTable Entry Vanishing Too Soon
  • Docker: Fix a Stale COPY Layer After Editing .dockerignore
  • Photoshop: Fix a Pattern Overlay Scaling Wrong on a High-DPI File
  • SQL Server: Fix a Computed Column Index Skipped by Implicit Cast
  • HTML5: Fix a required Input Inside a Hidden Tab Blocking Submit

Social

  • ErcanOPAK.com
  • GoodReads
  • LetterBoxD
  • Linkedin
  • The Blog
  • Twitter
© 2026 Bits of .NET | Built with Xblog Plus free WordPress theme by wpthemespace.com