Large enterprise systems frequently face concurrency issues. As transaction volume increases, you begin seeing:
Deadlocks
Lock waits
Update conflicts
Lost updates
Phantom reads
Row-level contention
To overcome these issues, strong ORM conventions alone are insufficient. A Concurrency Framework inside SQL Server allows your application to handle high volumes with predictable consistency and reliability.
This article explains how to design a hybrid concurrency strategy combining:
The goal is to produce a system that scales under stress while ensuring data correctness.
1. Why Concurrency Fails in Real Systems
Common scenarios:
Two users editing the same sales order.
Inventory decrement executed simultaneously by multiple workers.
Long-running read queries blocking updates.
Parallel background jobs updating the same rows.
ORM-generated transactions holding unnecessary locks.
When uncontrolled, this leads to:
A Concurrency Framework allows the database to enforce rules systematically rather than relying on ad-hoc fixes.
2. High-Level Architecture
┌────────────────────────────┐
│ Application Layer │
│ (.NET API, Background Jobs)│
└──────────────┬─────────────┘
│ Calls
┌──────────────┴─────────────┐
│ Concurrency Framework │
│ (SQL Server Stored Procs) │
├──────────────┬─────────────┤
│ Deadlock Retry Wrapper │
│ Optimistic Token Checks │
│ Pessimistic Lock Sections │
│ Logical Retry Conditions │
└──────────────┬─────────────┘
│
┌─────────┴──────────┐
│ Business Procedures │
└─────────────────────┘
3. Hybrid Concurrency Model
The framework uses three pillars:
Optimistic Concurrency
No lock initially. Use a version field.
If version mismatches → reject update.
Pessimistic Concurrency
Acquire XLOCK or UPDLOCK to ensure only one writer.
Deadlock Retry
Retry the block 3–5 times if SQL error 1205 occurs.
This gives high performance on normal operations and safety during high contention.
4. Flowchart: Concurrency Workflow
START
│
▼
┌─────────────────────────┐
│ Load record + version │
└──────────────┬──────────┘
│
┌───────────▼────────────┐
│ Optimistic check OK? │
└───────────┬────────────┘
│NO
▼
Reject update (409 Conflict)
│
▼
END
YES
│
▼
┌──────────────────────────────────┐
│ Enter Pessimistic Lock Block │
│ (SELECT … WITH UPDLOCK, ROWLOCK) │
└─────────────────┬────────────────┘
│
▼
Apply update logic
│
▼
┌──────────────────────────┐
│ Commit │
└──────────────────────────┘
│
▼
END
5. SQL Version-Token Design
Add a RowVersion or TimestampToken field:
ALTER TABLE SalesOrder
ADD RowVersion BIGINT NOT NULL DEFAULT 1;
On every update:
UPDATE SalesOrder
SET Quantity = @Qty,
RowVersion = RowVersion + 1WHERE SalesOrderId = @IdAND RowVersion = @OldVersion;
If no row is updated → version was outdated → concurrency conflict.
6. Pessimistic Lock Pattern
Use:
UPDLOCK: avoids deadlocks by indicating intention to update
ROWLOCK: restrict lock to specific row
HOLDLOCK: serializable behavior
Example:
SELECT *FROM SalesOrder WITH (UPDLOCK, ROWLOCK)
WHERE SalesOrderId = @Id;
This guarantees only one active writer.
7. Designing the Deadlock Retry Framework
Deadlocks are unavoidable, but retrying the failed block resolves 99 percent of them.
7.1 Deadlock Retry Wrapper
CREATE PROCEDURE DeadlockRetryWrapper
(
@Attempts INT,
@ProcName SYSNAME,
@JsonInput NVARCHAR(MAX)
)
ASBEGIN
DECLARE @Try INT = 1;
WHILE @Try <= @Attempts
BEGIN
BEGIN TRY
EXEC @ProcName @JsonInput;
RETURN;
END TRY
BEGIN CATCH
IF ERROR_NUMBER() = 1205 -- Deadlock
BEGIN
SET @Try += 1;
WAITFOR DELAY '00:00:00.150'; -- Backoff
CONTINUE;
END
ELSE
BEGIN
THROW; -- rethrow other errors
END
END CATCH
END
THROW 51000, 'Deadlock retry limit exceeded.', 1;
END
This can wrap all critical stored procedures.
8. Business Procedure Example with Hybrid Concurrency
Below is how a real transaction uses the framework.
CREATE PROCEDURE UpdateStockQty
(
@StockId INT,
@Qty INT,
@Version BIGINT
)
ASBEGIN
SET NOCOUNT ON;
BEGIN TRAN;
-- Optimistic check
UPDATE Stock
SET RowVersion = RowVersion + 1
WHERE StockId = @StockId
AND RowVersion = @Version;
IF @@ROWCOUNT = 0
BEGIN
ROLLBACK;
THROW 50001, 'Version conflict', 1;
RETURN;
END
-- Pessimistic lock
SELECT Quantity
INTO #Tmp
FROM Stock WITH (UPDLOCK, ROWLOCK)
WHERE StockId = @StockId;
UPDATE Stock
SET Quantity = Quantity - @Qty
WHERE StockId = @StockId;
COMMIT;
END
This ensures:
No concurrency overwrite
No phantom writes
No deadlock
No lost updates
9. Implementing this in .NET (Recommended Pattern)
Use a retry policy such as Polly:
var policy = Policy
.Handle<SqlException>(ex => ex.Number == 1205)
.WaitAndRetry(3, retry => TimeSpan.FromMilliseconds(150));
policy.Execute(() =>
{
ExecuteSqlStoredProcedure("UpdateStockQty", parameters);
});
10. Granular Locking Strategy Matrix
| Operation Type | Preferred Method | Reason |
|---|
| Read-only metadata | Optimistic or Snapshot | No locks |
| Small row updates | UPDLOCK + RowVersion | High performance |
| High-conflict updates | UPDLOCK + HOLDLOCK | Ensures order |
| Complex financial transactions | Serializable + Retry | Strong consistency |
| Long-running processes | Optimistic + Version check | Avoids blocking |
11. Snapshot Isolation
Enable it once per DB:
ALTER DATABASE MyDB SET READ_COMMITTED_SNAPSHOT ON;
ALTER DATABASE MyDB SET ALLOW_SNAPSHOT_ISOLATION ON;
This drastically reduces shared locks on reads.
12. Common Deadlock Causes & Prevention Rules
1. Access objects in the same order.
If two procedures update Account then Ledger, always update in same order.
2. Avoid implicit transactions.
ORMS often cause unexpected transaction scopes.
3. Keep transaction scope small.
No logging, loops, or external API calls inside transactions.
4. Use UPDLOCK on SELECT-before-UPDATE patterns.
Ensures consistent intent to update.
13. Framework Capabilities
Your SQL Concurrency Framework should support:
14. Final Architecture Diagram
┌───────────────────────────────┐
│ Concurrency Configuration Table│
└───────────────┬───────────────┘
│
┌────────────────┴─────────────────┐
│ Deadlock Retry Wrapper │
│ (SQL + .NET Retry Policies) │
└────────────────┬──────────────────┘
│
┌─────────────────────┴────────────────────────┐
│ Business Stored Procedures │
│ - Optimistic Version Checks │
│ - Pessimistic Lock Blocks │
│ - Serializable Transactions │
└─────────────────────┬────────────────────────┘
│
┌────────▼────────┐
│ SQL Data Tables │
└─────────────────┘
Final Summary
A SQL Server Concurrency Framework must combine:
Optimistic concurrency for low-conflict operations
Pessimistic locking for high-contention cases
Deadlock retry logic for stability
Snapshot-based reads for performance
Version tokens for correctness
Retry-safe stored procedure design
Systematic locking patterns instead of ad-hoc patches
Enterprises that adopt this hybrid strategy significantly reduce:
Deadlocks
Blocking
Failed updates
Data inconsistencies
Production defects