Why should you care?

Retries help, but prevention stops customer-facing failures.

See it live — copy this example

Run in SQL Server Management Studio (SSMS) or Azure Data Studio.

USE DataVerse;
CREATE OR ALTER PROCEDURE dbo.usp_TransferOrdered
    @FromId INT, @ToId INT, @Amount DECIMAL(18,2)
AS
BEGIN
    SET NOCOUNT ON; SET XACT_ABORT ON;
    DECLARE @First INT = CASE WHEN @FromId < @ToId THEN @FromId ELSE @ToId END;
    DECLARE @Second INT = CASE WHEN @FromId < @ToId THEN @ToId ELSE @FromId END;
    BEGIN TRAN;
    UPDATE dbo.Accounts WITH (UPDLOCK, ROWLOCK) SET Balance = Balance WHERE AccountId = @First;
    UPDATE dbo.Accounts WITH (UPDLOCK, ROWLOCK) SET Balance = Balance WHERE AccountId = @Second;
    UPDATE dbo.Accounts SET Balance = Balance - @Amount WHERE AccountId = @FromId;
    UPDATE dbo.Accounts SET Balance = Balance + @Amount WHERE AccountId = @ToId;
    COMMIT;
END

What happened?

• Regardless of transfer direction, locks are acquired by ascending AccountId first.
• That removes the classic deadlock cycle between two transfers.

Practice next

1. Create usp_TransferOrdered.
2. Run concurrent transfers in opposite directions in lab.
3. Compare to unordered locking.
4. Remove the ordered pre-lock and try to reproduce deadlock.
5. Log victims to an audit table.

Remember

One lock order for all writers. Short transactions. Retry as backup, not the only strategy.

Ordered account locks

All DataVerse money procs lock by AccountId ASC.

Outcome: Deadlock rate collapses under load tests.