Database Reference
In-Depth Information
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.
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.
DML Locks
DML locks are used to ensure that only one person at a time modifies a row and that no one can drop a table upon
which you are working. Oracle will place these locks for you, more or less transparently, as you do work.
 
 
Search WWH ::




Custom Search