In this Document
Purpose
Last Review Date
Instructions for the Reader
Troubleshooting Details
How does Oracle handle locking?
How to find the resource definitions?
Which lock modes are required for which table action?
How compatibility of locks works
How to detect locking situations?
Which views can be used to detect locking problems?
Which tools are there to diagnostic locking issues?
How to resolve locking situations?
Deadlock Situations

Unusual locking problems
How to use dbms_lock?
References

Applies to:

Purpose

Last Review Date

Instructions for the Reader

Troubleshooting Details

How does Oracle handle locking?

• Releasing locks are performed by the sessions when they issue a commit or a DDL statement (i.e. implicit commit), or by PMON if the sessions have been killed. (Remember this only pertains to row locks. Only TX, TM, and DX enqueues are held until commit. Other locks such as RT enqueues will be held until the instance is shutdown.)
  
  
• Conversion is the process of changing a lock from the mode we currently hold to a different mode.
  We are allowed to convert a lock if the mode we require, is a subset of the mode we hold or is compatible with the modes already held by other sessions. Otherwise, we wait on the converters queue of the resource.
  
  
• Acquiring a lock is the process of getting a lock on a resource on which we currently do not have a lock.
  We are allowed to acquire a lock, if there are no converters or waiters ahead of us and the mode we require is compatible with the modes already held by others. Otherwise, we wait on the waiters queue of the resource.
  
  When a session has a lock on a resource, then it stands in the owner queue of the resource. When a lock is released or converted, the converters and waiters are re-checked to see if they can be acquired. The converters are processed first, then the waiters.

• User A gets an S lock on resource 1
• User B gets an S lock on resource 2
• User A request an X lock on resource 2 and waits
  
• User B requests an X lock on resource 1 and waits
  
  A is waiting for B to release resource 2, which is waiting for A to release resource 1
  A is indirectly waiting for A. This is a deadlock, generating a tracefile in the user_dump_dest and and ORA-60 in the detecting session which results in the session being killed.
  

How to find the resource definitions?

1. The TM resource, known as the DML enqueue, is acquired during the execution of a statement when referencing a table so that the table is not dropped or altered during the execution of it.
   
   
2. The TX resource, known as the transaction enqueue, is acquired exclusive when a transaction initiates its first change and is held until the transaction does a COMMIT or ROLLBACK. Row locking is based on TX enqueues. PMON will mark a transaction status as dead in the undo segment header. At that point the TX enqueue associated with that transaction is also released. PMON will then attempt to rollback some of the changes associated with the "dead" transaction, but will then pass it to SMON to apply the remainder of the associated undo records.
   
   
3. The UL resource represent the user-defined locks defined by the DBMS_LOCK package.
   

Which lock modes are required for which table action?

The following table describes what lock modes on DML enqueues are actually gotten for which table operations in a standard Oracle installation.

How compatibility of locks works

The compatibility of lock modes are normally represented by following matrix:

 	
LMODE
Description
Name
NULL
SS
SX
S
SSX
X
0,1
No Lock
NULL
 YES
 YES
 YES
 YES
 YES
 YES
2
Row-Share
SS
 YES
 YES
 YES
 YES
 YES
 no
3
Row-Exclusive
SX
 YES
 YES
 YES
 no
 no
 no
4
Share
S
 YES
 YES
 no
 YES
 no
 no
5
Share Row-Exc
SSX
 YES
 YES
 no
 no
 no
 no
6
Exclusive
X
 YES
 no
 no
 no
 no
 no

How to detect locking situations?

Different tools can be used to detect locking issues and the identity of a blocking session or sessions. The following article contains some selects to find blocking locks:

A methodology using OEM Top Sessions report is explained in:

Which views can be used to detect locking problems?

• SID identifier of session holding the lock
• P1, P2, P3 determine the resource when event = 'enqueue'
• SECONDS_IN_WAIT gives how long the wait did occurs
  

• SID, SERIAL# identifier of the session
• EVENT event waited on
• P1, P2, P3 determine the resource when event = 'enqueue'
• # SECONDS_IN_WAIT gives how long the wait did occurs
  
• LOCKWAIT address of the lock waiting, otherwise null
• ROW_WAIT_OBJ# object identified of the object we are waiting on (object_id of dba_objects)
• ROW_WAIT_FILE#, ROW_WAIT_BLOCK#, ROW_WAIT_ROW#
  file_id , block_id and row location within block of the locked row
  

• SID identifier of session holding the lock
• TYPE, ID1 and ID2 determine the resource
• LMODE and REQUEST indicate which queue the session is waiting on, as follows:
  LMODE > 0, REQUEST = 0 owner
  LMODE = 0, REQUEST > 0 acquirer
  LMODE > 0, REQUEST > 0 converter
• CTIME time since current mode was converted
• BLOCK are we blocking another lock
  BLOCK = 0 non blocking
  BLOCK = 1 blocking others
  

