on April 26, 2017
I recently did a Dear SQL DBA episode answering a question about lock timeouts and memory in SQL Server. I really enjoyed the episode, and thought it would be fun to follow up and show what it looks like if SQL Server doesn’t have enough memory to allocate locks.
You can control how much memory SQL Server allocates for locks using the “locks” configuration option. At least for now: the configuration option is deprecated.
Microsoft recommends that you do NOT change this setting, and let SQL Server manage your locks dynamically.
I agree with them. Don’t mess around with the locks setting.
That being said…
Let’s do some bad things to my test instance!
I’m going to change the lock configuration setting so that my SQL Server runs out of memory for locks pretty easily. Here’s my formula for lock memory starvation, using the WideWorldImporters sample database:
- Set the ‘locks’ configuration to 5,000
- Restart the SQL Server service to make this (very bad) setting go into effect
- Disable lock escalation on a table with 104K rows: ALTER TABLE Sales.OrderLines SET (LOCK_ESCALATION = DISABLE)
- I run a query that’s greedy about locks
Here’s my greedy lock query
BEGIN TRAN
SELECT OrderLineID
FROM Sales.OrderLines WITH (HOLDLOCK, ROWLOCK)
The HOLDLOCK hint tells SQL Server to run in serializable mode for this table. That makes it “hold” locks for the life of the transaction – not just take out and release locks as it rolls through the table.
The ROWLOCK hint tells SQL Server to use the most granular form of locks. SQL Server would “escalate” these locks to the table level so that it wasn’t so granular, but we disallowed that as part of our lock starvation formula.
Error message 1204: cannot obtain a LOCK resource
My instance manages to stream 4837 rows back to me before it fails with this error:
Msg 1204, Level 19, State 4, Line 8 The instance of the SQL Server Database Engine cannot obtain a LOCK resource at this time. Rerun your statement when there are fewer active users. Ask the database administrator to check the lock and memory configuration for this instance, or to check for long-running transactions.
The message comes back to my session. The message is also in the SQL Server error log, with my session_id as the source. And the message is written to the Windows Event Log under Application, too, where it shows as an error.
In my case, though, the problem isn’t that I have too many active users, as the error message suggests. Or even that I don’t have enough memory.
Things to check if you hit this error
- Has anyone changed the locks configuration on your instance? (The default setting is 0)
- Have you disabled lock escalation on any tables?
- Does your code use ROWLOCK hints to force granular locks in the time periods where this is happening?
- Does the SQL Server instance have enough memory, and is it under memory pressure? This error will be thrown if locks require 60% of engine memory, or memory is running short in Windows – both of which are pretty dire circumstances for your instance.
As for me, I’m going to set the ‘locks’ setting back to 0, restart my instance, and pretend this never happened.
REGISTER