I’ve written here regarding Oracle locks but apparently the post didn’t explain enough about the views involved. Let’s fix that.
Two views are probably the most beneficial when investigating locks:
V$LOCK
V$LOCKED_OBJECT
with V$LOCK providing an overall view of the active locks in a database and V$LOCKED_OBJECT providing detail on who has TM (DML) locks against which database objects.
Looking at V$LOCK to see what information is available we see that this view provides:
the lock address (KADDR)
the SID of the session holding the lock (SID)
the type of lock (TYPE) (including two additional columns [ID1,ID2] which
can further identify the lock)
the lock mode (LMODE)
the requested mode (REQUEST)
the time since the current mode was granted (CTIME)
whether or not the lock is blocking another session (BLOCK)
The TYPE column describes whether this is a user or a system lock. The three user-type locks are
TM — DML enqueue [INSERT/UPDATE/DELETE transactions]
TX — Transaction enqueue [possibly DDL locks on an object]
UL — User supplied/defined [created by the DBMS_LOCK package]
System locks are usually held for a short duration, although there are exceptions, such as the MR and AE lock types. These lock types are
AE Application Edition locks (11g Edition Based Redefinition****)
BL Buffer hash table instance
CF Control file schema global enqueue
CI Cross-instance function invocation instance (RAC, OPS, single-instance PQ*)
CU Cursor bind*****
DF datafile instance
DL Direct loader parallel index create
DM Mount/startup db primary/secondary instance
DR Distributed recovery process
DX Distributed transaction** entry
FS File set
HW Space management operations on a specific segment
IN Instance number
IR Instance recovery serialization global enqueue
IS Instance state
IV Library cache invalidation instance
JQ Job queue
KK Thread kick
LA .. LP Library cache lock instance lock (A..P = namespace)
MM Mount definition global enqueue
MR Media recovery
NA..NZ Library cache pin instance (A..Z = namespace)
PF Password File
PI, PS Parallel operation
PR Process startup*******
QA..QZ Row cache instance (A..Z = cache)
RT Redo thread global enqueue
SC System change number instance
SM SMON
SN Sequence number instance
SQ Sequence number enqueue
SS Sort segment
ST Space transaction enqueue
SV Sequence number value
TA Generic enqueue
TS New block allocation enqueue (ID2=1)
TS Temporary segment enqueue (ID2=0)
TT Temporary table enqueue
UN User name
US Undo segment DDL***
WL Being-written redo log enqueue******
[The SM lock for SMON is acquired when the process ‘wakes up’ to check for work it has to do; the lock is taken as exclusive then converted to shared when the check is complete. This happens every time SMON becomes active, which is usually every five minutes. The lock is only active for a short period of time (one, maybe two seconds on average) and usually is not one worthy of concern.]
* The CI enqueue is used not only in RAC and OPS configurations but also by the parallel query coordinator. As an example before a direct read operation the reader process uses a CI call to DBWR to request a checkpoint of all dirty blocks in the cache belonging to the segment about to be read.
** The DX lock is set when single-master or multi-master replication is configured and active or when transactions span a database link, such as inserts and updates to a local table using data from a remote site. The DR lock is set when recovering from failed transactions involving two-phase commits (usually found in environments where replication as described at the beginning of this paragraph is used).
*** The US lock is set to serialize DDL (or processes(+) that invoke DDL) against UNDO segments, specifically:
CREATE ROLLBACK SEGMENT
DROP ROLLBACK SEGMENT
ALTER ROLLBACK SEGMENT ONLINE
ALTER ROLLBACK OFFLINE
ALTER ROLLBACK SEGMENT SHRINK
ALTER ROLLBACK SEGMENT STORAGE
Offlining PENDING OFFLINE RBS by SMON
SMON – abortive offline cleanup
(+)STARTUP
There is nothing to ‘fix’ with this lock as Oracle is managing the UNDO segments and preventing any access to them until any DDL or process in the above list has completed.
**** The AE lock is created for each user session connected to the database provided edition-based redefinition is enabled/installed. Each user session has one AE lock in shared mode, waiting to be pressed into service using the “ALTER SESSION SET EDITION=<edition_name>;” statement. This presumes the default database edition has been set by the DBA using “ALTER DATABASE DEFAULT EDITION = <edition_name>;”. Editions allow developers to change database object definitions in a private environment while the application is still in use, reserving the changes until the edition is put into service at the database level. More information can be found here.
***** The CU lock protects the bind variable definitions during cursor optimization. These locks tend to be used when cursor_sharing is set to a value other than EXACT (but the behaviour is version-dependent). In 8i they are allocated one lock per bind, and in 9i and later releases it appears they are allocated one lock per cursor. These locks should not be a performance problem as they are not held for long periods of time (the bind and optimization steps usually are fast operations, even in systems experiencing high levels of traffic).
****** The WL lock protects the current redo log from concurrent writes since redo logs are written sequentially. This lock may be seen frequently but it should not be a long-standing lock nor should it impede performance. Of course the size of your redo logs and the frequency in which they switch can impact performance especially when running in NOARCHIVELOG mode which may make the WL locks more visible.
******* The PR lock protects memory structures while a process starts and may not be visible in V$LOCK unless an issue arises which prevents a process from successfully starting. PR locks held for a considerable amount of time will need to be investigated as to which process is holding the lock and what is preventing the process from completing its startup. The alert log is the best place to begin such an investigation.
The ID1 and ID2 columns provide varying information from the OBJECT_ID for the object involved (for TM locks — ID1) to additional information differentiating multiple levels of a given lock type (as in the TS lock listed above). Since the ID1 column can contain information other than the id of the locked object it’s better to use the V$LOCKED_OBJECT view to investigate who has which database objects locked.
In the case of UL locks ID1 reports the lock handle used to generate and manage the lock through the DBMS_LOCK package:
SQL> select * From v$lock;
ADDR KADDR SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK
-------- -------- ---------- -- ---------- ---------- ---------- ---------- ---------- ----------
2EC34228 2EC34254 160 XR 4 0 1 0 17231 0
2EC3429C 2EC342C8 160 CF 0 0 2 0 17228 0
2EC34384 2EC343B0 160 RS 25 1 2 0 17223 0
2EC343F8 2EC34424 162 PW 1 0 3 0 17219 0
2EC3446C 2EC34498 161 RT 1 0 6 0 17223 0
2EC344E0 2EC3450C 170 AE 99 0 4 0 17120 0
2EC34554 2EC34580 128 AE 99 0 4 0 131 0
2EC345C8 2EC345F4 146 AE 99 0 4 0 17209 0
2EC3463C 2EC34668 162 MR 1 0 4 0 17223 0
2EC346B0 2EC346DC 162 MR 2 0 4 0 17223 0
2EC34724 2EC34750 162 MR 3 0 4 0 17223 0
ADDR KADDR SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK
-------- -------- ---------- -- ---------- ---------- ---------- ---------- ---------- ----------
2EC34798 2EC347C4 162 MR 4 0 4 0 17223 0
2EC3480C 2EC34838 162 MR 5 0 4 0 17223 0
2EC34880 2EC348AC 162 MR 201 0 4 0 17223 0
2EC348F4 2EC34920 155 AE 99 0 4 0 17120 0
2EC34968 2EC34994 159 TS 3 1 3 0 17218 0
2EC349DC 2EC34A08 157 AE 99 0 4 0 17209 0
2EC34A50 2EC34A7C 128 UL 1073742010 0 6 0 128 0
2EC34AC4 2EC34AF0 139 AE 99 0 4 0 17120 0
2EC34B38 2EC34B64 136 AE 99 0 4 0 17113 0
2EC34BAC 2EC34BD8 140 AE 99 0 4 0 16985 0
2EC34C20 2EC34C4C 137 AE 99 0 4 0 16978 0
ADDR KADDR SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK
-------- -------- ---------- -- ---------- ---------- ---------- ---------- ---------- ----------
2EC34C94 2EC34CC0 129 AE 99 0 4 0 106 0
23 rows selected.
SQL>
In the case of RS (reclaimable space) and TS (temporary space) locks ID2 provides the lock level; there may be other lock types that populate the ID2 column with non-zero values.
As noted in the list the CTIME column lists the time since the current mode was granted; as expected if the lock mode changes then the CTIME counter resets and starts counting from that point forward until the next mode change.
V$LOCKED_OBJECT provides the following for all DML locks (type TM) in the database:
the undo segment number (XIDUSN)
the slot number (XIDSLOT)
the sequence number (XIDSQN)
the object identifier (OBJECT_ID)
the session identifier (SESSION_ID)
the username (ORACLE_USERNAME)
the O/S user (OS_USER_NAME)
the O/S process id (PROCESS)
the mode (LOCKED_MODE)
The slot and sequence number apply to the undo segment assigned to each lock and the locked mode is a number spanning the range from 0 through 6, identified as follows:
0 — no lock held
1 — NULL
2 — Row share lock (SS)
3 — Row exclusive (SX)
4 — Shared (S)
5 — S/Row-exclusive (SSX)
6 — Exclusive (X)
The above values apply to both the V$LOCK and V$LOCKED_OBJECT views.
How, then, can these views be used to report who has which objects locked and in what modes? The following query can be run:
set linesize 200 pagesize 60 trimspool on
column username format a10
column osuser format a10
column sid format 99999
column object format a35
column type format a35
select s.username, s.sid, s.serial#,
s.osuser, k.ctime, o.object_name object, k.kaddr,
case l.locked_mode when 1 then 'No Lock'
when 2 then 'Row Share'
when 3 then 'Row Exclusive'
when 4 then 'Shared Table'
when 5 then 'Shared Row Exclusive'
when 6 then 'Exclusive'
end locked_mode,
case
when k.type = 'AE' then 'Application Edition'
when k.type = 'BL' then 'Buffer Cache Management (PCM lock)'
when k.type = 'CF' then 'Controlfile Transaction'
when k.type = 'CI' then 'Cross Instance Call'
when k.type = 'CU' then 'Bind Enqueue'
when k.type = 'DF' then 'Data File'
when k.type = 'DL' then 'Direct Loader'
when k.type = 'DM' then 'Database Mount'
when k.type = 'DR' then 'Distributed Recovery'
when k.type = 'DX' then 'Distributed Transaction'
when k.type = 'FS' then 'File Set'
when k.type = 'IN' then 'Instance Number'
when k.type = 'IR' then 'Instance Recovery'
when k.type = 'IS' then 'Instance State'
when k.type = 'IV' then 'Library Cache Invalidation'
when k.type = 'JQ' then 'Job Queue'
when k.type = 'KK' then 'Redo Log Kick'
when k.type like 'L%' then 'Library Cache Lock'
when k.type = 'MM' then 'Mount Definition'
when k.type = 'MR' then 'Media Recovery'
when k.type like 'N%' then 'Library Cache Pin'
when k.type = 'PF' then 'Password File'
when k.type = 'PI' then 'Parallel Slaves'
when k.type = 'PR' then 'Process Startup'
when k.type = 'PS' then 'Parallel slave Synchronization'
when k.type like 'Q%' then 'Row Cache Lock'
when k.type = 'RT' then 'Redo Thread'
when k.type = 'SC' then 'System Commit number'
when k.type = 'SM' then 'SMON synchronization'
when k.type = 'SN' then 'Sequence Number'
when k.type = 'SQ' then 'Sequence Enqueue'
when k.type = 'SR' then 'Synchronous Replication'
when k.type = 'SS' then 'Sort Segment'
when k.type = 'ST' then 'Space Management Transaction'
when k.type = 'SV' then 'Sequence Number Value'
when k.type = 'TA' then 'Transaction Recovery'
when k.type = 'TM' then 'DML Enqueue'
when k.type = 'TS' then 'Table Space (or Temporary Segment)'
when k.type = 'TT' then 'Temporary Table'
when k.type = 'TX' then 'Transaction'
when k.type = 'UL' then 'User-defined Locks'
when k.type = 'UN' then 'User Name'
when k.type = 'US' then 'Undo segment Serialization'
when k.type = 'WL' then 'Writing redo Log'
when k.type = 'XA' then 'Instance Attribute Lock'
when k.type = 'XI' then 'Instance Registration Lock'
end type
from v$session s, sys.v_$_lock c, sys.v_$locked_object l, dba_objects o, sys.v_$lock k
where o.object_id = l.object_id
and l.session_id = s.sid
and k.sid = s.sid
and s.saddr = c.saddr
and k.kaddr = c.kaddr
and k.lmode = l.locked_mode
and k.lmode = c.lmode
and k.request = c.request
order by object;
This query will run on 9iR2 and later releases — earlier versions of Oracle require DECODE statements and I’ll leave it to you to make any changes for Oracle 8.1.7 and earlier releases.
Sessions holding locks blocking other sessions can be reported as well:
with blocked as (
select sid blocked, serial#, username, blocking_session
from v$session
where blocking_session is not null
),
blocking as (
select sid blocking, serial# bl_serial#, username bl_username
from v$session
),
obj_info as (
select l.session_id, o.object_name, l.object_id,
decode(l.locked_mode, 1, 'No Lock',
2, 'Row Share',
3, 'Row Exclusive',
4, 'Shared Table',
5, 'Shared Row Exclusive',
6, 'Exclusive') locked_mode
from v$locked_object l, dba_objects o
where o.object_id = l.object_id
)
select blocked, serial#, username, blocking, bl_serial#, bl_username, session_id, object_name, object_id, locked_mode
from blocked, blocking, obj_info
where blocking = blocking_session
and session_id = blocking_session;
Again, this runs on 9iR2 and later releases of Oracle which provide the subquery factoring syntax (the WITH clause).
Oracle provides another script to output a ‘tree’ structure of the locks held in a database, found in $ORACLE_HOME/rdbms/admin, named utllockt.sql. This particular script reports the locks which are blocking other sessions. It uses the CONNECT BY syntax to produce a tree-like output; an example is shown in the internal documentation in the script.
So what to do should you find a blocking lock? That depends on the type of application you’re running. It may be as simple as contacting the user with the blocking lock and asking him or her to commit or rollback the changes he or she has made. Or, if they’ve done the unthinkable, assuming the session was hung, and clicked on that little red x to kill the application, you may need to simply kill the session which remains. There is no ‘cut and dried’, ‘one-size-fits-all’ solution.
So, locks aren’t the mystery some might think, and investigating them (with these and other scripts/queries available on the web) isn’t a thankless, convoluted task. Investigation does take some patience, however, as it may require the output from several scripts to determine where the problems lie. I’ve provided some starting points and I suggest you ‘google’ for additional scripts other DBAs find useful. Hopefully I’ve provided enough information to point you in the right direction so you won’t be like the Scarecrow in L. Frank Baum’s “The Wizard Of Oz” and not know which way is up. Or down.
Now, go investigate those locks.
Oracle Tips and Tricks -- David Fitzjarrell 