Subject: Detecting and Resolving Locking Conflicts
Modified: 27 Jun 95 03:56:41
Author:LTO
------------------------------------------------------------------------------
DETECTING AND RESOLVING LOCKING CONFLICTS
With the growing complexity of applications, it has become a challenge
for the dba and Oracle Support to resolve and properly diagnose locking
(hanging) issues. The focus of this paper is to give some concrete steps and
examples in resolving the most common locking issues. We will focus on
a combination of three utilities: SQL, MONITORING FACILITY, and LOCKING
SCRIPTS provided by oracle. We will also give a methodology of things to
check when faced with a difficult "hanging" scenerio.
USE OF MONITOR SCREENS:
=======================
MONITOR SESSION:
---------------
Session Serial Process Lock
ID Number ID Status Username Waited Current Statement
===============================================================================
6 35 28 ACTIVE LTO2 C2D2B3B4 UPDATE
8 70 19 INACTIVE SYSTEM SELECT
12 15 25 INACTIVE LTO INSERT
14 17 27 ACTIVE LTO3 C2D2B438 DELETE
15 30 26 ACTIVE SYS UNKNOWN
Name Description
Session Id SID - Session Identifier
Serial Nr Session serial number. Used to uniquely identify a
session's objects. Guarantees that session-level commands are
applied to the correct session objects in the event that the
session ends and another session begins with the same session
ID
Process Id PID in v$process. Oracle Process Identifier.
Status Status of the session.
Username User name.
Lock waited Address of lock waiting for ; null if none.
The most important column would be LOCK WAITED. If it is NOT NULL, then
this user is waiting for a resource. In this example, LTO2 and LTO3 are
waiting on locks.
MONITOR LOCK:
------------
Session Serial Lock Resource Resource Mode Mode
Username ID Number Type ID 1 ID 2 Held Requested
===============================================================================
LTO2 6 35 TM 2294 0 RX NONE
LTO2 6 35 TM 2295 0 RX NONE
LTO2 6 35 TX 262167 87 NONE X
LTO2 6 35 TX 327682 90 X NONE
LTO 12 15 TM 2294 0 RX NONE
LTO 12 15 TM 2295 0 RX NONE
LTO 12 15 TX 262167 87 X NONE
LTO3 14 17 TM 2294 0 RX NONE
LTO3 14 17 TM 2295 0 RX NONE
LTO3 14 17 TX 262167 87 NONE X
LTO3 14 17 TX 196636 87 X NONE
Username User name.
Session Id SID - Session Identifier.
Serial Nr Same as above.
Type of Lock LOCK ID1 LOCK ID2
TX(Transaction) Decimal representation of Decimal representation of
rollback segment number "wrap" number (number
and slot number times the rollback
slot has been reused)
TM(Table Locks) Object id of table being Always 0.
modified.
RW(Row Wait) Decimal representation of Decimal representation of
file no. and block no. row within block
(Version 6)
UL(User Defined Complete list is found in chpt 10 of the Oracle 7 Concepts
Locks) Manual or in Appendix B-81 in the Oracle 7 Admin Guide.
Please note that the RW enqueue was taken out after
version 6.0.34 and may be added back in a future release.
Mode Held Lock mode held.
Mode Requested Lock mode requested.
The following users are waiting:
LTO2 6 35 TX 262167 87 NONE X
LTO3 14 17 TX 262167 87 NONE X
They are waiting on:
LTO 12 15 TM 2294 0 RX NONE
LTO 12 15 TM 2295 0 RX NONE
LTO 12 15 TX 262167 87 X NONE
For the TX lock, lock id1 and lock id2 are the same when they are contending
for the same resouces. Within the block, we have a record of the each
transactions pertaining to the data in that particular block. If the
transaction is not committed or rolled back, then other subsequent
transactions may have to wait for that resource.
Often, the user(s) may be modifying many tables within the same transaction.
At times, this will make it difficult to find out which resource the WAITER
is contending from the HOLDER. This is easily resolved by looking at a
combination of two monitors. MONITOR SESSION will tell you which user is
waiting on a lock and MONITOR TABLE will tell you the table that the user is
currently trying to modify.
MONITOR SESSION:
---------------
Session Serial Process Lock
ID Number ID Status Username Waited Current Statement
===============================================================================
5 31 19 INACTIVE LTO INSERT
6 43 25 ACTIVE LTO2 C3D320F4 UPDATE
9 1 26 ACTIVE LTO3 C3D320C8 DELETE
MONITOR TABLE:
-------------
Session
ID Schema Name Table Name
===============================================================================
5 LTO DEPT
6 LTO EMP
9 LTO EMP
Session 6 and 9 are waiting for locks. They are both attempting to modify
lto.emp TABLE.
RESOLUTION:
==========
Most locking issues are application specifics. To resolve locking contention,
one needs to free the resource by:
1. Asking the HOLDER to commit or rollback,
2. Killing the session which holds the lock,
ALTER SESSION KILL SESSION sid, serial#; or
use KILL USER SESSION menu found in the sqldba form.
3. Killing the unix shadow process which is not recommended.
When killing the unix shadow process, please be careful of shared
servers in a multi-threaded environment.
4. ROLLBACK FORCE or COMMIT FORCE if 2pc pending transaction.
SQL STATEMENTS TO DECIPHER LOCKING ISSUES:
=========================================
TRANSACTIONS INVOLVED:
---------------------
This query defines the transactions involved in a locking situation:
column username format a10
column sid format 999
column lock_type format a15
column MODE_HELD format a11
column MODE_REQUESTED format a10
column LOCK_ID1 format a8
column LOCK_ID2 format a8
select a.sid,
decode(a.type,
'MR', 'Media Recovery',
'RT', 'Redo Thread',
'UN', 'User Name',
'TX', 'Transaction',
'TM', 'DML',
'UL', 'PL/SQL User Lock',
'DX', 'Distributed Xaction',
'CF', 'Control File',
'IS', 'Instance State',
'FS', 'File Set',
'IR', 'Instance Recovery',
'ST', 'Disk Space Transaction',
'IR', 'Instance Recovery',
'ST', 'Disk Space Transaction',
'TS', 'Temp Segment',
'IV', 'Library Cache Invalidation',
'LS', 'Log Start or Switch',
'RW', 'Row Wait',
'SQ', 'Sequence Number',
'TE', 'Extend Table',
'TT', 'Temp Table',
a.type) lock_type,
decode(a.lmode,
0, 'None', /* Mon Lock equivalent */
1, 'Null', /* N */
2, 'Row-S (SS)', /* L */
3, 'Row-X (SX)', /* R */
4, 'Share', /* S */
5, 'S/Row-X (SSX)', /* C */
6, 'Exclusive', /* X */
to_char(a.lmode)) mode_held,
decode(a.request,
0, 'None', /* Mon Lock equivalent */
1, 'Null', /* N */
2, 'Row-S (SS)', /* L */
3, 'Row-X (SX)', /* R */
4, 'Share', /* S */
5, 'S/Row-X (SSX)', /* C */
6, 'Exclusive', /* X */
to_char(a.request)) mode_requested,
to_char(a.id1) lock_id1, to_char(a.id2) lock_id2
from v$lock a
where (id1,id2) in
(select b.id1, b.id2 from v$lock b where b.id1=a.id1 and
b.id2=a.id2 and b.request>0)
/
Sample output:
SID LOCK_TYPE MODE_HELD MODE_REQUE LOCK_ID1 LOCK_ID2
---- --------------- ----------- ---------- -------- --------
5 Transaction Exclusive None 262172 90
6 Transaction None Exclusive 262172 90
9 Transaction None Exclusive 262172 90
This is very similar to the monitor lock output. Again, SID 6 and 9 are
waiting on SID 5.
OBJECTS INVOLVED:
----------------
This query will defined the objects that are involved in the locking conflict:
column username format a10
column lockwait format a10
column sql_text format a80
column object_owner format a14
column object format a15
select b.username username, c.sid sid, c.owner object_owner,
c.object object, b.lockwait, a.sql_text SQL
from v$sqltext a, v$session b, v$access c
where a.address=b.sql_address and
a.hash_value=b.sql_hash_value and
b.sid = c.sid and c.owner != 'SYS'
/
Sample output:
USERNAME SID OBJECT_OWNER OBJECT LOCKWAIT
---------- ---------- -------------- --------------- ----------
SQL
----------------------------------------------------------------
LTO2 6 LTO EMP C3D320F4
update lto.emp set empno =25 where empno=7788
LTO3 9 LTO EMP C3D320C8
delete from lto.emp where empno=7788
LTO 5 LTO DEPT
insert into lto.dept values (60,'PROGRAMMER','LOS ANGELOS')
One can see the DMLs resulting from LTO2 and LTO3 who are the WAITERS.
They are waiting for LTO to commit or rollback; unfortunately, the DML
statement listed under LTO is only the most recent DML. This may not indicate
the statement that is holding the resource. In this example, it is NOT the
DML. The only DML was an update statement. OBJECT_OWNER.OBJECT is the object
USERNAME is attempting to modify.
PROCESSES INVOLVED:
------------------
To ascertain process information, one can execute this query:
column "ORACLE USER" format a11
column SERIAL# format 9999999
column "OS USER" format a8
select substr(s.username,1,11) "ORACLE USER", p.pid "PROCESS ID",
s.sid "SESSION ID", s.serial#, osuser "OS USER", p.spid "PROC SPID",
s.process "SESS SPID", s.lockwait "LOCK WAIT"
from v$process p, v$session s, v$access a
where a.sid=s.sid and
p.addr=s.paddr and
s.username != 'SYS'
/
Sample output:
ORACLE USER PROCESS ID SESSION ID SERIAL# OS USER PROC SPID SESS SPID LOCKWT
----------- ---------- ---------- -------- -------- --------- -----------------
LTO 19 5 31 usupport 17312 17309
LTO2 25 6 43 usupport 17313 17310 C3D320F4
LTO3 26 9 1 usupport 17314 17311
C3D320C8
What can you do??
1) Can ask LTO to commit or rollback or
2) alter system kill session '5,31'; or
3) kill -9 17309 (shadow process on unix)
stop/id=<SESS SPID> (PROC SPID=SESS SPID on vms running single task)
Notice that the user SYS has been excluded. If your application was created
under SYS, then you may want to include it in the query.
USING CATBLOCK.SQL and UTLLOCKT.SQL:
-----------------------------------
These are very helpful scripts that oracle provides with the other RDBMS
installation scripts (CATALOG.SQL, CATPROC.SQL, etc.) For example,
?/rdbms/admin directory on unix and ora_rdbms directory on vms.
First run catblock.sql as sys and then run utllock.sql as sys.
Print out the result in a tree structured fashion:
column waiting_session format a8
select lpad(' ',3*(level-1)) || waiting_session waiting_session,
lock_type,
mode_requested,
mode_held,
lock_id1,
lock_id2
from lock_holders
connect by prior waiting_session = holding_session
start with holding_session is null;
Sample Output:
WAITING_ LOCK_TYPE MODE_REQUE MODE_HELD LOCK_ID1 LOCK_ID2
-------- ----------------- ---------- ---------- ---------- ----------
5 None
6 Transaction Exclusive Exclusive 262172 90
9 Transaction Exclusive Exclusive 262172 90
Sessions 6 and 9 are waiting on session 5.
DRASTIC METHODS IN DECIPHERING A LOCKING OR "HANGING" SITUATION:
===============================================================
At times, it may be difficult to find out the root cause of the
"hanging" problem within your application. One may need to resort to
drastic measures which include both tracing on the database and operating
system layer. Here are some recommendations:
1) Turn on sql_trace. This will reveal the sql statements involved.
2) Set timing on and monitor processes on the operating system side
as well. This will reveal if the process is getting any cpu and the
status of the process.
3) Utilize the operating system debugging facilities or utilize "oradbx" if
available. (oradbx is only for support and development use and may not
be available on your platform.)
4) Utilize monitor screens and the locking (blocking) scripts.
5) One can build your own self monitoring script.
For example:
Create a file called monitor.sql:
select b.username username, c.sid sid, c.owner object_owner,
c.object object, b.lockwait, a.sql_text SQL
from v$sqltext a, v$session b, v$access c
where a.address=b.sql_address and
a.hash_value=b.sql_hash_value and
b.sid = c.sid and
c.owner != 'SYS'
/
@?/rdbms/admin/utllockt
!ps -ef
!sleep <time_interval>
Create a file name runtest with executable permissions:
sqlplus name/password << test
spool output.txt
set time on
set echo on
@monitor
ASCERTAINING PROCESS STACKS:
===========================
In some rare cases, one may encounter a "hanging" problem that can
not be fathom. One may find that a process stack of the hanging process or
the process that holds the resource may be helpful in resolving the
problem.
1) Operating system debugging tools can be used to find out the last call
before it hangs. ie. truss -p <shadow pid>
2) ORACLE'S debugging utility, oradbx, is for SUPPORT and DEVELOPMENT's
use ONLY.
select substr(s.username,1,11) "ORACLE USER" ,
p.pid "PROCESS ID", s.sid "SESSION ID", s.serial#,
osuser "OS USER", p.spid "PROC SPID"
from v$session s, v$access a
where a.sid=s.sid and
p.addr=s.paddr and
s.username != 'SYS'
/
Sample Output:
ORACLE USER PROC ID SESSION ID SERIAL# OS USER PROC SPID SESS SPID LOCK WAIT
--------------------------------------- --------------------------------------
LTO 19 5 31 usupport 17312 17309
LTO2 25 6 43 usupport 17313 17310 C3D320F4
LTO3 26 9 1 usupport 17314 17311 C3D320C8
cd $ORACLE_HOME/rdbms/lib
make -f oracle.mk oradbx
To find out what LTO process is actually doing, one can dump the process stack.
ps -ef | grep 17312
usupport 17312 17309 0 Sep 15 ? 0:00 oracleV713 (DESCRIPTION=(LOCAL=YE
type <oradbx>
debug 17312 (which is the oracle shadow process for this user)
dump stack
dump procstat
These trace files may be instrumental in resolving a hanging problem. Please
remember that Oracle's debugging utility, oradbx, is for Support and
Development's use ONLY. The trace files should go to your USER_DUMP_DEST and
should be sent to Worldwide Support for debugging.
UNUSUAL LOCKING PROBLEMS:
========================
1) When your application has referential integrity and attempts to modify
the child table, Oracle will get a TABLE LEVEL SHARE LOCK on the parent
table when there is NO index on the foreign key.
2) When a table's PCTFREE is set too low , the block is full with data, and
there are many concurrent DML's occurring on rows within the block, one may
see a Share Lock being requested when doing a DML. To my knowledge, this
is only time we grab the SHARED lock. Instead of waiting for a lock, this
process is waiting for some extra space or a release of an INITRANS within
the transaction layer of the block.
Here's a simple example:
create table test (a number) initrans 1 maxtrans 1;
SYSTEM: insert into test values (5); /* completed */
SCOTT: insert into SYSTEM.test values (10); /* Scott waits */
In normal situations, SCOTT should not have to wait but SCOTT has to wait until
SYSTEM releases the one and only INITRANS in the block. If the MAXTRANS
was not set to 1, then oracle will try to allocate another INITRANS in the
block if space permits. I believe each INITRANS is about 24 bytes.
SID OWNER LOCK_TYPE MODE_HELD MODE_REQUE LOCK_ID1 LOCK_ID2
---- ----- --------------- ----------- ---------- -------- --------
7 System Transaction Exclusive None 196639 54
10 Scott Transaction None Share 196639 54
LOCKING CHART:
=============
The following table describes which table accesses are gotten for which
table operations and what equivalent DML modes are actually gotten in
various conditions.
DML Table Lock Mode
Yes Yes No No Row-Locking
Operation Table Access No Yes No Yes Serializable
--------------- --------------- ------- ------- ------- -------
Select Read NULL S NULL S
Select
For Update Row-Read SS S SS S
Insert Row-Write SX SX SSX SSX
Update Read-Row-Write SX SSX SSX SSX
Delete Read-Row-Write SX SSX SSX SSX
Lock For Update Row-Read SS S SS S
Lock Share S S S S
Lock Exclusive X X X X
Lock Row Share SS SS SS SS
Lock Row Exclusive SX SX SX SX
Lock Share Row Exclusive SSX SSX SSX SSX
Alter Write X X X X
Drop Write X X X X
Create Index S S S S
Drop Index Write X X X X
---------------------------------------------------------------
Lock Mode Compatibility:
-----------------------
NULL SS SX S SSX X
-----------------------------------------------------
NULL YES YES YES YES YES YES
SS YES YES YES YES YES no
SX YES YES YES no no no
S YES YES no YES no no
SSX YES YES no no no no
X YES no no no no no