🚦 Designing High-Concurrency Systems in Microsoft SQL Server

 

  

🚦 Designing High-Concurrency Systems in Microsoft SQL Server

How Large ERP Platforms Prevent Double Booking Under Extreme Load

In a large ERP system with thousands of concurrent users, a simple action like two users clicking the same seat at the exact millisecond becomes a serious engineering challenge.

This is not just a business logic issue.

It is fundamentally a database concurrency control problem in Microsoft SQL Server.

Without proper design, systems can suffer from:

⚠️ Double booking
⚠️ Inventory corruption
⚠️ Payment inconsistencies
⚠️ Deadlocks and severe blocking

In high-traffic environments such as:

• Airline reservation systems
• Telecom ERP platforms
• Banking transaction systems
• Large e-commerce platforms

Concurrency control must be precise, scalable, and resilient.

The goal is simple:

✔ Only one transaction succeeds
✔ Others fail safely
✔ System remains consistent under heavy load

 

🎟️ Scenario: Two Users Booking the Same Resource

Imagine a ticket booking system.

Table: Seats

SeatId	IsBooked	VersionNo
101	0	5

Two users attempt booking at the same moment.

User A

SeatId = 101
Time = 10:01:02.345

User B

SeatId = 101
Time = 10:01:02.345

Without concurrency protection:

Both transactions read:

IsBooked = 0

Both update the seat.

Result:

❌ Double booking
❌ Inconsistent database state

This is called a Race Condition.

 

🔒 Strategy 1: Enterprise Pessimistic Locking

In high-conflict environments (banking, ERP), the safest approach is pessimistic locking.

The system assumes conflicts will happen, so it locks the data immediately.

Implementation

BEGIN TRY
    BEGIN TRAN;

    DECLARE @SeatId INT = 101;

    -- Lock row for update
    SELECT *
    FROM Seats WITH (UPDLOCK, ROWLOCK)
    WHERE SeatId = @SeatId;

    -- Validate availability
    IF EXISTS (
        SELECT 1
        FROM Seats
        WHERE SeatId = @SeatId
        AND IsBooked = 0
    )
    BEGIN

        UPDATE Seats
        SET IsBooked = 1,
            BookedTime = SYSDATETIME()
        WHERE SeatId = @SeatId;

        COMMIT TRAN;

        PRINT 'Seat booked successfully';
    END
    ELSE
    BEGIN
        ROLLBACK TRAN;
        PRINT 'Seat already booked';
    END

END TRY
BEGIN CATCH

    IF @@TRANCOUNT > 0
        ROLLBACK TRAN;

    DECLARE
        @ErrorMessage NVARCHAR(4000),
        @ErrorNumber INT;

    SELECT
        @ErrorMessage = ERROR_MESSAGE(),
        @ErrorNumber = ERROR_NUMBER();

    PRINT 'Booking failed';
    PRINT @ErrorMessage;

END CATCH

---

🔍 Why This Works

UPDLOCK

Prevents other sessions from acquiring update locks on the row.

ROWLOCK

Ensures only the specific row is locked, not the entire table.

Transaction Control

Guarantees atomic execution.

Meaning:

Either booking succeeds
OR nothing happens

 

 

Result: Only one user succeeds; the other receives an immediate “already booked” message.

 

 

 

 

 

⚡ Strategy 2: Optimistic Concurrency (Version Control)

Large distributed systems often prefer optimistic locking.

Instead of locking rows immediately, the system validates during update.

Table Structure

SeatId

IsBooked

VersionNo

Update Operation

UPDATE Seats

SET IsBooked = 1,

    VersionNo = VersionNo + 1

WHERE SeatId = 101

AND VersionNo = 5

AND IsBooked = 0;

IF @@ROWCOUNT = 0

BEGIN

    -- Conflict detected – another user booked it already

    PRINT 'Sorry, the seat was just taken. Please choose another.';

    -- Optionally, retry the whole process

END

If another transaction updated first:

@@ROWCOUNT = 0

Meaning:

Concurrency conflict detected

Retry required

Advantages:

✔ Higher throughput
✔ Less blocking
✔ Better scalability

But it requires retry logic in application code.

For systems with mostly read operations and relatively few conflicts (e.g., product catalog with occasional purchases), optimistic locking offers higher throughput by avoiding long‑held locks.

 

🛒 Strategy 3: Reservation Model (Used by Airlines & E-commerce)

Modern booking systems rarely book immediately.

Instead they temporarily reserve resources.

Step 1 — Temporary Hold

SeatStatus = HOLD

HoldExpiry = NOW + 2 minutes

Step 2 — Payment Completion

SeatStatus = BOOKED

Step 3 — Expired Hold Release

UPDATE Seats

SET SeatStatus = 'AVAILABLE'

WHERE SeatStatus = 'HOLD'

AND HoldExpiry < SYSDATETIME();

This solves problems like:

✔ Payment timeout
✔ Cart abandonment
✔ User session drop

---

⚠️ Deadlock Risks in Large ERP Systems

Deadlocks happen when transactions lock resources in different order.

Bad Pattern

Transaction A

Seat → Payment

Transaction B

Payment → Seat

Result:

Deadlock detected

One transaction killed

Best Practice

Always follow a consistent lock order.

Seat → Order → Payment

Across the entire system.

---

⚙️ Isolation Level for High-Traffic Systems

Recommended isolation level:

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

Or enable:

READ COMMITTED SNAPSHOT

Benefits:

✔ Reduced blocking
✔ Improved concurrency
✔ Stable reads

---

📈 Indexing Strategy for High Concurrency

Indexes are critical for reducing lock scope.

Recommended Indexes

CREATE INDEX IX_Seats_Status

ON Seats (SeatId, IsBooked);



CREATE INDEX IX_Seats_Status_Version

ON Seats (SeatId, VersionNo);

Without proper indexing:

❌ SQL Server may escalate to page or table locks
❌ Performance degrades under heavy load

---

🏢 Real-World Concurrency Strategies

System	Concurrency Approach
Airline reservation	Temporary hold + transaction
Banking transfers	Pessimistic locking
E-commerce inventory	Optimistic locking
Large ERP systems	Hybrid strategy

---

🧠 Golden Rule of High-Scale Systems

Situation	Best Strategy
Frequent write conflicts	Pessimistic locking
Mostly read operations	Optimistic locking
Payment involved	Reservation system
Massive scale (millions users)	Hybrid architecture

---

🚀 Critical Production Advice

Never rely solely on application logic.

Always enforce database-level protection:

✔ Transactions
✔ Lock hints
✔ Unique constraints
✔ Version validation

Because under extreme load, failures lead to:

⚠️ Double booking
⚠️ Inventory corruption
⚠️ Financial inconsistencies

In large enterprise systems:

Data consistency is always more important than raw speed.

Design for correctness first, then optimize for performance.