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

many locks can reduce performance. The increased use of locks also
enhances the probability of deadlocks, which occur when two transactions
are waiting for locks held by the other. Oracle automatically detects dead-
locks and returns an ORA-00060 error to one of the participating transac-
tions, which can either roll back the entire transaction or retry the SQL
statement that caused the deadlock. Other databases will signal a deadlock in
a similar way.

Some databases have limitations on how SELECT FOR UPDATE can be used. For
example, with Oracle, it can only be used at the top level and cannot be
nested within another SQL statement. Also, there are certain SQL features
that cannot be used in conjunction with SELECT FOR UPDATE . These features
include DISTINCT , aggregate functions, and GROUP BY . It cannot be used on
certain types of views and nested SELECT s. This is a particularly important
limitation when an application uses a persistence framework since it has no
control over the generated SQL .

■

An application that accesses the database using a persistence framework can
only use pessimistic locking if the persistence framework supports it. An
application cannot implement pessimistic locking on top of a persistence
framework.

■

An application that uses pessimistic locking cannot use a process-level cache
because it must access the database in order to lock the rows.

■

Despite these limitations, pessimistic locking is extremely useful in many situations.

When to use it
Pessimistic locking should be used when:

The database schema does not support optimistic locking because, for
example, the tables do not have a version or timestamp column or contain
values such as floats or blobs that cannot be compared.

■

The application requires some degree of read consistency.

■

You don't want to incur the overhead of serializable transactions.

■

12.1.4 Using a combination of locking mechanisms

The simplest approach is to use a single concurrency strategy throughout the
application, but sometimes you might need to use a combination of concurrency
strategies. You could, for example, use optimistic locking for all transactions
except those with special requirements. Transactions that access tables that do not
support optimistic locking can use pessimistic locking, and transactions that need