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 Deadlock That Only Happens Under Load Because Two Stored Procedures Touch the Same Tables in Opposite Order

- 28.10.26 - ErcanOPAK

🗄️ Fix a Deadlock That Only Happens Under Load Because Two Stored Procedures Touch the Same Tables in Opposite Order

A deadlock forms when one procedure locks a table and then needs a lock on a second table, while at the same moment a different procedure running concurrently has already locked that second table and now needs a lock on the first one – each is waiting on a resource the other is holding, and neither can proceed until the deadlock monitor picks one of them as a victim and rolls it back. Under light traffic, the timing rarely lines up closely enough for this to actually happen, which is exactly why it can pass most testing and only start appearing once concurrent load is heavy enough to create that overlap regularly.

🔎 The Problem

-- Procedure A:
BEGIN TRAN;
UPDATE Accounts SET Balance = Balance - 100 WHERE Id = 1;
UPDATE Ledger SET LastTouched = GETDATE() WHERE AccountId = 1;
COMMIT;

-- Procedure B, running concurrently:
BEGIN TRAN;
UPDATE Ledger SET LastTouched = GETDATE() WHERE AccountId = 1;
UPDATE Accounts SET Balance = Balance + 100 WHERE Id = 1;
COMMIT;

-- Under load: A holds Accounts and waits on Ledger, while
-- B holds Ledger and waits on Accounts - deadlock, one
-- transaction is chosen as victim and rolled back.

✅ Fix: Make Both Procedures Lock the Shared Tables in the Same Order

  • Capture the actual deadlock graph, through an extended events session on the deadlock report event or the error log if the right trace flag is enabled, to see precisely which resources and procedures were involved rather than guessing from the error alone.
  • Reorder the statements in one of the two procedures so both acquire locks on the shared tables in the same consistent sequence, which removes the possibility of the two ever forming a cycle in the first place.
  • Consider whether additional indexing on the filtered columns can reduce lock duration or scope as a complementary improvement, understanding this lowers the odds of overlap without fixing the underlying ordering issue by itself.

🚨 Why This Is Dangerous

  • Catching the resulting deadlock error and simply retrying the transaction is a reasonable short-term mitigation for availability, but treating retry-on-deadlock as the actual fix leaves the real lock-ordering conflict in place, where it keeps consuming a small, growing cost in retried transactions as load increases.

A deadlock isn’t bad luck on a bad day – it’s two procedures that were always going to collide, just waiting for enough concurrent traffic to actually prove it.

— Database Engineer

Related posts:

SQL Server: Fix a Scalar User-Defined Function That Silently Forces a Row-by-Row Scan Across the Ent...

SQL Server: Fix a Scalar User-Defined Function That Silently Forces a Row-by-Row Scan Across the Ent...

SQL: Use Aggregate Functions for Data Analysis

SQL: Use Aggregate Functions for Data Analysis

SQL Server: Why a Non-SARGable WHERE Clause Silently Ignores Your Index

SQL Server: Why a Non-SARGable WHERE Clause Silently Ignores Your Index

Views: 8

Post navigation

C#: Fix String Interpolation That Silently Uses the Wrong Culture Because ToString() Was Called Without Specifying InvariantCulture
The AI Prompt That Turns a Semester’s Worth of Lecture Notes Into a Condensed Study Guide Organized by Topic Instead of by Date

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 (858)
  • 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 (858)
  • 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