Sandeep Thakur

Oracle Locking
Oracle Locking Agenda
 Oracle Locking a Brief Description
 Oracle Isolation Levels
 Setting Isolation Level
 Oracle Lock Duration
 Oracle Lock Modes
 Oracle Lock Types
 Oracle DML Lock Types/Modes
 Oracle DDL Locks Modes
 Oracle Lock Escalation
 Deadlocks
 Snapshot too old brief description
Oracle Locking a Brief Description
 Locks are mechanisms that prevent destructive
interaction between transactions accessing the same
resource.

General Object Type Affected By Locks:

 User objects, such as tables and rows (structures and

data)
 System objects not visible to users, such as shared data
structures in the memory and data dictionary rows
Oracle Isolation Levels
Isolation Levels are how Oracle executes
SQL statements in regards to read
consistency and is directly related to what
lock may be ignored.

Read Committed (Default)
Serializable Transactions 
Read-only 
Oracle Isolation Levels
Read Committed (Oracle Default)

Each query executed by a transaction sees only data that

was committed before the query (not the transaction)
began. An Oracle query will never read dirty
(uncommitted) data.  

Because Oracle does not prevent other transactions from

modifying the data read by a query, that data may be
changed by other transactions between two executions
of the query
Oracle Isolation Levels
Serializable Transactions 

See only those changes that were committed at the

time the transaction began, plus those changes
made by the transaction itself through INSERT,
UPDATE, and DELETE statements.

* Note: Not Usable in Distributed Transactions

Oracle Isolation Levels
Read-Only 

See only those changes that were

committed at the time the transaction
began and do not allow INSERT,
UPDATE, and DELETE statements. 
Setting Oracle Isolation Level
Setting at Transaction Level:

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
SET TRANSACTION ISOLATION LEVEL READ ONLY;

Setting at Session Level:

ALTER SESSION SET ISOLATION_LEVEL READ COMMITTED;

ALTER SESSION SET ISOLATION_LEVEL SERIALIZABLE;
ALTET SESSION SET ISOLATION_LEVEL READ ONLY;
Oracle Lock Duration
 All locks acquired by statements within a transaction are
held for the duration of the transaction.

 Oracle releases all locks acquired by the statements

within a transaction when an explict or implied commit
or roll back is executed. Oracle also releases locks
acquired after a savepoint when rolling back to the
savepoint.

* Note: Only transactions not waiting for the previously locked

resources can acquire locks on now available resources. Waiting
transactions continue to wait until after the original transaction
commits or completely rolls back.
Oracle Lock Modes
ExclusiveLock Mode 
Share Lock Mode 
Oracle Exclusive Lock Mode
Exclusive Lock Mode 

Prevents the associates resource from being

shared. This lock mode is obtained to
modify data. The first transaction to lock a
resource exclusively is the only
transaction that can alter the resource until
the exclusive lock is released. 
Oracle Share Lock Mode
Share Lock Mode 

Allows the associated resource to be shared,

depending on the operations involved. Multiple
users reading data can share the data, holding
share locks to prevent concurrent access by a
writer (who needs an exclusive lock). Several
transactions can acquire share locks on the same
resource.
Oracle Lock Types
DML locks (data locks)
DDL locks (dictionary locks) 
Oracle Internal Locks/Latches
Oracle Distributed Locks
Oracle Parallell Cache Management
Locks
Oracle DML Locks
DML locks (data locks) 

DML locks protect data. For example, table locks

lock entire tables, row locks lock selected rows.

DML operations can acquire data locks at two

different levels: for specific rows and for entire
tables.  
Oracle DML Lock Types
Row Level Locks
Table Level Locks
Oracle Row Locks [TX]
All DML locks Oracle acquires automatically
are row-level locks.
No limit to the number of row locks held by a
transaction.
Oracle does not escalate locks from the row
level.
Row locking provides the lowest level of
locking possible provides the best possible
transaction concurrency.
Readers of data do not wait for writers of the
same data rows.  
Oracle Row Level Locks [TX]
Continued
 A modified row is always locked exclusively so that
other users cannot modify the row until the transaction
holding the lock is committed or rolled back.
 If a transaction obtains a row lock for a row, the
transaction also acquires a table lock for the
corresponding table. The table lock prevents conflicting
DDL operations that would override data changes in a
current transaction.

* Note: A transaction gets an exclusive DML lock for each

row modified by any of the following statements:
INSERT, UPDATE, DELETE, and SELECT with the
FOR UPDATE clause.
Oracle Table Level Lock [TM]
 A transaction acquires a table lock for DML statements
