Lock Types - Oracle Database Transactions and Locking Revealed - page 27

Chapter 3
Lock Types
In this chapter, we'll take a detailed look at how Oracle locks both data (e.g., rows in tables) and shared data structures
(such as those found in the SGA). We'll investigate the granularity to which Oracle locks data and what that means
to you, the developer. When appropriate, I'll contrast Oracle's locking scheme with other popular implementations,
mostly to dispel the myth that row-level locking adds overhead; in reality, it adds overhead only if the implementation
adds overhead. In the next chapter, we'll continue this discussion and investigate Oracle's multiversioning techniques
and how locking strategies interact with them.
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.
TX (Transaction) Locks
A TX lock is acquired when a transaction initiates its first change. The transaction is automatically initiated at this
point (you don't explicitly start a transaction in Oracle). The lock is held until the transaction performs a COMMIT or
ROLLBACK . It is used as a queuing mechanism so that other sessions can wait for the transaction to complete. Each and