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 areTM -- 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 OMS/emagent process locks (11g)
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 instance
[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 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>
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 999
column object 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 = '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, v$_lock v
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.kaddr = v.kaddr
and v.saddr = s.saddr
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.
0 comments:
Post a Comment