If the row in question requires repeatable-read or serialization semantics, this update must be
performed even if the data was only read during the transaction—those isolation levels re-
quire locking read-only data used in a transaction. For read-committed semantics, the version
column only needs to be updated when other data in the row is also updated.
Under this scheme, if two transactions use my employee record at the same time, each will
read a version number of 1012. The first transaction to complete will successfully update the
version number to 1013 and continue. The second transaction will not be able to update the
employee record—there is no longer any record where the version number is 1012, so the
SQL update statement will fail. That transaction will get an exception and be rolled back.
This highlights a major difference between optimistic locking in the database and Java's
atomic primitives: in database programming, when the transaction gets that exception, it is
not (and cannot be) transparently retried. If you are programming directly to JDBC, the com-
mit() method will get an SQLException ; in JPA, your application will get an Optimist-
icLockException when the transaction is committed.
Depending on your perspective, this is either a good or a bad thing. When the performance of
the atomic utilities (which did transparently retry the operation) was discussed, I observed
that performance in highly contended cases could suffer when there were a lot of retries
chewing up a lot of CPU resources. In a database, that situation is far worse, since the code
executed in a transaction is far more complicated than simply incrementing the value held in
a memory location. Retrying a failed optimistic transaction in the database has a far greater
potential to lead to a never-ending spiral of retries. Plus, it is often infeasible to determine
automatically what operation(s) to retry.
So not retrying transparently is a good thing (and often the only possible solution), but on the
other hand, that does mean the application is now responsible for handling the exception.
The application can choose to retry the transaction (maybe only once or twice), it can choose
to prompt the user for different data, or it can simply inform the user that the operation has
failed. There is no one-size-fits-all answer here.
Optimistic locking works best, then, when there is very little chance of a collision between
two sources. Think of a joint checking account: there is a slight chance that my husband and
I may be in different parts of the city withdrawing money from our checking account at ex-
actly the same time. That would trigger an optimistic lock exception for one of us. Even if
that does happen, asking one of us to try again is not too onerous, and now the chance of an
optimistic lock exception is virtually nil (or so I would hope; let's not address how frequently
we make ATM withdrawals). Contrast that scenario to something involving the sample stock
application. In the real world, that data is updated so frequently that locking it optimistically