Why optimistic locking is never considered in those articles ? Adding a version column and checking the affected numbers of row scales a lot better that a select for update...
But there are definitely many valid solutions, including just setting the specific transaction to serializable and handling transaction retries.
Essentially a hidden field on the page that tracks interactions and outstanding Ajax calls. When you assert that a field on the page hasn’t changed, you need to know that it’s not still in the process of painting. And just waiting “long enough” makes for very slow testing overall.
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.
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.