How the system makes its decision if it has a choice

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.