Management and Escalation
Lock escalation occurs when
numerous locks are held at one level of granularity, such as rows,
and a database raises the locks to a higher level of granularity,
like table. For example, if a single user locks many rows in a
table, some databases automatically escalate the user's row locks to
a single table. The number of locks is reduced, but the
restrictiveness of what is being locked is increased.
Unlike other database products such as IBM DB2 and Microsoft SQL
Server, Oracle never escalates locks. Lock escalation greatly
increases the likelihood 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 lock escalation of the
same data before it can proceed.
Now some tips will be provided on how to manage locking issues
with clustered Oracle RAC environments.
Lock Management for Oracle RAC
Oracle Real Application Cluster (RAC) environments introduce a
special case of how locking activity is managed quite differently
than single instance Oracle environments. Since Oracle RAC
involves multiple instances that share a common database on shared
storage, the gv$ dynamic performance views can be used to monitor
the status for locking activities and to resolve lock conflicts
within a RAC environment.
Locking Mechanism in Oracle RAC
Database resources within Oracle, such as tables and rows,
are represented by enqueues. Enqueues are a special type of lock
within Oracle that places a request for a resource into a queue.
These can be locked in various modes such as shared or exclusive
lock modes. Concurrent locking requests may enter a conflict based
upon lock compatibility rules. Enqueue lock resources are accessed
externally by querying the
dynamic performance view. Lock requests can be viewed by query of
the gv$lock dynamic performance view. Next, how locking functions
with Oracle RAC environments will be illustrated by a review of
common lock conflict types and example.
Locking Conflict Types
Within Oracle environments, lock conflicts are composed of the
following two basic types:
- Local locking conflicts (block level)
- Global lock conflicts (block level)
Local lock conflicts are limited to conflicting sessions
connected to the same instance which may also apply to a single
instance within RAC environments. In the v$lock dynamic performance
view, the column for BLOCK contains the value of 1 for blocking lock
Global locking conflict (block) occurs when conflicting sessions
are connected to different instances for RAC environments only. With
RAC environments for Oracle, the v$lock column BLOCK will
contain a value of 2 to mark potential conflicts. The value will
always appear as 2 for RAC environments unless there is a local
The following script displays all sessions that are holding or
requesting locking of resources for a particular session. Waiting
sessions have a non-zero value for the column GV$LOCK.REQUEST.
Resources are identified by the TYPE, ID1, and ID2 columns in the
dynamic performance view for gv$lock.
How to Locate the Root Blocker with Lock Problems
The following query provides the code sample along with the query
to execute to determine the root blockers causing the lock problems.
The solution is to first locate and kill the root blockers. Usually
the row with the highest CTIME value, e.g. row L1, will be the
starting point for determine the root blocker.
Make sure that there is not another row with the same SID as that
found in the L1 column, then kill the root blocker value in L1
column. Another option is to kill the oldest blocking session, which
should have the highest CTIME value.
Frustrated by the Hidden or
Undocumented Features of Oracle?
The landmark book
Oracle Internals: Tips and Tricks for the Oracle DBA"
will take you far beyond the standard features of the Oracle
database into the hidden and undocumented realms formerly
reserved for Oracle insiders. You will discover valuable
tips and tricks for mastering your Oracle database, and you can
buy it for
30% off directly from the publisher.
Burleson is the American Team
documentation was created as a support and Oracle training reference for use by our
DBA performance tuning consulting professionals.
Feel free to ask questions on our
considering using the services of an Oracle support expert should
independently investigate their credentials and experience, and not rely on
advertisements and self-proclaimed expertise. All legitimate Oracle experts
Oracle technology is changing and we
strive to update our BC Oracle support information. If you find an error
or have a suggestion for improving our content, we would appreciate your
and include the URL for the page.
Copyright © 1996 - 2017
All rights reserved by
is the registered trademark of Oracle Corporation.
Remote Emergency Support provided by