The optimizer makes its decisions for the scope of a single statement at compile time; the runtime overrides are also for the scope of a single statement. As you know, a transaction can span several statements. For connections running in TRANSACTION_SERIALIZABLE isolation and for connections that are doing a lot of inserts or updates, a transaction can accumulate a number of row locks even though no single statement would touch enough rows to make the optimizer choose table-level locking for any single table.
However, during a transaction, the Derby system tracks the number of locks for all tables in the transaction, and when this number exceeds a threshold number (which you can configure; see Lock escalation threshold), the system attempts to escalate locking for at least one of the tables involved from row-level to table-level locking.
The system attempts to escalate to table-level locking for each table that has a burdensome number of locks by trying to obtain the relevant table lock. If the system can lock the table without waiting, the system locks the entire table and releases all row locks for the table. If the system cannot lock the table without waiting, the system leaves the row locks intact.
After a table is locked in either mode, a transaction does not acquire any subsequent row-level locks on a table. For example, if you have a table called Hotels that contained several thousand rows and a transaction locks the entire table in share mode in order to read data, it might later need to lock a particular row in exclusive mode in order to update the row. However, the previous table-level lock on Hotels forces the exclusive lock to be table-level as well.
This transaction-based runtime decision is independent of any compilation decision.
If when the escalation threshold was exceeded the system did not obtain any table locks because it would have had to wait, the next lock escalation attempt is delayed until the number of held locks has increased by some significant amount, for example from 5000 to 6000.
Here are some examples, assuming the escalation threshold is 5000.
In the following table, a single database table holds the majority of the locks.
| Table | Number of Row Locks | Promote? |
|---|---|---|
| Hotels | 4853 | Yes |
| Countries | 3 | No |
| Cities | 12 | No |
In the following table, two database tables hold the majority of the locks.
| Table | Number of Row Locks | Promote? |
|---|---|---|
| Hotels | 2349 | Yes |
| Countries | 3 | No |
| Cities | 1800 | Yes |
In the following table, many database tables hold a small number of locks.
| Table | Number of Row Locks | Promote? |
|---|---|---|
| table001 | 279 | No |
| table002 | 142 | No |
| table003 | 356 | No |
| table004 | 79 | No |
| table194 | 384 | No |
| table195 | 416 | No |