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.
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:
To satisfy the isolation requirements,