Is Optimistic-Locking absolutely safe?

Question

when using optimistic-locking strategy, it can solve concurrency problem like 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 the extremely rare cases if the update in the second transaction is after the udpate in the first transaction but before the the transaction commit?

| 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 made an experiment that the update in the second transaction could not read the 'dirty' version because the first transaction had not been committed yet. Will the second transaction fail in this case?

Answer 1

You didn't say in your question what database system you're actually using, so I don't know the details of your system.

But in any case, under an optimistic locking system, a process cannot just check the row versions when it performs the update statement, because of exactly the problem you are worried about.

For fully serializable, isolated transactions, each process must atomically check the row versions of all the rows it examined and modified, at commit time. So in your second scenario, the right-hand process will not detect a conflict until it tries to commit (a step which you did not include for the right-hand process). When it tries to commit, it will detect the conflict and roll back.

Answer 2

As you've already discovered, Optimistic Locking is subject to TOCTOU race condition: before the commit decision and the actual commit, there is a short time window during which another transaction can modify the data.

To make Optimistic Locking 100% safe, you have to make sure that the second transaction waits until the first transaction commits and only then makes a version check:

You can achieve this by acquiring a row-level (select for update) lock prior to the update statement.

jOOQ does that for you. In Hibernate, you have to lock the row manually:

var pessimisticRead = new LockOptions(LockMode.PESSIMISTIC_READ);
session.buildLockRequest(pessimisticRead).lock(entity);

Beware that you can't reproduce that annoying TOCTOU race condition in Hibernate on a single VM. Hibernate will smoothly resolve this thanks to the shared Persistent Context. When transactions run on different VMs, Hibernate can't help, and you'll have to add extra locking.