Database transactions and concurrency - POJOs in Action: Developing Enterprise Applications with Lightweight Frameworks - page 456

Oracle, unlike Java, an empty string is considered to be null. We solved this prob-
lem by adding a version column to the table.
Another drawback is that floating-point columns cannot be compared pre-
cisely and changes to them may not be detected. Because of these issues, an
application should only use this approach if it not possible to add a version or
timestamp column.
Efficiently implementing the optimistic locking check
A JDBC/ i BATIS application or a persistence framework can efficiently implement
the optimistic locking check by incorporating it into the UPDATE statement that
updates the row. For example, here is an UPDATE statement that updates an order
and uses a version column to detect changes:
UPDATE PLACED_ORDER
SET VERSION = VERSION + 1,
STATUS = 'SENT'
WHERE ORDER_ID = ? AND VERSION = ?
This UPDATE statement changes the state of the order and increments the version
number. Its WHERE clause checks that the version number is unchanged. If another
transaction changed or deleted the order, the UPDATE statement would not update
any rows and the JDBC PreparedStatement.executeUpdate() method, which exe-
cutes the UPDATE statement, would return a row count of zero. The application
could check this value and roll back the transaction when it is zero. UPDATE state-
ments that used timestamps or compared column values rows would be similar.
Using optimistic locking
Let's look at how optimistic locking can be used to prevent lost updates when one
transaction attempts to send an order to a restaurant while another transaction tries
to cancel it. Keep in mind that all this applies to read committed or less isolation only.
In the scenario shown in figure 12.1, both transactions query the PLACED_ORDER
table using a SQL SELECT statement that retrieves the order's version number. When
updating the order, they verify that the version number is unchanged.
Transaction A reads the orders and saves the version numbers, and then trans-
action B does the same. Transaction A then updates an order using an UPDATE
statement that checks that the version number is unchanged and increments the
version number. When transaction B attempts to update the order, its UPDATE
statement fails because the VERSION column has changed and PreparedState-
ment.executeUpdate() will return zero. At this point transaction B can then do
one of two things. It could roll back and start again, or it could reread the