Optimistic - Is Blocking Safe?

Question

using an optimization strategy with optimization can solve the concurrency problem as shown below:

  |  the first transaction started |
 |  |  
 |  select a row |
 |  |  the second transaction started
 |  update the row with version checking |
 |  |  select the same row 
 |  commit txn |
 |  |  update the row with version checking
 |  |  
 |  |  rolls back because version is dirty

But what if, in extremely rare cases, if the update is in the second transaction after udpate in the first transaction, but before the transaction is completed?

  |  the first transaction started |
 |  |  the second transaction started
 |  select a row |
 |  |  select the same row 
 |  update the row with version checking |
 |  |  update the row with version checking
 |  commit txn |
 |  |  rolls back because version is dirty // will it?
 |  |  
 |  |  

I did an experiment according to which the update in the second transaction cannot read the dirty version, since the first transaction has not yet been completed. Will the second transaction fail in this case?

Answer 1

You did not say in your question which database system you are using, so I do not know the details of your system.

But in any case, with an optimistic locking system, the process cannot just check the version of the lines when it executes the update statement, because of the exact problem you are worried about.

For fully serializable isolated transactions, each process must atomically check the row versions of all the rows that it examined and modified at the time of commit. Thus, in your second scenario, the right process will not detect a conflict until it tries to complete it (a step that you did not enable for the right process). When he tries to fix, he will find a conflict and a rollback.