Remix.run Logo
hilariously 2 days 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 2 days ago | parent [-]

What’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.

jdmichal 16 hours ago | parent | next [-]

Continuing 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.

hilariously a day ago | parent | prev [-]

Sure, you'd need a better key I am just saying for example.