Transaction-based lock escalation

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:

Single table holding the majority of the locks

Table Number of row locks Promote?

Hotels 4853 yes

Countries 3 no

Cities 12 no
Two tables holding the majority of the locks

Table Number of row locks Promote?

Hotels 2349 yes

Countries 3 no

Cities 1800 yes
Many tables holding 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

Table	Number of row locks	Promote?
table001	279	no
table002	142	no
table003	356	no
table004	79	no
table194	384	no
table195	416	no

Table	Number of row locks	Promote?
Hotels	2349	yes
Countries	3	no
Cities	1800	yes