DETECTING AND RESOLVING LOCKING CONFLICTS -------------------------------------------------------------------------------- Document ID: 106450.503 Title: Detecting and Resolving Locking Conflicts Creation Date: 24 September 1994 Last Revision Date: 24 September 1994 Revision Number: 0 Product: RDBMS Product Version: 7.1.3 Platform: GENERIC Information Type: Solution Impact: High Abstract: This article describes how to detect and resolve locking and hanging problems. It gives numerous suggestions when dealing with difficult locking issues which involve complex applications. This bulletin should be used as a stepping stone in resolving locking issues. Keywords: LOCK;SESSION;TX;TM;SQL_TEXT;HANGING ------------------------------------------------------------------------------ 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= (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 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 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 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 ------------------------------------------------------------------------------