such as INSERT/UPDATE/DELETE, SELECT with the
FOR UPDATE, and LOCK TABLE. Reasons are to
reserve DML access to the table on behalf of a
transaction and prevent DDL operations
 Table locks prevent the an exclusive DDL lock on the
same table which prevents DDL operations. Example, a
table cannot be altered or dropped if any uncommitted
transaction holds a table lock for it.
 A table lock can be held in several modes: row share
(RS), row exclusive (RX), share (S), share row
exclusive (SRX), and exclusive (X).
Oracle Table Level Lock [TM]
Continued
The restrictiveness of a table lock's mode
determines the modes in which other table locks
on the same table can be obtained and held.**

** Table Next Slide Explains restrictiveness of

lock modes.
Oracle Table Lock Mode Table
Lock Lock Modes Permitted?
SQL Mode RS RX S SRX X
SELECT Y Y Y Y Y
INSERT RX Y Y N N N
UPDATE RX Y* Y* N N N
DELETE RX Y* Y* N N N
SELECT.. FOR UPDATE OF.. RS Y* Y* Y* Y* N
LOCK TABLE <table_name>
IN ROW SHARE MODE RS Y Y Y Y N
IN ROW EXCLUSIVE MODE RX Y Y N N N
IN SHARE MODE S Y N Y N N
IN SHARE ROW EXCLUSIVE MODE SRX Y N N N N
IN EXCLUSIVE MODE X N N N N N

RS: row share RX: row exclusive S: share

SRX: share row exclusive X: exclusive
Oracle Table Lock Mode (RS)
Row Share Table Lock (RS)

 Indicates a transaction holding the lock on the table has locked

rows in the table and intends to update them.

 Permitted Operations: Allows other transactions to query, insert,

update, delete, or lock rows concurrently in the same table.
Therefore, other transactions can obtain simultaneous row share,
row exclusive, share, and share row exclusive table locks for the
same table.

 Prohibited Operations: Lock Table in Exclusive Mode.

Oracle Table Lock Mode (RX)
Row Exclusive Table Lock (RX)

 Indicates that a transaction holding the lock has made one or more
updates to rows in the table. A row exclusive table lock is acquired
automatically by: INSERT, UPDATE, DELETE, LOCK TABLE.. IN
ROW EXCLUSIVE MODE; A row exclusive table lock is slightly
more restrictive than a row share table lock.

 Permitted Operations: Allows other transactions to query, insert,

update, delete, or lock rows in the same table. The row exclusive
table locks allow multiple transactions to obtain simultaneous row
exclusive and row share table locks in the same table.

 Prohibited Operations: Prevents locking the table for exclusive

reading or writing. Therefore, other transactions cannot concurrently
lock the table: IN SHARE MODE, IN SHARE EXCLUSIVE MODE,
or IN EXCLUSIVE MODE.
Oracle Table Lock Mode (S)
Share Table Lock (S)

 Acquired automatically for the table specified in the following

statement: LOCK TABLE <table> IN SHARE MODE;

 Permitted Operations: Allows other transactions only to query the

table, to lock specific rows with SELECT . . . FOR UPDATE, or to
execute LOCK TABLE . . . IN SHARE MODE; no updates are
allowed by other transactions. Multiple transactions can hold share
table locks for the same table concurrently. No transaction can update
the table (with SELECT.. FOR UPDATE). Therefore, a transaction
that has a share table lock can update the table only if no other
transaction has a share table lock on the same table.

 Prohibited Operations: Prevents other transactions from modifying

the same table or lock table: IN SHARE ROW EXCLUSIVE MODE,
IN EXCLUSIVE MODE, or IN ROW EXCLUSIVE MODE.
Oracle Table Lock Mode (SRX)
Share Row Exclusive Table Lock (SRX)

 More restrictive than a share table lock. A share row exclusive table
lock is acquired for a table as follows: LOCK TABLE <table> IN
SHARE ROW EXCLUSIVE MODE;

 Permitted Operations: Only one transaction at a time can acquire a

share row exclusive table lock on a given table. A share row exclusive
table lock held by a transaction allows other transactions to query or
lock specific rows using SELECT with the FOR UPDATE clause, but
not to update the table.

 Prohibited Operations: Prevents other transactions from obtaining

row exclusive table locks and modifying the same table. A share row
exclusive table lock also prohibits other transactions from obtaining
share, share row exclusive, and exclusive table locks.
Oracle Table Lock Mode (X)
Exclusive Table Lock (X)

 Most restrictive mode of table lock, allowing the

transaction that holds the lock exclusive write access to the
table. An exclusive table lock is acquired by: LOCK
TABLE <table> IN EXCLUSIVE MODE;

 Permitted Operations: Only one transaction can obtain an

