Prevent SQL Server Blocking using Lock_Timeout

By: Bhavesh Patel   |   Updated: 2020-01-07   |   Comments (2)   |   Related: 1 | 2 | 3 | 4 | 5 | 6 | > Locking and Blocking

Problem

In SQL Server, when one session holds locks on a particular resource such as a table, row or key and a second session needs to obtain locks on the same resource, the second session may need to wait for the first session to release the needed locks before proceeding with the process. Depending on how long the wait is for the locks to be acquired for the second session, there might be a blocking chain between these two processes. In this tip, I will explain the use of lock_timeout to minimize blocking.

Solution

Blocking is a normal characteristic of a relational database engine and SQL Server blocking happens for lock-based concurrency. In simple terms, one session acquires and holds a lock on a specific resource for its processing and a second session attempts to acquire a lock that causes contention on the same resource and this causes blocking. The blocking stops as soon as the first session releases the locks and there is no other session holding the locks.  Locks are used to make sure that multiple processes are not modifying the same data at the same time which can cause inconsistencies.

So, blocking is necessary for the relational database engine to work properly, but we can do some of these things to minimize blocking:

• In DML operations (Insert/Update/Delete), try to build T-SQL code specific to one table vs using several tables at once.
• Try to divide the SQL code into chunks instead of one large complex statement.
• Try to minimize transaction size with respect to data volume, meaning try to modify/delete data in chunks instead of one big operation.
• Look at using deadlock priority if it meets business needs.
• Try to use Lock_Timeout with query re-attempt logic.

As per the above points, I would like to explain more about the lock timeout setting vs query re-attempt logic.

Setting the SQL Server Lock_Timeout Interval

If you don't want to wait forever for a lock to become available, SQL Server offers the lock_timeout interval, which case be set as follows:

SET LOCK_TIMEOUT {Millisecond}

You specify the timeout interval in milliseconds, i.e. for a 10 second interval use the below code:

SET LOCK_TIMEOUT 10000

As per the process request, a lock resource that can’t be granted within 10 seconds will be aborted. This will generate the exception "the lock resource request time out period exceeded".

This doesn’t mean that every query session would terminate if the interval is exceeded, because it is a lock exceeded interval value.

Let me explain. I executed the below code in SSMS.

After creating a database, I set the Lock_Timeout interval to 10 seconds and a delay interval to 15 seconds. In the last statement, I want to check what the timeout setting was for this session. After execution, the overall batch took about 16 seconds which is expected based on the 15 second delay, but the overall process didn't abort even though the lock timeout of 10 seconds was exceeded.

Setting the SQL Server Lock_Timeout Interval with Query Re-attempt Logic

As per a real use case, we can use the lock_timeout to check for blocking that exceeds a certain limit and instead of failing the process due to the lock_timeout or waiting indefinitely for the locks to become free, we can set a delay and re-attempt the command again.  This could also be helpful for deadlock situations.

Sample Script

The following creates a test database and a table with data.

USE Master
GO
CREATE DATABASE LockInterval
GO
USE LockInterval
GO
CREATE TABLE BTest (
  id bigint,
  value1 int,
)
GO
INSERT INTO BTest
  SELECT
    10251478,
    585471
GO 1000

Now I am going to write a DDL operation (doing an add column) using the lock_timeout setting, in addition I will use five query re-attempts in case the process is blocked for more than 10 seconds.

DECLARE @counter int = 1, @is_success int = 0

WHILE @counter <= 5 AND @is_success = 0
BEGIN

  BEGIN TRY
    BEGIN TRANSACTION
      SET LOCK_TIMEOUT 10000
      ALTER TABLE BTest ADD value2 varchar(200)
    COMMIT TRANSACTION
    SET @is_success = 1 --- Need tp set to exit this loop 
  END TRY

  BEGIN CATCH
    IF @@TRANCOUNT > 0
    BEGIN
      ROLLBACK TRANSACTION
    END
    IF ERROR_NUMBER() IN
       (
        1204, -- SQLOUTOFLOCKS 
        1205, -- SQLDEADLOCKVICTIM 
        1222  -- SQLREQUESTTIMEOUT 
       )
      AND @counter NOT IN (5)
    BEGIN
      SET @counter = @counter + 1 -- Re-attempt counter 
      WAITFOR DELAY '00:00:05'  -- 5 second delay for next re-attempt 
    END
    ELSE
    BEGIN
      THROW;
    END
  END CATCH

END

Below is execution of the script.

The above finished in less than 1 second, which means there was no locking contention with other processes. 

In the script, I used a transaction with a try-catch block for evaluating the blocking issue. I used variable @counter with a value 5 which means when an error is raised related to the lock time out, request time out or deadlock the counter will increment by 1.  After waiting 5 seconds, then try again. The entire process will fail when the counter’s value is exceeded. If the entire process fails, you can just run the entire process again until it is successful.

Below are the error numbers and description.

• 1204 - The instance of the SQL Server Database Engine cannot obtain a LOCK resource at this time
• 1205 - Transaction was deadlocked on resources with another process and has been chosen as the deadlock victim
• 1222 - Lock request time out period exceeded

Sample SQL Server Process with Blocking Contention

I would like to demonstrate the process where the error is generated.

In the database we created I am going to execute an update query using with transaction as follows, but not commit the transaction so the locks stay open.

In another session, I am going to add value3 as a new column to the same table with using the code below.

The first query was holding update locks and the second query tried to alter the table, but failed due to the lock request time out period. The process tried for each of the iterations, but since the first query was never committed or rolled back, the second query errored with the lock request time out period exceeded error.

Next Steps

• Understanding and resolving SQL Server Blocking issue.
• How to identify blocking in SQL Server.
• Set Statements in SQL Server.
• Check out Error_number in SQL Server.
• SQL Server Locking and Blocking Tips.

About the author

Bhavesh Patel is a SQL Server database professional with 10+ years of experience.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips