I am getting difficulties when trying to understand how can version-based optimistic locking prevent "last-commit-wins" issue and appropriate overriding.
To make the question more concrete, let's consider the following pseudo-code that uses JDBC:
connection.setAutoCommit(false);
Account account = select(id);
if (account.getBalance() >= amount) {
account.setBalance(account.getBalance() - amount);
}
int rowsUpdated = update(account); // version=:oldVer+1 WHERE version=:oldVer
if (rowsUpdated == 0) throw new OptimisticLockException();
connection.commit();
Here what if other transaction would commit its change right between the update and the commit ? If the transactions are concurrent, then the update made by the first transaction is not yet committed and so not visible to the second transaction (with proper isolation levels) and so the first transaction commit will override the changes of the second transaction without any notification or error.
Is this the case that optimistic locking just decrease the probability of the issue while not preventing it in general ?
the idea of a database "transaction" is that it is supposed to provide a guarantee of "consistency" across multiple conceptual operations. the database is in charge of enforcing this. so, when the transaction commits, the database should only allow the transaction to complete if it can ensure that everything that happened during the transaction is still valid.
In practice, a database will typically handle this such that once one of the updates succeeds, the relevant row will be write locked until the relevant transaction completes. Thus, one of the updates is guaranteed to fail.
Note: this also requires an appropriate isolation level in your jdbc connection. the isolation level ensures that the test for the current value done before the update is still applicable at the time of the write.