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
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
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.
In the following table, two database tables hold the majority of the locks.
In the following table, many database tables hold a small number of locks.