A Retry Loop in T-SQL for Deadlock Victims and Lock Timeouts

A deadlock victim can retry successfully after the competing transaction finishes. A retry loop should repeat the complete unit of work, limit its attempts, and preserve the error when that limit is reached.

Retry the Transaction You Can Safely Repeat

Error 1205 identifies a deadlock victim. SQL Server rolls back that victim's transaction to break the cycle. Error 1222 identifies a lock timeout, which does not provide the same automatic whole-transaction rollback guarantee.

Handle both by cleaning up any remaining transaction before another attempt. A retry of only the failed statement can omit earlier work that was rolled back. The unit being repeated must therefore match the transaction's business unit.

I check for external side effects before adding retries. A database rollback cannot retract a notification already delivered elsewhere. Keep the repeated work inside a transaction or coordinate external work through a separate durable design.

The example uses THROW, available starting with SQL Server 2012. It owns its transaction and rejects an existing outer transaction. That keeps a rollback from unexpectedly undoing the caller's unrelated work.

A retry is a recovery response to an occasional conflict. It is not a cure for inconsistent access order or transactions held open unnecessarily. Keep the original cause visible while improving the application's response.

Prepare Two Rows for a Controlled Test

Use a disposable database. The balances are demonstration inputs, not a measured financial workload. Both rows participate in one transaction so a failed attempt must leave neither partial adjustment committed.

CREATE TABLE dbo.RetryBalanceDemo
(
    AccountID int NOT NULL PRIMARY KEY,
    Balance decimal(12,2) NOT NULL
);
INSERT dbo.RetryBalanceDemo VALUES (1, 100.00), (2, 100.00);
CREATE TABLE #RetryDiagnostics
(
    AttemptNumber int NOT NULL,
    ErrorNumber int NOT NULL,
    RecordedAt datetime2(7) NOT NULL
);

The retry diagnostics table belongs to the current test session. It shows the attempts without pretending to be durable production logging. A production caller needs an approved logging path that survives disconnection and records request identity.

Keep the transfer values and account identifiers fixed throughout one retry cycle. A later attempt should repeat the same request. Fetching a new business input halfway through the loop changes what the caller originally asked to do.

Bound the Retry Loop and Reset the Lock Timeout

The example sets a short test timeout so conflicts are visible promptly. SET LOCK_TIMEOUT accepts a number, not a variable, so the loop returns to -1, the default wait without a limit. It does that on success and before throwing an unrecoverable or exhausted error. Check @@LOCK_TIMEOUT first if your session normally uses another value.

IF @@TRANCOUNT <> 0
    THROW 50030, 'Run the test without an existing transaction.', 1;
DECLARE @Attempt int = 0, @MaximumAttempts int = 3;
SET XACT_ABORT ON;
SET LOCK_TIMEOUT 1000;
WHILE 1 = 1
BEGIN
    SET @Attempt += 1;
    BEGIN TRY
        BEGIN TRANSACTION;
        UPDATE dbo.RetryBalanceDemo SET Balance -= 10 WHERE AccountID = 1;
        UPDATE dbo.RetryBalanceDemo SET Balance += 10 WHERE AccountID = 2;
        COMMIT TRANSACTION;
        BREAK;
    END TRY
    BEGIN CATCH
        DECLARE @Failure int = ERROR_NUMBER();
        IF XACT_STATE() <> 0 ROLLBACK TRANSACTION;
        INSERT #RetryDiagnostics VALUES (@Attempt, @Failure, SYSUTCDATETIME());
        IF @Failure NOT IN (1205, 1222) OR @Attempt >= @MaximumAttempts
        BEGIN
            SET LOCK_TIMEOUT -1;
            THROW;
        END;
        DECLARE @Pause varchar(8) = CONVERT(varchar(8),
            DATEADD(second, @Attempt, CONVERT(time(0), '00:00:00')), 108);
        WAITFOR DELAY @Pause;
    END CATCH;
END;
SET LOCK_TIMEOUT -1;
SELECT * FROM #RetryDiagnostics ORDER BY AttemptNumber;

Only 1205 and 1222 qualify for another attempt. Constraint violations, permission failures, and other errors are thrown immediately. A broad catch that retries everything converts clear failures into delayed clear failures.

The retry limit includes the initial attempt. Backoff grows between failed attempts, reducing immediate collisions with competing work. Production systems can use caller-side randomized backoff where many requests otherwise retry together.

