When a system is configured for row-level locking, it decides whether to
use table-level locking or row-level locking for each table in each DML statement.
The system bases this decision on the number of rows read or written for each
table, and on whether a full conglomerate scan is done for each table.
Note: When you have turned off row-level locking for your system, Derby always
uses table-level locking.
The first goal of the system's decision is concurrency; wherever possible,
the system chooses row-level locking. However, row-level locking uses a lot
of resources and might have a negative impact on performance. Sometimes row-level
locking does not provide much more concurrency than table-level locking. In
those situations, the system might choose to escalate the locking scheme from
row-level locking to table-level locking to improve performance. For example,
if a connection is configured for TRANSACTION_SERIALIZABLE isolation, the
system chooses table-level locking for the following statement:
SELECT *
FROM FlightAvailability AS fa, Flights AS fts
WHERE fts.flight_id = fa.flight_id
AND fts.segment_number = fa.segment_number
To satisfy the isolation requirements,
Derby would have to lock all
the rows in both the
FlightAvailability and the
Flights tables. Locking both the tables would be cheaper,
would provide the same isolation, and would allow the same concurrency.
Note: You can force lock escalation for specific tables when you alter
them with the LOCKSIZE clause. For these tables, Derby always chooses
table-level locking. For more information, see the Derby Reference Manual.
