Why Does My Delete Script Lock the Whole Table? Understanding SQL Server Lock Escalation

Background

In a database there is a table named 后宫佳丽 that accumulates millions of rows daily. To keep the table size manageable, a scheduled script deletes rows where age>18 every Sunday. However, the script runs for a whole day and blocks reads. delete from `后宫佳丽` where age>18 The problem turned out to be lock escalation: the delete statement caused row locks to be promoted to a table lock, making the table unavailable.

Why

The lock escalation occurs because the massive delete acquires many row locks, which SQL Server upgrades to a table lock. This also leads to errors when other processes try to read the table.

Prerequisite Knowledge

ACID

Atomicity

All operations in a transaction either complete entirely or roll back.

Consistency

The database remains in a valid state before and after a transaction.

Isolation

Concurrent transactions are isolated to prevent interference, with isolation levels such as read uncommitted, read committed, repeatable read, and serializable.

Durability

Committed changes survive system failures.

Transaction

A transaction is the smallest unit of work that can be committed or rolled back.

Lock

SQL Server acquires locks on objects when a transaction starts and releases them when it ends. Lock types include shared (S), exclusive (X), update (U), and intent locks.

Lock Hierarchy

Locks are taken at different levels: row, page, and table. The following diagram shows the hierarchy.

Shared (S) Locks

Used for reading data.

Exclusive (X) Locks

Prevent other transactions from modifying or reading the locked object.

Update (U) Locks

Used during the read phase of an update to block other modifications.

Intent Locks

Indicate that a lower‑level lock (S or X) will be taken on a resource.

SQL Server Locking

SQL Server provides dynamic management views such as sys.dm_tran_locks to monitor active locks.

Example: create a demo table and start a transaction that updates rows.

CREATE TABLE TestBlock
(
    Id INT,
    Nm VARCHAR(100)
);
INSERT INTO TestBlock VALUES (1,'CodingSight');
BEGIN TRAN;
UPDATE TestBlock SET Nm='NewValue_CodingSight' WHERE Id=1;
SELECT @@SPID;

Querying sys.dm_tran_locks shows an exclusive lock on the row, page, and an intent lock on the table.

After populating the table with thousands of rows and running two overlapping update transactions, SQL Server escalates the many row locks to a table lock (lock escalation).

Lock escalation is triggered when a single statement acquires at least 5,000 locks on a non‑partitioned table, or when the total lock count exceeds memory thresholds.

How to Prevent Lock Escalation

Break Large Operations into Smaller Batches

Instead of deleting all rows at once, delete a limited number of rows per batch.

SET ROWCOUNT 500;
delete_more:
    DELETE FROM `后宫佳丽` WHERE age>18;
IF @@ROWCOUNT > 0 GOTO delete_more;
SET ROWCOUNT 0;

Create Appropriate Indexes

Indexes reduce the need for table scans, lowering the chance of lock escalation and deadlocks.

Hold an Intent Exclusive (IX) Lock on the Table

Acquiring an IX lock for the duration of a long operation prevents escalation to a table lock.

BEGIN TRAN;
SELECT * FROM mytable (UPDLOCK, HOLDLOCK) WHERE 1=0;
WAITFOR DELAY '1:00:00';
COMMIT TRAN;

Conclusion

By keeping transactions short, batching large deletes, adding suitable indexes, and using intent locks, you can avoid lock escalation and keep your SQL Server database responsive.