Using Lock Timeouts To Avoid Deadlocks In SQL Server

Try And Retry

I’ll sometimes see people implement retry logic to catch deadlocks, which isn’t a terrible idea by itself. The problem that may arise is when the deadlock monitor takes a full 5 seconds to catch a query, which can block other queries, and may generally make things feel slower.

Different Locks


An alternative is to set a lock timeout that’s shorter than five seconds.

DECLARE @lock_try INT = 0

WHILE @lock_try < 5 
BEGIN
    BEGIN TRY

        SET LOCK_TIMEOUT 5; /*five milliseconds*/

        SELECT COUNT(*) AS records FROM dbo.Users AS u;

    END TRY
    BEGIN CATCH

        IF ERROR_NUMBER() <> 1222 /*Lock request time out period exceeded.*/
		RETURN;

    END CATCH;

SET @lock_try += 1;

WAITFOR DELAY '00:00:01.000' /*Wait a second and try again*/

END;

While 5 milliseconds is maybe an unreasonably short time to wait for a lock, I’d rather you start low and go high if you’re trying this at home. The catch block is set up to break if we hit an error other than 1222, which is what gets thrown when a lock request times out.

This is a better pattern than just hitting a deadlock, or just waiting for a deadlock to retry. Normally when a deadlock occurs, one query throws an error, and there’s no attempt to try it again (unless a user is sitting there hitting submit until something works). Waiting ~5 seconds (I know I’m simplifying here, and the deadlock monitor will wake up more frequently after it detects one)

The big question is: are you better off doing this in T-SQL than in your application?

Thanks for reading!

Going Further


If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that and need to solve performance problems quickly.



One thought on “Using Lock Timeouts To Avoid Deadlocks In SQL Server

  1. Wow, I’ve never considered this setting.
    In our case, we’ve really reduced our deadlocks since adopting RCSI. And for those areas of our code that still seem susceptible to deadlocks, we do have a retry mechanism (implemented in the app).
    At the moment it works because, the alternative right now is to throw an error and in practice, these deadlocks usually occur when other blocking and worse problems are happening.
    So deadlock retries happening too slowly hasn’t been the principal problem (at least not yet!)

    The question you asked: should the retry happen in the App or in SQL?
    Normally, I’d say the app, except the `SET LOCK_TIMEOUT` command needs to be SQL. So maybe SQL?

Comments are closed.