Oracle Locks FAQ

FAQ about Detecting and Resolving Locking Conflicts [ID 15476.1]
  Questions and Answers
     1. How does Oracle handle locking?
     2. How to find the resource definitions?
     3. Which lock modes are required for which table action?
     4. How compatibility of locks work
     5. Which views can be used to detect locking problems?
     6. Which tools are there to diagnostic locking issues?
     7. How to detect locking situations?
     8. How to resolve locking situations?
     9. How to resolve deadlock situations?
     10. Unusual locking problems
     11. How to use dbms_lock?
     References


Row Exclusive vs. Share - What is the difference between the following types of locks? [ID 266720.1]
     RESEARCH / REFERENCES
     EXCLUSIVE VS. SHARED
     Exclusive:
     Shared:
     ROW LEVEL LOCK
     WHEN LOCK CREATED
     ENQUEUE MECHANISM
     ROW EXCLUSIVE VS. ROW SHARE
     DETECTING LOCKS
          @$ORACLE_HOME/rdbms/admin/utllockt.sql


            select * from v$locked_object;
      
         col OS_USER_NAME head OS_USER for a7 trunc
            col ORACLE_USERNAME head ORCL_USER for a10 trunc
         select   ORACLE_USERNAME,OS_USER_NAME,OBJECT_NAME,locked_mode,session_id
            from all_objects a ,sys.V_$LOCKED_OBJECT b
            where a.OBJECT_ID=b.object_id;

     METALINK NOTES
     LOCKING BUG
  References




HOW TO FIND THE SESSION HOLDING A LIBRARY CACHE LOCK [ID 122793.1]

METHOD 1: SYSTEMSTATE ANALYSIS

  For Oracle 9.2.0.1 or higher

        $ sqlplus '/ as sysdba'
          oradebug setmypid
          oradebug unlimit
          oradebug dump systemstate 266
          wait 90 seconds
          oradebug dump systemstate 266
          wait 90 seconds
          oradebug dump systemstate 266
          quit

METHOD 2: EXAMINE THE X$KGLLK TABLE

Note:1054939.6 COMPILATION OF PACKAGE IS HANGING ON LIBRARY CACHE LOCK 

VIEW: "V$LOCK" Reference Note [ID 29787.1]

Note:1020012.6 SCRIPT TO RETURN MEDIUM DETAIL LOCKING INFO

Note:1020011.6 SCRIPT: LOW COMPLEXITY LOCKING INFO

Identifying Locking Issues and utllockt.sql

Performance Monitoring Data Dictionary Views

This section lists some of the data dictionary views that you can use to monitor an Oracle Database instance. These views are general in their scope. Other views, more specific to a process, are discussed in the section of this book where the process is described.

View	Description
V$LOCK	Lists the locks currently held by Oracle Database and outstanding requests for a lock or latch
DBA_BLOCKERS	Displays a session if it is holding a lock on an object for which another session is waiting
DBA_WAITERS	Displays a session if it is waiting for a locked object
DBA_DDL_LOCKS	Lists all DDL locks held in the database and all outstanding requests for a DDL lock
DBA_DML_LOCKS	Lists all DML locks held in the database and all outstanding requests for a DML lock
DBA_LOCK	Lists all locks or latches held in the database and all outstanding requests for a lock or latch
DBA_LOCK_INTERNAL	Displays a row for each lock or latch that is being held, and one row for each outstanding request for a lock or latch
V$LOCKED_OBJECT	Lists all locks acquired by every transaction on the system
V$SESSION_WAIT	Lists the resources or events for which active sessions are waiting
V$SYSSTAT	Contains session statistics
V$RESOURCE_LIMIT	Provides information about current and maximum global resource utilization for some system resources
V$SQLAREA	Contains statistics about shared SQL area and contains one row for each SQL string. Also provides statistics about SQL statements that are in memory, parsed, and ready for execution
V$LATCH	Contains statistics for nonparent latches and summary statistics for parent latches

Identifying Locking Issues 
Note: text in this session was copied from toadworld.com


1 Query to show information about locked objects:
SELECT O.OWNER, O.OBJECT_ID, O.OBJECT_NAME, O.OBJECT_TYPE, L.TYPE
FROM DBA_OBJECTS O, V$LOCK L
WHERE O.OBJECT_ID = L.ID1
/



OWNER       OBJECT_ID OBJECT_NAME          OBJECT_TYP TY
---------- ---------- -------------------- ---------- --
SYSTEM          32392 FOOBAR               TABLE      TM
SYSTEM          32392 FOOBAR               TABLE      TM
...
SYSTEM          32371 MY_DEPT              TABLE      TM

2 DBA_BLOCKERS

The DBA_BLOCKERS view has one column, HOLDING_SESSION, which indicates each session that is:

• Not currently waiting on a locked object, and
• Currently holding a lock on an object for which another session is waiting.

3 DBA_WAITERS

