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

Transaction A-Send Orders Transaction B-Cancel Order

SELECT ...
FROM PLACED_ORDER
WHERE …
FOR UPDATE

SELECT …
FROM PLACED_ORDER
WHERE …
FOR UPDATE

UPDATE PLACED_ORDER
SET STATUS='SENT'
WHERE …

COMMIT

Figure 12.2
An example of how pessimistic
locking prevents concurrent updates

Time

Benefits and drawbacks
Pessimistic locking has several advantages:

Unlike optimistic locking, pessimistic locking does not require any schema
changes.

■

It prevents a transaction from overwriting another transaction's changes. By
locking rows when they are read, a transaction can ensure that when it
updates them later it will not overwrite another transaction's changes.

■

It can be used to maintain read consistency in scenarios where a transaction
reads from one table but updates another. A transaction can use SELECT FOR
UPDATE to ensure that rows that it reads but does not update are unchanged
when it commits.

■

It reduces the probability of deadlocks in databases that implement fully iso-
lated transactions by locking rows when they read.

■

But again, there are some drawbacks and issues as well:

All potentially conflicting transactions have to use SELECT FOR UPDATE in
order for pessimistic locking to work, which is potentially error-prone. For
example, in the sendOrders/cancelOrder scenario, if transaction B used a
regular SELECT statement it would not block and would end up overwriting
transaction A's changes.

■

In databases such as Oracle where SELECT does not normally lock rows, the
increased use of locks reduces concurrency and the overhead of maintaining

■