Why should you care?

This is the reference workload for SQL Server concurrency — get it right once and reuse the pattern.

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_TransferFunds
    @FromId INT, @ToId INT, @Amount DECIMAL(18,2)
AS
BEGIN
    SET NOCOUNT ON; SET XACT_ABORT ON;
    IF @Amount <= 0 THROW 50001, 'Amount must be positive.', 1;
    IF @FromId = @ToId THROW 50002, 'Accounts must differ.', 1;
    DECLARE @First INT = IIF(@FromId < @ToId, @FromId, @ToId);
    DECLARE @Second INT = IIF(@FromId < @ToId, @ToId, @FromId);
    BEGIN TRY
        BEGIN TRAN;
        UPDATE dbo.Accounts WITH (UPDLOCK, ROWLOCK) SET Balance = Balance WHERE AccountId IN (@First,@Second);
        UPDATE dbo.Accounts SET Balance = Balance - @Amount WHERE AccountId = @FromId AND Balance >= @Amount;
        IF @@ROWCOUNT <> 1 THROW 50003, 'Insufficient funds or missing account.', 1;
        UPDATE dbo.Accounts SET Balance = Balance + @Amount WHERE AccountId = @ToId;
        IF @@ROWCOUNT <> 1 THROW 50004, 'Destination account missing.', 1;
        COMMIT;
    END TRY
    BEGIN CATCH
        IF @@TRANCOUNT > 0 ROLLBACK;
        THROW;
    END CATCH
END

What happened?

• Validates input, locks accounts in order, debits only if enough balance, credits destination, rolls back on any failure.
• Extend with an audit insert in the same tran for production.

Practice next

1. Ensure Accounts CHECK (Balance >= 0).
2. Create and test usp_TransferFunds.
3. Try overdraft and confirm THROW/rollback.
4. Return new balances as a result set.
5. Add idempotency key column for retries.

Remember

Validate → lock ordered → debit → credit → audit → commit. Rowcount checks catch missing rows. Rollback on any failure.

DataVerse wallet transfer

UPI-like wallet moves INR between accounts with this proc pattern.

Outcome: Zero silent balance corruption in soak tests.