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.