3 ms·
while true this is not what I mean. if your goal is to transfer 10 dollars for account A to account B but only if account A balance is larger than 10 dollars.
by skyde 3y ago
while true this is not what I mean.
if your goal is to transfer 10 dollars for account A to account B
but only if account A balance is larger than 10 dollars.
You have to update account A to be 10 dollars less
and
you have to update account B to be 10 dollars more.
you can read both account a keep track of current version number for each.
then do
"UPDATE Accounts SET Balance = CASE AccountID WHEN 1 THEN Balance - 10 WHEN 2 THEN Balance + 10 END WHERE (AccountID, version) IN ((1,v1), (2,v2))"
But if using Read Uncommited isolation level I am not sure this UPDATE would actually lock both row until commit.
- skyde 3y agoyou still have to do IF @@ROWCOUNT = 2 BEGIN COMMIT TRANSACTION; END ELSE BEGIN ROLLBACK TRANSACTION; END to make sure both rows have been updated. so its a lot simpler to explicitly take a lock on the rows.
- giovannibonetti 3y agoYou're right. Locking the rows or using serializable isolation would be required to achieve an atomic operation if and only if both accounts are in the pristine state before it.