SQL Server: Fix – Msg 1204, Level 19, State 4 – The instance of the SQL Server Database Engine cannot obtain a LOCK resource at this time…

This is an error that does not happen often. In fact, I came across this for the first time when one of my co-workers Inken HOEIJMANS asked me about this. With her permission, I am reproducing her code solution here. Thank you Inken!

She was trying to run a MERGE statement using a large source and large target table and got this error.

The Error:

Msg 1204, Level 19, State 4, Server MYSERVERNAME\MYINSTANCE, Line 1819

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.

A Solution?

Google, the first source of answers asked me to increase the value of “locks” as did several other solutions. The default value seemed good enough and I was skeptical.

SELECT *
FROM sys.configurations
WHERE name = 'locks'
configuration_id name value minimum maximum value_in_use description is_dynamic is_advanced
106 locks 0 5000 2147483647 0 Number of locks for all users 0 1

Increasing the “locks” value might have worked but I certainly don’t think that it is the best solution in all cases.

Basically,  SQL Server is saying that it ran out of locks (more on Locks here). In this case, it ran out the maximum number of locks since the default is a very high maximum of 2147483647! What can we do to reduce the number of locks used?  In our case of the MERGE statement, we need

  • Shared lock (S) – on source table to reserve a page or row to be available only for reading
  • Update lock (U) – On target table rows that will be updated which will be transformed to an exclusive lock (X) when it is ready to Update
  • Exclusive lock (X) – On target table rows/pages that require INSERT/UPDATE/DELETE as a result of the MERGE

Depending on how the MERGE statement is structured, SQL Server will see that all rows may need one or more of the above types of locks even if the value is updated to existing value on a record. Hence, the need for the large number of locks and the error.

If the situation is that an UPDATE or MERGE on a single table is causing SQL Server to run-out of locks, we need to re-evaluate and guide it properly.

Here is an altered version of SQL shared by Inken which error’ed out

PRINT 'merging tables: my_target_table' 

MERGE [my_target_table] AS target
USING (SELECT columns_list_csv
    FROM   my_database..my_target_table
            JOIN my_database..my_join_table
                ON my_target_table.id = my_join_table.id
      ) AS source (columns_list_csv)
ON ( target.col1 = source.col1
    AND target.col2 = source.col2 )
WHEN NOT matched THEN
INSERT ( columns_list_csv)
VALUES ( source.col1,
            source.col2,
            source.col3);

Recommendation:

Based on the reasoning above, I recommended to Inken that two things (became three) could be tried

  1. Break-up your UPDATE/MERGE/DELETE into chunks. I show techniques here and here.
  2. Obtain EXCLUSIVE lock at the table level on target table and possibly source table for the duration of the MERGE. We are helping SQL Server avoid row/page level locks since it already has Exclusive (x) at the table level we are saving it time by preventing lock escalations. i.e., 3 exclusive locks vs. 2147483647+ all lock types!
  3. Delete foreign key references to the PK column of the target table and recreate after the MERGE. This is something Inken added to the above two to optimize the process even further as this prevents the FK check from being done on every change in target with the PK!

Solution

As one of the very smart developers that Inken is, she quickly turned the SQL to run in 10 chunks and added exclusive locking with TABLOCK on all the tables involved. Here is the alternative SQL that worked without any errors:

---------------------------------------------------------
--STEP 1: Drop the foreign keys on tables(s) referencing our target table's primary key
---------------------------------------------------------
ALTER TABLE dbo.[my_fk_table]
    DROP CONSTRAINT [fk_my_other_table]
GO
PRINT GETDATE();
PRINT 'merging tables: my_target_table';

---------------------------------------------------------
--STEP 2: Use Table lock and merge in chunks. Done with MODULUS %
---------------------------------------------------------
DECLARE @i INTEGER
DECLARE @j INTEGER 

SELECT @i = 0
SELECT @j = 10 

WHILE @i <= @j
BEGIN
    MERGE [my_target_table] WITH (tablockx) AS target
    using(SELECT columns_list_csv
            FROM   my_database..my_target_table WITH (tablockx)
                    JOIN my_database..my_join_table WITH (tablockx)
                    ON my_target_table.id = my_join_table.id
            WHERE  my_join_table.id%@j = @i
		  ) AS source (columns_list_csv)
    ON ( target.col1 = source.col1
            AND target.col2 = source.col2 )
    WHEN NOT matched THEN
        INSERT (columns_list_csv)
        VALUES ( source.col1,
                source.col2,
                source.col3); 

    SELECT @i = @i + 1
END
GO
PRINT GETDATE()
---------------------------------------------------------
--STEP 3: Recreate dropped FKs
---------------------------------------------------------
ALTER TABLE dbo.[my_fk_table]
    ADD CONSTRAINT [fk_my_other_table] FOREIGN KEY (pk_id) REFERENCES
    dbo.my_target_table (pk) ON DELETE CASCADE
GO

Notice the comments. In addition to the TABLOCKX (exclusive table locks), it is a simple yet effective trick that Inken is using here to split the MERGE into 10 chunks in a loop driven by MODULUS. Here is the output of the successful run.

Output:
**********************
05/05/2020 18:52:49.45
merging tables: my_target_table

(77848035 rows affected)

(75690377 rows affected)

(72445049 rows affected)

(82673633 rows affected)

(82241747 rows affected)

(78270031 rows affected)

(76979514 rows affected)

(92893469 rows affected)

(88428815 rows affected)

(88368104 rows affected)

(0 rows affected)
05/05/2020 22:46:23.36

Total count(*) 815,838,774

Thanks to Inken for sharing her technique and the code!

Conclusion:

Hopefully, the notes in this blog post help you resolve the problem without having to increase the “locks” configuration to a mindlessly big number. Please post your comments and experiences!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s