exclusive table lock for a table. An exclusive table lock
permits other transactions only to query the table.

 Prohibited Operations: Prohibits other transactions from

performing any type of DML statement or placing any
type of lock on the table.
Oracle DDL Locks
DDL locks (dictionary locks) 

 Protects the definition of an object while being used by a

DDL operation. Recall that a DDL statement implicitly
commits.
 Create Procedure will automatically acquire DDL locks for
all schema objects referenced in the procedure definition.
The DDL locks prevent objects referenced in the
procedure from being altered/dropped before the compile
is complete.
 Cannot explicitly request DDL locks. Individual schema
objects that are modified or referenced are locked during
DDL operations; the whole data dictionary is never locked.
 Three categories: exclusive DDL locks, share DDL locks,
and breakable parse locks.
Oracle DDL Lock Modes
Exclusive DDL Locks
Shared DDL Locks
Breakable Parse Locks
Oracle Exclusive DDL Lock
Most DDL operations require exclusive DDL
locks for a resource to prevent destructive
interference with other DDL operations on the
same object.
In the acquisition of an exclusive DDL lock, if
another DDL lock is already held on the object
by another operation, the lock get waits until the
other DDL lock is released before proceeding.
DDL operations also acquire DML locks (data
locks) on the schema object to be modified.
Oracle Shared DDL Lock
 Some DDL need a share DDL lock for an object to
prevent destructive interference other conflict DDL
operations, but allow data concurrency for other DDL.
For example, when a CREATE PROCEDURE executes,
the transaction acquires share DDL locks for all
referenced tables. Other transactions can concurrently
create procedures that reference the same tables and
therefore acquire concurrent share DDL locks on the
same tables, but no transaction can acquire an exclusive
DDL lock on any referenced table. No transaction can
alter or drop a referenced table. As a result, a transaction
that holds a share DDL lock is guaranteed that the
definition of the referenced schema object will remain
constant for the duration of the transaction.
Oracle Shared DDL Lock
Continued
Gotten on an object for DDL statements
that have: AUDIT, NOAUDIT,
COMMENT, CREATE [OR REPLACE]
VIEW/PROCEDURE/PACKAGE/
PACKAGE BODY/FUNCTION/
TRIGGER, CREATE SYNONYM, and
CREATE TABLE (when the CLUSTER
parameter is not included).
Oracle Breakable Parse Locks
A SQL statement (or PL/SQLprogram unit) in the
shared pool holds a parse lock for each object
referenced. Parse locks are gotten so that the
associated shared SQL area can be invalidated if a
referenced object is altered or dropped. A parse
lock does not disallow any DDL operation and can
be broken to allow conflicting DDL operations.
Gotten during the parse phase of SQL statement
execution and held as long as the shared SQL area
for that statement remains in the shared pool.
Oracle Internal Locks/Latches
Internal Locks/Latches  

Internal locks and latches protect Oracle

internal database structures such like
datafiles. Internal locks and latches are
entirely handled by Oracle internal
functions and are automatic. Some
Internal Latches can be turned by an
Oracle DBA.  
Oracle Latches
Latches are low-level serialization mechanisms to
protect shared data structures in the system global
area (SGA). Latches protect the oracle lists like
list of users currently accessing the database and
protect the data structures describing the blocks in
the buffer cache. A server or background process
acquires a latch for a very short time while
manipulating or looking at one of these structures.
The implementation of latches is operating system
dependent, particularly in regard to whether and
how long a process will wait for a latch.
Oracle Internal Locks
 Data Dictionary Locks
◦ Held on entries in dictionary caches while the entries are being
modified or used. They guarantee that statements being parsed do not
see inconsistent object definitions.
 File and Log Management Locks
◦ Protect various files like control files, redo log files so that only one
process at a time can change it. Datafiles are locked to ensure that
multiple instances mount a database in shared mode or that one
instance mounts it in exclusive mode.
 Tablespace and Rollback Segment Locks
◦ Protect tablespaces and rollback segments. Example, all instances
accessing a database must agree on if s tablespace is online or offline.
Rollback segments are locked so that only one instance can write to a
segment.
Oracle Distributed Locks
Distributed Locks 

Distributed locks ensure that the data and

other resources distributed among the
various instances consistent. Distributed
locks are held by instances rather than
transactions.  
Oracle Parallel Cache
Management Locks
Parallel Cache Management (PCM) Locks 

Parallel cache management locks are

distributed locks that cover one or more
data blocks (table or index blocks) in the
buffer cache. PCM locks do not lock any
rows on behalf of transactions.  
Data Lock Escalation
 A transaction holds exclusive row locks for all rows
