A request that needs a quick answer should not wait indefinitely behind another transaction. LOCK_TIMEOUT sets a limit on lock waiting, then error handling decides what happens to the unfinished work.
Pick a LOCK_TIMEOUT Value for This Work
A lock wait is only one part of request time. SET LOCK_TIMEOUT controls how long a statement waits for a lock to become available. Its unit is milliseconds. The default value, minus one, permits indefinite waiting. Zero returns an error immediately when the required lock is unavailable.
Choose the value from the operation's needs. A quick interactive action and a scheduled maintenance task have different patience budgets. A smaller setting does not remove the blocker or make the locked resource available. It lets the waiting operation stop sooner.
I check the current value before diagnosing a sudden stream of lock timeouts. A setting left on a long-lived session can affect later work. Use @@LOCK_TIMEOUT to inspect it, and restore temporary overrides when a diagnostic script ends. A short fuse is useful only when everybody knows it exists.
SELECT @@SPID AS SessionID, @@LOCK_TIMEOUT AS LockTimeoutMs;
SET LOCK_TIMEOUT 1500;
SELECT @@LOCK_TIMEOUT AS LockTimeoutMs;
SET LOCK_TIMEOUT -1;Create a Two-Session Test
Use one scratch database and two SSMS query windows connected to it. The first creates a shared permanent table, because local temporary tables are not visible in the other session. Create it once and keep the supplied name separate from application objects.
The table has two independent keys. Session B changes the second row before attempting the first. That earlier successful write makes the transaction-cleanup issue visible. A single failing statement in autocommit mode would hide the more interesting part of this demonstration.
CREATE TABLE dbo.LockTimeoutDemo
(
ItemID int NOT NULL PRIMARY KEY,
ValueAmount int NOT NULL
);
INSERT dbo.LockTimeoutDemo VALUES (1, 100), (2, 200);
SELECT ItemID, ValueAmount FROM dbo.LockTimeoutDemo ORDER BY ItemID;The Blocking Session Holds the Required Lock
Run the next block in Session A with no existing transaction. Leave the transaction open deliberately while running Session B's block. The first row's update remains uncommitted and owns the incompatible write lock. Session A's identifier helps confirm which connection is the blocker.
Keep this demonstration in the scratch database. Do not leave the window unattended after the test. Its final cleanup appears later in a separate block, because including an immediate rollback here would release the lock before Session B tried to acquire it.
IF @@TRANCOUNT <> 0
THROW 50000, 'Use Session A without an existing transaction.', 1;
BEGIN TRANSACTION;
UPDATE dbo.LockTimeoutDemo
SET ValueAmount = ValueAmount + 1
WHERE ItemID = 1;
SELECT @@SPID AS BlockingSessionID, @@TRANCOUNT AS TransactionCount;Session B Hits LOCK_TIMEOUT and Error 1222
Run this complete block in Session B while Session A still holds the lock. The diagnostic setting is 1500 milliseconds. That is the requested wait budget, not a measured elapsed duration. The UPDATE on ItemID 1 encounters the incompatible lock and fails with error 1222.
This example sets XACT_ABORT OFF to expose statement-level failure behavior. Inside CATCH, the transaction remains active for this lock-timeout case. The earlier update on ItemID 2 still belongs to it. Error 1222 by itself does not roll the complete transaction back.
The handler rolls back any active transaction. That SET statement accepts a number but not a variable, so the block returns to the default of -1 and restores XACT_ABORT from @@OPTIONS. It prints the caught error for teaching purposes. Production code should propagate a final failure rather than returning success because cleanup finished. Inspect both XACT_STATE and @@TRANCOUNT before deciding how to unwind work.
IF @@TRANCOUNT <> 0
THROW 50001, 'Use Session B without an existing transaction.', 1;
DECLARE @PreviousXactAbort bit =
CASE WHEN (@@OPTIONS & 16384) = 16384 THEN 1 ELSE 0 END;
SET LOCK_TIMEOUT 1500;
SET XACT_ABORT OFF;
BEGIN TRY
BEGIN TRANSACTION;
UPDATE dbo.LockTimeoutDemo
SET ValueAmount = ValueAmount + 1 WHERE ItemID = 2;
UPDATE dbo.LockTimeoutDemo
SET ValueAmount = ValueAmount + 1 WHERE ItemID = 1;
COMMIT TRANSACTION;
END TRY
BEGIN CATCH
SELECT ERROR_NUMBER() AS ErrorNumber, ERROR_MESSAGE() AS ErrorMessage,
@@TRANCOUNT AS TransactionCount, XACT_STATE() AS TransactionState;
IF XACT_STATE() <> 0 ROLLBACK TRANSACTION;
END CATCH;
SET LOCK_TIMEOUT -1;
IF @PreviousXactAbort = 1 SET XACT_ABORT ON;
ELSE SET XACT_ABORT OFF;
SELECT @@TRANCOUNT AS TransactionCount, XACT_STATE() AS TransactionState;
Retry the Whole Transaction Carefully
A retry must begin after cleanup. Repeating only the failed statement while earlier work remains pending creates ambiguous business behavior. The next block starts a new transaction for each attempt. It retries only error 1222, allows three attempts, and waits briefly between attempts.
Run it in Session B, again without an ambient transaction. If Session A keeps its lock, all three attempts time out and the block ends with error 1222. Release Session A during the test to allow an attempt to finish. The delay is a demonstration policy, not a universal production recommendation.
I keep retries bounded and visible. Unlimited retries turn a fast-failing request into a very patient hidden loop. In application code, use backoff with jitter, an overall deadline, and logs showing the attempts. Retrying a lock timeout does not justify retrying every possible error.
IF @@TRANCOUNT <> 0
THROW 50002, 'Run the retry demo outside an existing transaction.', 1;
DECLARE @SavedXactAbort bit =
CASE WHEN (@@OPTIONS & 16384) = 16384 THEN 1 ELSE 0 END;
DECLARE @Attempt int = 0;
SET LOCK_TIMEOUT 1000;
SET XACT_ABORT ON;
WHILE @Attempt < 3
BEGIN
SET @Attempt += 1;
BEGIN TRY
BEGIN TRANSACTION;
UPDATE dbo.LockTimeoutDemo
SET ValueAmount = ValueAmount + 1 WHERE ItemID = 1;
COMMIT TRANSACTION;
BREAK;
END TRY
BEGIN CATCH
DECLARE @ErrorNumber int = ERROR_NUMBER();
IF XACT_STATE() <> 0 ROLLBACK TRANSACTION;
IF @ErrorNumber <> 1222 OR @Attempt = 3
BEGIN
SET LOCK_TIMEOUT -1;
IF @SavedXactAbort = 1 SET XACT_ABORT ON;
ELSE SET XACT_ABORT OFF;
THROW;
END;
WAITFOR DELAY '00:00:00.250';
END CATCH;
END;
SET LOCK_TIMEOUT -1;
IF @SavedXactAbort = 1 SET XACT_ABORT ON;
ELSE SET XACT_ABORT OFF;
SELECT @Attempt AS AttemptsUsed, @@TRANCOUNT AS TransactionCount;Separate LOCK_TIMEOUT From Client Command Timeouts
An application command timeout limits the client's wait for command completion. It covers more than locking, including execution and other delays. Its configuration commonly uses seconds, whereas LOCK_TIMEOUT uses milliseconds. Confirm the unit in the actual client API before copying a number.
A client cancellation also has different error and cleanup behavior. It is not guaranteed to appear as server error 1222. TRY CATCH does not catch a client attention in the same way as this server-generated exception. The application must handle cancellation and connection transaction state deliberately.
Does your retry budget fit inside the caller's overall deadline? Count attempts, lock waits, backoff, and other execution time together. Otherwise the caller gives up while the server-side policy still expects another attempt. Coordinated budgets make failure predictable and easier to explain.
Look at the Blocker Before Raising the Limit
Repeated lock timeouts point to recurring contention. Inspect active requests, open transactions, and the blocking chain. A longer limit makes the symptom quieter while the request waits longer. It does not fix an unnecessarily broad update or a transaction waiting for external work.
READPAST and row-versioned reads have different semantics from timing out. Skipping locked rows can be correct for a designed work queue, but wrong for a complete financial report. Read isolation changes also do not remove write-write contention. Pick the remedy from the operation's correctness requirements.
Record the timed-out statement and the blocker when possible. Correlate that evidence with transaction duration and execution plans. Avoid automatically terminating every blocker. A needed transaction still has business ownership, and rollback can take time.
Finish by Releasing the Blocking Session
Return to Session A and run the following rollback. It removes the deliberately held update and releases its locks. Then check the shared table from either window. A successful retry can have changed ItemID 1, while Session B's failed demonstration rolled back its earlier ItemID 2 change.
Keep the setting as a controlled behavior in the write path, with cleanup and bounded retries beside it. The goal is a defined failure contract. When waiting stops, the transaction and the caller both need an honest answer.
IF XACT_STATE() <> 0 ROLLBACK TRANSACTION;
SELECT @@TRANCOUNT AS TransactionCount, XACT_STATE() AS TransactionState;
SELECT ItemID, ValueAmount FROM dbo.LockTimeoutDemo ORDER BY ItemID;Related reading on this blog: SET XACT_ABORT ON: Stopping Timeouts From Leaving Open Transactions and Optimized Locking in SQL Server 2025: What Changes for Blocking.
A lock timeout is not transaction cleanup, it is a limit on waiting for a lock.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
