Generally I would recommend an append only data structure here, not a bunch of updates
BEGIN TRANSACTION;
IF EXISTS (
SELECT 1
FROM account_balances WITH (UPDLOCK, HOLDLOCK)
WHERE owner = 'alice'
AND balance >= 10
)
BEGIN
INSERT INTO account_ledger (owner, amount, memo)
VALUES
('alice', -10, 'transfer to bob'),
('bob', 10, 'transfer from alice');
END
COMMIT TRANSACTION;
or if I wanted to use the update pattern since I am taking the lock anyway
BEGIN TRANSACTION;
UPDATE accounts WITH (UPDLOCK)
SET balance = balance - 10
WHERE owner = 'alice'
AND balance >= 10;
IF @@ROWCOUNT = 1
BEGIN
UPDATE accounts
SET balance = balance + 10
WHERE owner = 'bob';
END
COMMIT TRANSACTION;
