2 ms·
Generally I would recommend an append only data structure here, not a bunch of updates BEGIN TRANSACTION; IF EXISTS ( SELECT 1 FROM account_balanc
by hilariously 6mo ago
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;
- LgWoodenBadger 6mo agoWhat’s amusing is you’ve now stolen $10 from all of Alice’s accounts if she has more than one, or if Bob has none.
- hilariously 5mo agoSure, you'd need a better key I am just saying for example.
- jdmichal 5mo agoContinuing the pattern of existing examples (in this case, as present in the article) is a perfectly normal thing to do. I think everyone can make the mental leap that a proper account ID would be used instead of the owner's name in an actual setup.