When lock escalation occurs, the system is decreasing the granularity of your locks. An example would be the database system turning your 100 row-level locks against a table into a single table-level lock. You are now using one lock to lock everything, and, typically, you are also locking a whole lot more data than you were before. Lock escalation is used frequently in databases that consider a lock to be a scarce resource and overhead to be avoided.
Note Oracle will never escalate a lock. Never.
Oracle never escalates locks, but it does practice lock conversion or lock promotion, terms that are often confused with lock escalation.
Note The terms lock conversion and lock promotion are synonymous. Oracle typically refers to the process as lock conversion.
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 one lock or one million locks is the same: none.
Lock Types
The three general classes of locks in Oracle are as follows:
•\ DML locks: DML stands for Data Manipulation Language. In general, this means SELECT, INSERT, UPDATE, MERGE, and DELETE statements. DML locks are the mechanism that allows for concurrent data modifications. DML locks will be, for example, locks on a specific row of data or a lock at the table level that locks every row in the table.
•\ DDL locks: DDL stands for Data Definition Language (CREATE and ALTER statements, and so on). DDL locks protect the definition of the structure of objects.
•\ Internal locks and latches: Oracle uses these locks to protect its internal data structures. For example, when Oracle parses a query and generates an optimized query plan, it will latch the library cache to put that plan in there for other sessions to use. A latch is a lightweight, low-level serialization device employed by Oracle, similar in function to a lock. Do not confuse or be misled by the term lightweight; latches are a common cause of contention in the database, as you will see. They are lightweight in their implementation, but not their effect.
We will now take a more detailed look at the specific types of locks within each of these general classes and the implications of their use. There are more lock types than I can cover here. The ones I cover in the sections that follow are the most common and are held for a long duration. The other types of locks are generally held for very short periods of time.
Leave a Reply