Java Reference
In-Depth Information
How it works
The mechanism for acquiring locks is database specific and not all databases sup-
port it. In Oracle, an application uses pessimistic locking by executing a
SELECT
FOR
UPDATE
statement, which locks the rows that it selects. The rows remain locked
until the transaction either commits or rolls back. Other transactions will be
blocked if they update or delete those rows or attempt to retrieve them using a
SELECT
FOR
UPDATE
. Here is an example of an
SELECT
FOR
UPDATE
statement:
SELECT *
FROM PLACED_ORDER o, PLACED_ORDER_LINE_ITEM l
WHERE o.DELIVERY_TIME < SYSDATE
bb
AND o.STATUS = 'PLACED'
bb
AND o.ORDER_ID = l.ORDER_ID
FOR UPDATE
This
SELECT
FOR
UPDATE
statement retrieves and locks all orders whose state is
'PLACED'
and whose delivery time is before a certain time.
A transaction that executes a
SELECT
FOR
UPDATE
statement will be blocked if
another transaction has locked the rows. This will happen if the other transaction
has either updated or deleted those rows or locked them using a
SELECT
FOR
UPDATE
. The transaction will be blocked until the other transaction commits or
rolls back. If a transaction doesn't want to wait, it can use a
SELECT
FOR
UPDATE
NO
WAIT
statement, which returns with an
ORA-00054
error if it cannot lock the rows
immediately. Alternatively, it can wait for a specified period by using
SELECT
FOR
UPDATE
WAIT
<n seconds>
.
Using pessimistic locking
Let's look at how this application can use a
SELECT
FOR
UPDATE
statement to pre-
vent lost updates in the sendOrders/cancelOrder scenario. In the scenario shown
in figure 12.2, both transactions query the
PLACED_ORDER
table using a
SELECT
FOR
UPDATE
. In this scenario, transaction A executes the
SELECT
FOR
UPDATE
state-
ment first, which locks the row. The
SELECT
FOR
UPDATE
executed by transaction B
will block until transaction A commits and releases the lock. At this point, transac-
tion B will discover that the order has been sent and cannot be canceled.
A transaction can use pessimistic locking to provide some degree of read con-
sistency. Because rows that are read using
SELECT
FOR
UPDATE
are locked, they can-
not be changed or deleted by another transaction. If the transaction queries the
database again, those rows will be unchanged. However, because pessimistic lock-
ing does not prevent another transaction from inserting new rows, a query could
return additional rows.
Search WWH ::
Custom Search