• SESSION_ID == SID in V$LOCK
• LOCK_TYPE, LOCK_ID1, LOCK_ID2 formatted value of TYPE, ID1, ID2 from V$LOCK
• MODE_HELD and MODE_REQUESTED formatted value of LMODE and REQUEST from V$LOCK
• LAST_CONVERT == CTIME of V$LOCK
• BLOCKING_OTHERS formatted value of BLOCK from V$LOCK
  

• XIDUSN, XIDSLOT and XIDSQN rollback information to be linked with V$TRANSACTION
• OBJECT_ID object being locked
• SESSION_ID session id
• ORACLE_USERNAME oracle user name
• OS_USER_NAME OS user name
• PROCESS OS process id
• LOCKED_MODE lock mode
  

• TYPE, ID1 and ID2 determine the resource

• WAITING_SESSION waiting session
• HOLDING_SESSION holding session
• LOCK_TYPE, LOCK_ID1, LOCK_ID2 resource locked
• MODE_HELD lock type held
• MODE_REQUESTED lock type requested
  

• HOLDING_SESSION holding session

Which tools are there to diagnostic locking issues?

1. OEM Top Session
   OEM Top Session permits to see all the session information. The sessions that are in status ACTIVE can be waiting on locks. By right clicking on those sessions and choosing the 'detail' screen, you can go to the 'lock' leaflet and find blocking sessions.
   For more information see
   
   
   Note 164760.1 Detecting and Resolving Locking Conflicts using TopSessions
   
   
2. Lock Charts in Performance Manager of Oracle Enterprise Manager
   OEM contains a lock graph facility named the Lock Charts in the Performance Manager that is part of the Oracle Diagnostics Pack of OEM. The Performance Manager requires the Oracle Data Gatherer to run. You should select the database from the OEM Console Navigator window. Then launch Performance Manager from the Applications menu bar. When the application comes up, select the 'Lock Charts'. You can use the Performance Manager to kill the session. You just need to right-mouse click on the session you want to kill. Since the Lock Charts are automatically refreshed, you can watch the charts and instantly see locks being released.
   
   
3. Scripts for detecting locking
   A number of sample scripts exist to retrieve locking information.
   
   
   Note 1020012.6 TFTS SCRIPT. TO RETURN MEDIUM DETAIL LOCKING INFO Note 1020008.6TFTS FULLY DECODED LOCKING SCRIPT
   Note 1020007.6 SCRIPT. DISPLAY LOCKS AND GIVE SID AND SERIAL # TO KILL ()
   Note 1020010.6 SCRIPT. DISPLAY SQL TEXT FROM LOCKS
   Note 1020047.6 SCRIPT. SCRIPT. TO DISPLAY USER LOCK INFORMATION Note 1020088.6 SCRIPT. REPORT SESSIONS WAITING FOR LOCKS ()
   
   
   
4. Using the logminer (Oracle8i onwards)
   All locking statements are logged in the redologs.
   
   
   Note 198828.1 Analyzing them permits to understand locking situations
   
   

How to resolve locking situations?

1. Asking the HOLDER to commit or rollback
2. Killing the session which holds the lock,
   For example:
   
   ALTER SESSION KILL SESSION 'sid, serial#';
3. Killing the unix/vms shadow process directly.
   This is not recommended as it may prevent proper cleanup of a session
   When killing the shadow process, please be careful of shared servers in a multi-threaded environment.
4. ROLLBACK FORCE or COMMIT FORCE if 2pc pending transaction.
   

Deadlock Situations

Unusual locking problems

1. Some common locking scenarios are explained in:
   
   
   Note 62354.1TX Transaction and Enq: Tx - Row Lock Contention - Example wait scenarios
   
   
   
2. When your application has referential integrity and attempts to modify the child/parent table, Oracle will get additional locking on the parent/child table when there is NO index on the foreign key. To bypass this problem, the most efficient way is to create indexes for all foreign key defined in the database. See the following notes:
   Note 1019527.6 'TFTS CHECK FOR FOREIGN KEY LOCKING'
   Note 33453.1 REFERENTIAL INTEGRITY AND LOCKING
   Note 223303.1 Correction to 9.2.0 foreign key constraint locking behaviour, per documentation Bug:2546492
   
   
3. When your application is using DBMS_PIPE extensively, your session can wait for CI locks. You should increase your shared pool.
   
   
4. When statements like 'CREATE INDEX' and 'ALTER INDEX REBUILD' are issued, Oracle behave differently in Oracle7 compared to Oracle8i. To understand the benefit of the ONLINE option, you can find more information in
   
   
   Note 70120.1 Locking Behavior. During Index Creation or Index Rebuild
   
   
   
5. When a table's INITRANS 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.
   
   
   See Note 62354.1 TX Transaction locks - Example wait scenarios
   
   
   
6. There are other systemwide locks that can be held at any given time.
   See
   
   
   Note 102925.1 Tracing sessions: waiting on an enqueue
   
   
   
7. Distributed transactions also use locks
   
   
   See Note 118219.1 Detecting and Resolving Distributed Locking Conflicts
   
   

How to use dbms_lock?

References