Locking and Issues - Oracle Database Transactions and Locking Revealed - page 26

Oracle never escalates locks, but it does practice lock conversion or lock promotion, terms that are often
confused with lock escalation.

■ the terms lock conversion and lock promotion are synonymous. oracle typically refers to the process as
lock conversion .

Note

Oracle will take a lock at the lowest level possible (i.e., the least restrictive lock possible) and convert that lock to a
more restrictive level if necessary. For example, if you select a row from a table with the FOR UPDATE clause, two locks
will be created. One lock is placed on the row(s) you selected (and this will be an exclusive lock; no one else can lock
that specific row in exclusive mode). The other lock, a ROW SHARE TABLE lock, is placed on the table itself. This will
prevent other sessions from placing an exclusive lock on the table and thus prevent them from altering the structure of
the table, for example. Another session can modify any other row in this table without conflict. As many commands as
possible that could execute successfully given there is a locked row in the table will be permitted.
Lock escalation is not a database “feature.” It is not a desired attribute. The fact that a database supports lock
escalation implies there is some inherent overhead in its locking mechanism and significant work is performed to
manage hundreds of locks. In Oracle, the overhead to have 1 lock or 1 million locks is the same: none.

Summary
This chapter covered a lot of material that, at times, may have made you scratch your head. While locking is rather
straightforward, some of its side effects are not. However, it is vital that you understand these issues. For example,
if you were not aware of the table lock Oracle uses to enforce a foreign key relationship when the foreign key is not
indexed, then your application would suffer from poor performance. If you did not understand how to review the
data dictionary to see who was locking whom, you might never figure that one out. You would just assume that the
database hangs sometimes. I sometimes wish I had a dollar for every time I was able to solve the insolvable hanging
issue by simply running the query to detect unindexed foreign keys and suggesting that we index the one causing the
problem. I would be very rich.