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

12.2.3 Using pessimistic locking

If you are unable to add a version column to the PLACED_ORDER table, you could
use pessimistic locking to handle concurrent updates. When using pessimistic
locking, the OrderDAO locks the rows in PLACED_ORDER when it retrieves the
orders. It does this using this SQL SELECT FOR UPDATE statement:

SELECT *
FROM PLACED_ORDER o, RESTAURANT r, PLACED_ORDER_LINE_ITEM l
WHERE
bb order.order_status = 'PLACED' AND DELIVERY_TIME < ? AND
bb r.restaurant_id = o.restaurant_id
bb and l.order_id = o.order_id
ORDER BY o.order_id ASC
FOR UPDATE OF o.ORDER_ID
The FOR UPDATE OF o.ORDER_ID clause tells Oracle to lock the rows in just the
PLACED_ORDER table, which is more efficient than locking the rows in all three of
the tables. The SELECT FOR UPDATE statement will block if the rows in the
PLACED_ORDER table are locked by another transaction. In section 12.2.5, we will
look at signaling pessimistic locking failures.
The UPDATE statement is a vanilla update statement:

UPDATE PLACED_ORDER
SET STATUS = 'SENT', MESSAGE_ID = ?, SENT_TIME = ?
WHERE ORDER_ID = ?
It simply updates the PLACED_ORDER table.

12.2.4 Using serializable or repeatable read transactions

Another alternative to pessimistic locking that also leaves the database schema
unchanged is to use serializable or repeatable read transactions. When using
these isolation levels, the OrderDAO would access the PLACED_ORDER table using
the following vanilla SQL statements:

SELECT *
FROM PLACED_ORDER o, RESTAURANT r, PLACED_ORDER_LINE_ITEM l
WHERE
bb order.order_status = 'PLACED' AND DELIVERY_TIME < ? AND
bb r.restaurant_id = o.restaurant_id
bb and l.order_id = o.order_id
ORDER BY o.order_id ASC

UPDATE PLACED_ORDER
SET STATUS = 'SENT', MESSAGE_ID = ?, SENT_TIME = ?
WHERE ORDER_ID = ?