XACT_ABORT ON improves handling of transaction-ending errors, but explicit XACT_STATE cleanup remains essential. This sample leaves that setting enabled for the test connection. Use a dedicated connection or restore the broader session configuration through your normal application boundary.

Reproduce Opposite Access Order in Two Windows

A deadlock test needs competing transactions holding resources the other needs. In one test window, update account 1 and then account 2. In another, reverse that order. The pauses below arrange an overlap; they are test inputs, not measured durations.

-- Test window A, in the disposable database.
BEGIN TRY
    BEGIN TRANSACTION;
    UPDATE dbo.RetryBalanceDemo SET Balance += 1 WHERE AccountID = 1;
    WAITFOR DELAY '00:00:05';
    UPDATE dbo.RetryBalanceDemo SET Balance -= 1 WHERE AccountID = 2;
    COMMIT TRANSACTION;
END TRY
BEGIN CATCH
    IF XACT_STATE() <> 0 ROLLBACK TRANSACTION;
    THROW;
END CATCH;

-- Test window B, start while window A is paused.
BEGIN TRY
    BEGIN TRANSACTION;
    UPDATE dbo.RetryBalanceDemo SET Balance += 1 WHERE AccountID = 2;
    WAITFOR DELAY '00:00:05';
    UPDATE dbo.RetryBalanceDemo SET Balance -= 1 WHERE AccountID = 1;
    COMMIT TRANSACTION;
END TRY
BEGIN CATCH
    IF XACT_STATE() <> 0 ROLLBACK TRANSACTION;
    THROW;
END CATCH;

The engine chooses a victim when a cycle forms. Do not assume the same test window always loses. Record the actual error and inspect the transaction outcome rather than narrating a result that was not observed.

Then test the retry loop against controlled competing activity. Keep the retry connection's timeout in mind: a lock timeout can occur before a deadlock forms. Test those two error paths separately so both cleanup behaviors are verified.

Check the Whole Outcome After the Retry Loop

Inspect balances and diagnostics after the test. Confirm that a successful request applied once, and that an exhausted request did not leave a partial transfer. An execution that returned without an error still needs its business invariants checked.

SELECT AccountID, Balance FROM dbo.RetryBalanceDemo ORDER BY AccountID;
SELECT SUM(Balance) AS CombinedBalance FROM dbo.RetryBalanceDemo;
SELECT AttemptNumber, ErrorNumber, RecordedAt FROM #RetryDiagnostics ORDER BY AttemptNumber;

The combined-balance check is appropriate to this transfer demonstration. Real work needs its own invariants. A single affected-row count cannot establish that every related table remains consistent.

Keep the Cause Visible After Success

I record successful retries as well as exhausted failures. Otherwise, growing contention disappears from the dashboard because callers eventually succeed. The server can be spending substantial work on repeated attempts while appearing error-free.

Which access order creates the conflict? Align it across competing transactions where possible, shorten transaction duration, and inspect supporting indexes. Retrying should accompany that investigation rather than postpone it indefinitely.

Move the Retry Loop to the Caller Across Boundaries

A T-SQL loop is useful for a contained database operation. A request involving several services needs a coordinated caller-level retry policy. Retrying several layers independently can multiply attempts and produce unpredictable delays.

Set one clear responsibility for retry count, backoff, and final error reporting. A retry loop is patient enough to repeat a mistake, so give it a limit and an operation that remains safe to repeat.

Include a Request Deadline and Business Checks

Choose the attempt limit and backoff with the caller's deadline in mind. Three attempts can still exceed a short request timeout when each attempt waits on locks and performs substantial work. A bounded loop limits attempts, but its total duration also depends on the transaction and delay policy.

Check affected rows when a transaction expects specific records. The demonstration assumes both sample accounts exist. Production work should reject missing targets and enforce its own balance or quantity rules inside the transaction. A retry must not convert an invalid business operation into repeated partial attempts.

Keep transactions closed during the backoff delay. The catch rolls back before WAITFOR, so it does not keep the failed attempt's locks while pausing. Holding a transaction open during that pause would extend the same contention you are trying to recover from.

Review the caller's own retry behavior too. If the database retries three times and the caller retries the whole request repeatedly, the combined attempts exceed the visible database limit. Document one coherent policy and log enough request identity to connect its attempts.

Related reading on this blog: SQL Server Deadlock: Build One With Your Own Hands and Resolving Deadlock by Accessing Objects in the Same Order.

A retry loop is not a contention fix, it is a bounded way to repeat safe work while its cause remains visible.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.