If the lock granularity (whether to lock rows or entire tables) is not
forced by the user, the system makes a decision using the following rules:
- For SELECT statements running in READ_COMMITTED isolation, the system
always chooses row-level locking.
- If the statement scans the entire table or index and it does not meet
the criteria above, the system chooses table-level locking. (A statement scans
the entire table whenever it chooses a table as the access path.)
- If a statement partially scans the index, the system uses row-level locking,
until the number of rows touched on a table reaches lock escalation threshold.
It is then escalated to a table lock. (You can configure this threshold number;
see Lock escalation threshold.)
- For SELECT, UPDATE, and DELETE statements, the number of rows touched
is different from the number of rows read. If the same row is read more than
once, it is considered to have been touched only once. Each row in the inner
table of a join can be read many times, but can be touched at most one time.