SQL Server error 1204: Cannot obtain a LOCK resource
SQL Server ran out of memory for locks. Too many locks are held at once, usually by a huge transaction or a long-running one.
- Error number
1204- Severity
- 19
- Category
- Memory, log and storage
What it means
Each lock uses a little memory. When SQL Server can't allocate memory for another lock, the statement fails with 1204. Lock escalation normally replaces thousands of row or page locks with one table lock, so this usually means escalation was prevented or the instance is short of memory.
Common causes
- A single transaction that modifies a very large number of rows while lock escalation is disabled (
LOCK_ESCALATION = DISABLEor trace flags 1211/1224). - Many concurrent transactions each holding many locks.
- Low memory available to SQL Server.
How to fix it
-
Break large modifications into smaller transactions.
WHILE 1 = 1 BEGIN UPDATE TOP (5000) dbo.Orders SET Archived = 1 WHERE Archived = 0 AND OrderDate < '2023-01-01'; IF @@ROWCOUNT = 0 BREAK; END; -
Check lock escalation settings on the table.
SELECT name, lock_escalation_desc FROM sys.tables WHERE name = N'Orders';
-
See which sessions hold the most locks.
SELECT request_session_id, COUNT(*) AS locks FROM sys.dm_tran_locks GROUP BY request_session_id ORDER BY locks DESC;
Related errors
1222Lock request time out period exceeded701Insufficient memory in resource pool1206MySQL: Locks exceed the lock table size
More memory, log and storage errors
9002Transaction log for database is full1105Filegroup is full, cannot allocate1101Could not allocate a new page8645Timeout waiting for memory resources8623Query processor ran out of resources
The code and message text are from the Microsoft Database Engine events and errors reference. Browse the full SQL Server error list or search every database error.
Work on SQL Server in SQLly
A native SQL client for macOS, Windows and Linux with schema-aware IntelliSense, guardrails for risky statements and clear error messages, for SQL Server and the other databases you use.