inserted, updated, or deleted within the transaction.
Because row locks are acquired at the highest degree of
restrictiveness, no lock conversion is required or
performed.

 Oracle automatically converts a table lock of lower

restrictiveness to one of higher restrictiveness as
appropriate. For example, assume that a transaction uses a
SELECT statement with the FOR UPDATE clause to lock
rows of a table. As a result, it acquires the exclusive row
locks and a row share table lock for the table. If the
transaction later updates one or more of the locked rows,
the row share table lock is automatically converted to a
row exclusive table lock.
Oracle Lock Escalation
Continued
 Occurs when many locks are held at one level like rows,
and the database raises the locks to a higher level like a
table lock. If many row locks on a table, the database will
automatically escalate the row locks to a single table lock
for the transaction. The physical number of locks is
reduced, but the restrictiveness is increased. **
 Lock escalation increases the possiblity of deadlocks.
Imagine the situation where the system is trying to escalate
locks on behalf of transaction T1 but cannot because of the
locks held by transaction T2. A deadlock is created if
transaction T2 also requires escalation of the same data
before it can proceed.
** Note: Oracle dose not do Lock Escalation to Exclusive
Table Level for numerous row locks.
Deadlocks
 Oracle automatically detects deadlock situations and
resolves them by rolling back one of the statements
involved in the deadlock. This releases one set of the
conflicting row locks. A corresponding message also is
returned to the transaction that undergoes the rollback.
 Deadlocks often occur when transactions override Oracle
default locking. Oracle itself does no lock escalation and
does not use read locks for queries and does not use
page-level locking, deadlocks rarely occur in Oracle.
 Deadlocks can usually be avoided if transactions accessing
the same tables lock those tables in the same order, either
through implicit or explicit locks and when a sequence of
locks for one transaction are required, you should consider
acquiring the most exclusive (least compatible) lock first
 Always close explicit cursors when finished to free locks.
Snapshot Too Old
Snapshot too old occurs when:
◦ (a) transaction reads a table (a)
◦ Another transaction (b) updates a row in table (a)
◦ The update transaction (b) commits, the commit
releases the rollback segment for another transaction
to use.
◦ Another transaction (c) uses that same rollback
segment and from update transaction (b) and
overwrites the rollback information from the update
transaction (b)
Snapshot Too Old
◦ The transaction (a) tries to read the row
transaction (b) updated and to maintain read
consistency Oracle has to goto rollback for the
previous view of the record. Since transaction
(c) has over wrote the rollback from
transaction (b) transaction (a) can not get a
read consistent view of the row which causes
the snapshot too old error to be returned.
Snapshot Too Old
Things that can affect occurance of snapshot too
old.
◦ Rollback Segment Max Size
◦ Rollback Segment Optimal Size
◦ Number of Rollback Segments
◦ Proper application transaction activity
Rollback Segment Max Size
The maximum size a rollback segment
can grow too can have an impact on how
long a particular change can be held in a
rollback segment before being over wrote
causing the snapshot too old error.
Rollback Segment Optimal Size
The rollback segment can have what is called
and optimal size. The optimal size is what a
rollback segment will shrink back to after the
space beyond optimal has not been used for a
period of time. This affects when a large
transaction does a DML operation and the
rollback segment must grow beyond optimal
and then commits. The rollback information
contained in the space beyond optimal can not
be accessed if the rollback segment shrinks back
to optimal.
Number of Rollback Segments
The total number of rollback segments in
the database can affect the snapshot too
old by making it less likely that a rollback
segment would be used by another
transaction.
Note: Additional rollback segments dose
not ensure that more rollback segments
are used in lower transaction activity
environments
Proper Application Transaction
Activity
98% of all snapshot too old errors are due
to conflicting transactional activity,
mostly in batch processing jobs.
Keep conflicting update and select jobs
from running at the same time. This has
to do with the design of application jobs
and process flow of jobs in batch. Which
jobs run concurrently and in what order
jobs run.
Proper Application Transaction
Activity
Keep Updates under the Rollback segment
optimal if you can not consider speaking with a
DBA about rollbacks with high optimal size.
Use special rollback segments for large DML
jobs.
Commit in DML jobs as little as possible. This
let rollbacks larger, but will keep undo
information in rollback segments until commit
and can have a larger optimal size.
Proper Application Transaction
Activity
Keep transactions as fast as possible. The
faster activity completes will reduce the
chances of updates that may cause
snapshot too old.
CREDITS
Oracle Metalink White Paper 70120
Oracle 8.1.7 Documentation
My own knowledge and experience using
Oracle
?? QUESTIONS ??