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 Transaction Isolation Levels: Master Data Consistency

- 29.08.26 | 29.08.26 - ErcanOPAK

🔒 Deep Dive: Transaction isolation levels control data visibility between transactions. Choose the right level for your use case.

Concurrent transactions can conflict. Isolation levels prevent data anomalies.

🚀 Isolation Levels Explained

-- ✅ Read Uncommitted (Dirty Reads)
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
BEGIN TRANSACTION;
    -- ✅ Can read uncommitted data (dirty reads)
    -- Fastest but least consistent
COMMIT;

-- ✅ Read Committed (Default)
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
BEGIN TRANSACTION;
    -- ✅ Only reads committed data
    -- Prevents dirty reads
COMMIT;

-- ✅ Repeatable Read
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
BEGIN TRANSACTION;
    -- ✅ Data stays consistent during transaction
    -- Prevents dirty reads and non-repeatable reads
COMMIT;

-- ✅ Serializable (Highest)
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN TRANSACTION;
    -- ✅ Full isolation
    -- Prevents all anomalies
COMMIT;

-- ✅ Snapshot (Optimistic)
ALTER DATABASE MyDatabase SET ALLOW_SNAPSHOT_ISOLATION ON;
SET TRANSACTION ISOLATION LEVEL SNAPSHOT;
BEGIN TRANSACTION;
    -- ✅ Reads data as of transaction start
    -- No locks, uses row versioning
COMMIT;

💡 Real-World: Isolation Level Selection

-- ✅ E-Commerce: Snapshot isolation (prevent deadlocks)
ALTER DATABASE MyDatabase SET ALLOW_SNAPSHOT_ISOLATION ON;

-- ✅ Financial: Serializable (highest consistency)
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN TRANSACTION;
    -- ✅ Transfer money - must be consistent
    UPDATE Accounts SET Balance = Balance - 100 WHERE Id = 1;
    UPDATE Accounts SET Balance = Balance + 100 WHERE Id = 2;
COMMIT;

-- ✅ Reporting: Read Uncommitted (fastest)
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SELECT * FROM Orders WHERE OrderDate > '2024-01-01';
-- ✅ Dirty reads are acceptable for reports

-- ✅ Analytics: Snapshot (consistent view)
SET TRANSACTION ISOLATION LEVEL SNAPSHOT;
SELECT 
    YEAR(OrderDate) AS Year,
    SUM(TotalAmount) AS Total
FROM Orders
GROUP BY YEAR(OrderDate);

💡 Expert Tip: Deadlocks and Isolation

-- ✅ Handle deadlocks with TRY/CATCH
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

BEGIN TRY
    BEGIN TRANSACTION;
        -- ✅ Critical section
        UPDATE Orders SET Status = 'Processing' WHERE Id = 123;
        UPDATE Inventory SET Stock = Stock - 1 WHERE ProductId = 456;
    COMMIT TRANSACTION;
END TRY
BEGIN CATCH
    IF ERROR_NUMBER() = 1205  -- Deadlock
    BEGIN
        ROLLBACK TRANSACTION;
        -- ✅ Retry logic
        W AITFOR DELAY '00:00:00.100';
        -- Retry transaction
    END
    ELSE
    BEGIN
        -- ✅ Other errors
        ROLLBACK TRANSACTION;
        THROW;
    END
END CATCH;

-- ✅ Use NOLOCK hint (Read Uncommitted)
SELECT *
FROM Orders WITH (NOLOCK)
WHERE OrderDate > '2024-01-01';

Related posts:

SQL: Use Subqueries for Complex Data Retrieval

SQL: Use Subqueries for Complex Data Retrieval

How to update Identity Column in SQL Server

How to update Identity Column in SQL Server

SQL Server: Fix a COALESCE Default Value That Gets Silently Truncated Because the Result Took the Fi...

SQL Server: Fix a COALESCE Default Value That Gets Silently Truncated Because the Result Took the Fi...

Views: 11

Post navigation

Step-by-Step: SQL Stored Procedures for Maximum Performance
C#: Why async void Event Handlers Silently Swallow Exceptions (And How to Catch Them Before Production Breaks)

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 @ (608)
  • 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 (546)
  • 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