3 ms·

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 selec

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

amw-zero 3y ago
Probably because it’s more work, and it’s logic that you have to write and get correct. Whereas “for update” pretty much just works. But there are definitely many valid solutions, including just setting the specific transaction to serializable and handling transaction retries.
- GauntletWizard 3y ago
  and handling retries is something I almost never see, and more infrequently see done right.
- gav 3y ago
  Optimistic locking is a great solution to avoid holding a lock for an indeterminate period of time as you wait for a user or external system to provide updates.
hinkley 3y ago
I don’t know about databases but I’ve done similar with testing interactive web apps and it works quite well. When your architecture supports it, at least. 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.
skyde 3y ago
because row version does not make it possible to update more than 1 row atomically.
- giovannibonetti 3y ago
  Yes it does. UPDATE foo = 'abc' WHERE (id, version) IN ((123, 1), (234, 2))
  - 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 ago
      you 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 ago
        You'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.

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

amw-zero 3y ago

Probably because it’s more work, and it’s logic that you have to write and get correct. Whereas “for update” pretty much just works. But there are definitely many valid solutions, including just setting the specific transaction to serializable and handling transaction retries.

GauntletWizard 3y ago
and handling retries is something I almost never see, and more infrequently see done right.
gav 3y ago
Optimistic locking is a great solution to avoid holding a lock for an indeterminate period of time as you wait for a user or external system to provide updates.

hinkley 3y ago

I don’t know about databases but I’ve done similar with testing interactive web apps and it works quite well. When your architecture supports it, at least. 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.

skyde 3y ago

because row version does not make it possible to update more than 1 row atomically.

giovannibonetti 3y ago
Yes it does. UPDATE foo = 'abc' WHERE (id, version) IN ((123, 1), (234, 2))
- 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 ago
    you 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 ago
      You'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.