In a perfect world, the SQL Server Query Optimizer works perfectly. It automatically decides when to use Shared (S) locks for reading and Exclusive (X) locks for writing, while following the rules of your transaction isolation level (usually READ COMMITTED by default).

But in systems with a lot of traffic, „default” isn’t always good enough. The usual way that locks work can cause performance issues, such as things getting stuck or updates being lost. This is where Locking Hints (or Table Hints) come in.

Locking Hints are instructions that tell the optimizer: „Step aside, I know better how to handle concurrency in this specific case.” They force the database engine to use a specific type of lock.

1. The „Check-Then-Update” Pattern (Preventing Lost Updates)

Hint: WITH (UPDLOCK)

Scenario: Two sessions read the same row (e.g., Balance = 100), the application calculates a new value, and both try to update it. Without proper hints, one update might overwrite the other (Lost Update) or cause a deadlock during the conversion from a Shared (S) to an Exclusive (X) lock.

BEGIN TRAN

SELECT Balance
FROM Accounts WITH (UPDLOCK)
WHERE Id = 1;

-- Business Logic ...

-- only one process can hold a U lock
UPDATE Accounts
SET Balance = Balance - 100
WHERE Id = 1;

COMMIT

Why it works:

  • Forces an Update (U) lock during the SELECT phase.
  • Blocks other processes that also try to acquire an UPDLOCK (queues them up).
  • Allows other processes to perform standard reads (S-Lock) but prevents them from preparing for a write.

Use when:

You fetch data, process it in the application’s memory, save the result, and want to avoid (S → X) conversion deadlocks.

2. The „Queue Processor” Pattern (High Throughput, Zero Blocking)

Hint: WITH (UPDLOCK, READPAST)

Scenario: You have multiple background workers picking up jobs from a single queue table. You want massive throughput and parallelism without workers blocking each other. This is the „Holy Grail” of database queue systems.

BEGIN TRAN

SELECT TOP 1 *
FROM JobQueue WITH (UPDLOCK, READPAST)
WHERE Status = 'Pending'
ORDER BY CreatedAt;

UPDATE JobQueue
SET Status = 'Processing'
WHERE Id = @Id;

COMMIT

Why it works:

  • UPDLOCK: Reserves the row for modification, guaranteeing nobody else will touch it.
  • READPAST: Skips locked rows. Instead of waiting (and blocking the thread), SQL Server silently skips locked rows and grabs the next available one. Result? Zero blocking, allowing 10 workers to process the same table simultaneously.

If you are using libraries like Hangfire or MassTransit with SQL Server, you are already using this pattern under the hood. Hangfire uses a WITH (UPDLOCK, READPAST) query in its methods, allowing multiple server instances to process jobs in parallel without becoming a bottleneck.

3. The „Upsert Guard” (Manual Uniqueness)

Hint: WITH (UPDLOCK, HOLDLOCK)

Scenario: You need to check if a record exists before inserting it (to avoid duplicates), but you cannot rely purely on a UNIQUE constraint (e.g., you need to execute business logic before throwing an error).

BEGIN TRAN

IF NOT EXISTS (
    SELECT 1
    FROM Users WITH (UPDLOCK, HOLDLOCK)
    WHERE Email = @Email
)
BEGIN
    INSERT INTO Users (Email) VALUES (@Email);
END

COMMIT

Why it works:

  • HOLDLOCK: Equivalent to the SERIALIZABLE isolation level. It holds the lock until the transaction ends (COMMIT/ROLLBACK).
  • Protects against Phantom Inserts (a scenario where someone else inserts a row in the split second between your SELECT and INSERT). Even if the row doesn’t exist yet, SQL Server places a Range Lock.

Use when: Manual Upserts and systems requiring absolute financial consistency guarantees.

Avoid when: Highly concurrent systems, unless strictly necessary—HOLDLOCK is a fast track to deadlocks.

4. The „Fail Fast” API Strategy

Hint: WITH (NOWAIT)

Scenario: Building a low-latency API (e.g., RESTful microservices). Instead of hanging the thread indefinitely waiting for a lock to be released.

BEGIN TRY
-- Attempt to lock row. Throw error 1222 immediately if blocked.
SELECT * FROM Orders WITH (UPDLOCK, NOWAIT) WHERE Id = 10;
END TRY
BEGIN CATCH
-- Fast error handling / retry logic in the application
END CATCH

Why it works: Zero waiting. The engine immediately throws an error, protecting the application’s thread pool from exhaustion (Thread Starvation).

5. The „Dirty Read” Dashboard (Performance over Accuracy)

Hint: WITH (NOLOCK)

Scenario: Running a heavy analytical query on a live OLTP system where exact, row-level accuracy is secondary to performance and keeping the system unblocked.

SELECT Count(*)
FROM TrafficLogs WITH (NOLOCK)
WHERE Date > GETDATE() - 1;

What this means in practice:

  • Dirty Reads: You read uncommitted data.
  • Double Reads: Due to Page Splits occurring during the read, you might count the same row twice.
  • Missing Rows: You might entirely miss rows that are currently moving physical locations.

Never use for: Financial ledgers, inventory management, or payments.

6. The „Brute Force” Mutex

Hint: WITH (XLOCK)

Scenario: You need to guarantee that absolutely NO ONE ELSE touches a resource (not even for reading) while you perform a critical operation.

SELECT *
FROM SystemConfig WITH (XLOCK)
WHERE Key = 'MaintenanceMode';

Why it works: Acquires an Exclusive (X) lock immediately during the read phase. All other sessions are blocked.

Real-World Architecture: Powering Modern .NET Libraries

These patterns aren’t just theoretical—they are the invisible engines behind some of the most popular libraries in the .NET ecosystem. If you are building scalable systems, you are likely already using them:

Hangfire (Background Job Processing) MassTransit (SqlServer)

Transactional Outbox Patterns:
Most custom implementations of the Outbox pattern (used to ensure database updates and message publishing happen atomically) use READPAST to allow the „Outbox Publisher” background service to scale horizontally without causing massive blocking on the Outbox table.

If you’re aiming for horizontal scalability with SQL Server as your source of truth, UPDLOCK is your distributed lock, and READPAST is your key to parallel performance.

Bibliography

MSSQL Tips – SQL Server UPDATE lock and UPDLOCK Table Hints
https://www.mssqltips.com/sqlservertip/6290/sql-server-update-lock-and-updlock-table-hints

MSSQL Tips – Using the NOWAIT option with the SQL Server RAISERROR statement
https://www.mssqltips.com/sqlservertip/1660/using-the-nowait-option-with-the-sql-server-raiserror-statement

Microsoft – SQL Server Transaction Locking and Row Versioning Guide: https://learn.microsoft.com/en-us/sql/relational-databases/sql-server-transaction-locking-and-row-versioning-guide

Microsoft – Table Hints (Transact-SQL): https://learn.microsoft.com/en-us/sql/t-sql/queries/hints-transact-sql-table

The Paradox of NOLOCK: How Dirty Reads Can Sometimes Be Cleaner: https://www.sqlservercentral.com/articles/the-paradox-of-nolock-how-dirty-reads-can-sometimes-be-cleaner

Locking in Microsoft SQL Server (Part 17) – Implementing Critical Section / Mutexes in T-SQL : https://aboutsqlserver.com/2013/05/08/locking-in-microsoft-sql-server-part-17-implementing-critical-section-mutexes-in-t-sql/

Categorized in:

ArchitecturePerformanceSQL

Tagged in:

, ,