The DBA_WAITERS view is the counterpart to DBA_BLOCKERS. It indicates which sessions are:

• Currently waiting on a locked object, and
• Not currently holding a lock on an object for which another session is waiting.

The WAITING_SESSION column indicates the SID of the waiting session, and the HOLDING_SESSION column indicates the SID of the holding session. The view shows the lock type, and the modes in which the lock is being held and requested.

Oracle Locking Survival Guide Note: text below was copied from this website, which is likely copied from akadia.com

utllockt.sql

This is the original Oracle script to print out the lock wait-for graph in a tree structured fashion. This script prints the sessions in the system that are waiting for locks, and the locks that they are waiting for. The printout is tree structured. If a sessionid is printed immediately below and to the right of another session, then it is waiting for that session. The session ids printed at the left hand side of the page are the ones that everyone is waiting for (Session 96 is waiting for session 88 to complete):

WAITING_SESSION   LOCK_TYPE    MODE_REQUESTED MODE_HELD  LOCK_ID1  LOCK_ID2
----------------- ------------ -------------- ---------- --------- --------
88                None
   96             Transaction  Exclusive      Exclusive  262144    3206

The lock information to the right of the session id describes the lock that the session is waiting for (not the lock it is holding). Note that this is a script and not a set of view definitions because connect-by is used in the implementation and therefore a temporary table is created and dropped since you cannot do a join in a connect-by.

This script has two small disadvantages. One, a table is created when this script is run. To create a table a number of locks must be acquired. This might cause the session running the script to get caught in the lock problem it is trying to diagnose. Two, if a session waits on a lock held by more than one session (share lock) then the wait-for graph is no longer a tree and the conenct-by will show the session (and any sessions waiting on it) several times.

Basic Blocking Lock Detection

In Doug Burns' website, I found this very helpful article about Oracle Locks. 

PX Deq Credit: send blkd

This is a place holder for "PX Deq Credit: send blkd" wait event.  It will be expanded later with more information.
1 PX Deq Credit: send blkd - wait for what?
2 PX Deq Credit: send blkd caused by IDE (SQL Developer, Toad, PL/SQL Developer)
3 Session Tracing and Wait Events
4 Tips to Reduce Waits for "PX DEQ CREDIT SEND BLKD" at Database Level [ID 738464.1]
5 Report for the Degree of Parallelism on Tables and Indexes [ID 270837.1]
6 Use Multiple CPUs with Oracle Parallel Query
7 Row Exclusive vs. Share - What is the difference between the following types of locks? [ID 266720.1]
8 Using Oracle's Parallel Execution Features
9 Auto DOP and Parallel Statement Queuing
10 Parallel SQL Execution in Oracle 10g
11 Parallel Query Option - Setting Degree of Parallelism
12 Parallel Query FAQ

Library Cache Lock

This post is a space holder for "Library Cache Lock" wait event.  It will be expanded later with more information.
1 Process hung on library cache lock
2 Oracle Library Cache
   Part I
   Part II
3 Library cache lock and library cache pin waits
4 Resolving a library cache lock issue
5 Systematic Latch Contention Troubleshooting in Oracle
6 Row Exclusive vs. Share - What is the difference between the following types of locks? [ID 266720.1]

Row Cache Lock

This is a space holder for "Row Cache Lock" wait event detected on a 2-nodes Oracle database. This post is to be expanded later:
1 Investigate ROW CACHE LOCK
2 WAITED TOO LONG FOR A ROW CACHE ENQUEUE LOCK!的分析
3 Systemstate dump analysis: Nocache on high intensive sequences in RAC
4 Latch Row Cache Objects基本信息的确认

Some good articles about Oracle

1 面对一个全新的环境,作为一个Oracle DBA,首先应该了解什么?
2 Oracle GridControl 11gR1 for Linux 安装和配置指南
3 exadata x2-8的技术方向终于向正确的方向有所改进
4 Oracle Support Master Note for 10g Enterprise Manager Grid Control Agent Performance & Core Dump issues (Doc ID 1087997.1)
5 Working with Materialized Views Creation, Refresh and Monitoring
6 Starting and Stopping Oracle Enterprise Manager 11g Grid Control
7 Grid Control 11 Known Issues, Bugs and Features
8 Master Note: How to diagnose Database Performance - FAQ [ID 402983.1]
9 No Response from the Server, Does it Hang or Spin? [ID 68738.1]
10 Data Gathering for Troubleshooting RAC Issues [ID 556679.1]
11 What is enq: TX - row lock contention?
12 enq: TX - row lock contention
13 Use Batch File to Create Filename Using Date and Time
14 EMD upload error: Upload was successful but collections currently disabled - disk full
15 SQL Loader
16 Script to Detect Tablespace Fragmentation [ID 1020182.6]
17 Important Changes to Oracle Database Patch Sets Starting With 11.2.0.2 [ID 1189783.1]
18 Troubleshooting Oracle Clusters and Oracle RAC