Applies to:
Oracle Server - Standard Edition - Version 7.0.16.0 and later
Oracle Server - Personal Edition - Version 7.1.4.0 and later
Oracle Server - Enterprise Edition - Version 7.0.16.0 and later
Information in this document applies to any platform.
Purpose
This document provides an overview reference to content that pertains to the resolution of locks, enqueues and deadlocks affecting the database.
Questions and Answers
General Notes on Enqueues
What is an enqueue?
Enqueues are shared memory structures (locks) that serialize access to database resources.
They can be associated with a session or transaction. The following are some relevant reference documents:
A complete list of enqueues can be found at by querying V$LOCK_TYPE:
from v$lock_type
or in the documentation under: v$lock types
User Type Locks (TM, TX and UL Enqueues)
User locks are locks that are obtained by user applications to protect the integrity of data and the structure of schema objects. The following 3 enqueue types are defined as "User Type" Locks:
TM -DML (Table Manipulation) Enqueue called against a base table or partition for various table / partition operations that need to be co-ordinated.
TX - Transaction Enqueue used to protect transaction information.
UL - User Lock Enqueue used when an application makes use of the DBMS_LOCK package.
The following notes contain information relating to these enqueues and how to troubleshoot issues where there are excessive enqueues or deadlocks involving user type locks:
Document 62354.1 TX Transaction and Enq: Tx - Row Lock Contention - Example wait scenarios
Document 33453.1 REFERENTIAL INTEGRITY AND LOCKING
Document 223303.1 Correction to 9.2.0 foreign key constraint locking behaviour, per documentation Bug:2546492
Document 70120.1 Locking Behavior. During Index Creation or Index Rebuild
Document 1343365.1 Increase in TM enqueue in 11g release
Document 1317447.1 Direct Path Insert - APPEND HINT and TM Enqueue LOCK Behaviour
RAC Specific
Diagnostics
There are a number of general diagnostic articles and scripts available to assist with the resolution of locking issues:
Document 15476.1 FAQ: Detecting and Resolving Locking Conflicts and Ora-00060 errors
Scripts to help Diagnose Locking Issues
Document 1020012.6 SCRIPT. TO RETURN MEDIUM DETAIL LOCKING INFO
Document 1020011.6 SCRIPT. LOW COMPLEXITY LOCKING INFO
Document 1019527.6 SCRIPT. TO CHECK FOR FOREIGN KEY LOCKING ISSUES
Document 728539.1 Find Blocking Sessions In SQLPLUS
Deadlocks
What is a deadlock?
A deadlock is a situation in which two or more users are trying to modify the same piece of data, but the second user finds that the user holding the resource is waiting for data that he is already locking. This situation usually occurs because a consistent locking strategy has not been followed thoughout an application.
Deadlocks prevent some transactions from continuing to work.
Oracle Database automatically detects deadlocks and resolves them by rolling back one statement involved in the deadlock,releasing one set of the conflicting row locks.
The database returns a corresponding message to the transaction that undergoes statement-level rollback.The statement rolled back belongs to the transaction that detected the deadlock.
Document 62365.1 What to do with "ORA-60 Deadlock Detected" Errors
System Enqueues
A partial list of System Enqueues with relevant documentation is posted below: The list is not exhaustive, but contains the most common system enqueues.
BF enqueue
BF enqueue Synchronize access to a bloom filter in a parallel statement
PS enqueue: Parallel Execution Server Process reservation and synchronization
CF Controlfile Enqueue
CF enqueue serializes controlfile transactions, reads and writes on shared portions of the controlfile.
General
Document 1072417.1 Performance Degradation as a Result of 'enq: CF - contention'
Document 1380939.1 Excessive CF enqueue when performing many parallel NOLOGGING DML
Document 8318486.8 Bug 8318486 - CF enqueue contention while dumping redo for pinned buffer history
Document 8702276.8 Bug 8702276 - CF enqueue held for too long with fix 7593835
Document 779552.1 ORA-00494 Or ORA-600 [2103] During High Load After 10.2.0.4 Upgrade
Flashback Related
Document 6976005.8 Bug 6976005 - ORA-494 CF enqueue held for longer than needed with flashback enabled
Document 8499385.8 Bug 8499385 - Flashback Database may hang/spin holding CF Enqueue causing ORA-494
Dataguard Related
DX Distributed Transaction Enqueue
DX enqueue is used to protect distributed transactions.
Document 730423.1 Select With Local Function and Remote Tables Using a Dblink Hangs Due To Enq DX
Document 9773415.8 Bug 9773415 - GTX may deadlock with foreground on DX lock
Document 8477973.8 Bug 8477973 - Multiple open DB links / ORA-2020 / distributed deadlock / ORA-600 possible using DB Links
HW High Water Enqueue
The HW enqueue is used to serialize the allocation of space beyond the high water mark of a segment.
Document 419348.1 How To Analyze the Wait Statistic: 'enq: HW - contention'
Document 740075.1 enq HW - contention' For Busy LOB Segment
Document 837883.1 LOB HWM CONTENTION :Using AWR Reports to Identify the Problem; Confirm and Verify the Fix
Document 6413373.8 Bug 6413373 - Lob HW enqueue contention in RAC environments with ASSM space management
JS Job Scheduler Enqueue
RO Fast Object Reuse Enqueue
The RO enqueue known as "Multiple object resue" enqueue, is used to synchronise operations between foreground process and a background process such as DBWR or CKPT. It is typically used when you are dropping objects or truncating tables.
Document 7385253.8 Bug 7385253 - Slow Truncate / DBWR uses high CPU / CKPT blocks on RO enqueue
Document 8544896.8 Bug 8544896 - Waits for "enq: RO - fast object reuse" with high DBWR CPU
Document 5177241.8 Bug 5177241 - Truncate can hang converting RO enqueue SSX to X
SQ Sequence Cache Enqueue
Serializes access to Oracle sequences.
Document 378302.1 How to Set 'Cache Size' of a Sequence Object?
Document 432508.1 High SQ Enqueue Contention with LOBs or Advanced Replication
TO Temporary Table Object Enqueue
TT Tablespace Operations Enqueue
This enqueue is used to avoid deadlocks when we are performing various types of tablespace operation.
US Undo Segment Enqueue
This enqueue is always acquired in exclusive mode and is used to protect operations made on a specific rollback segment:
When marking a rollback segment as online or offline
When performing instance recovery rollback
When trying to determine the next available rollback segment that we can bind transaction to
When performing a rollback segment shrink
When the XA library XARECOVER call invokes dbms_system.dist_txn_sync()
Document 8268775.8 Bug 8268775 - High US enqueue contention during a login storm or session failover
Document 7291739.8 Bug 7291739 - Contention with auto-tuned undo retention or high TUNED_UNDORETENTION
References
NOTE:8318486.8 - Bug 8318486 - CF enqueue contention while dumping redo for pinned buffer history
NOTE:837883.1 - LOB HWM CONTENTION :Using AWR Reports to Identify the Problem; Confirm and Verify the Fix
NOTE:8477973.8 - Bug 8477973 - Multiple open DB links / ORA-2020 / distributed deadlock / ORA-600 possible using DB Links
NOTE:8499385.8 - Bug 8499385 - Flashback Database may hang/spin holding CF Enqueue causing ORA-494
NOTE:853652.1 - RAC and Sequences
NOTE:8544896.8 - Bug 8544896 - Waits for "enq: RO - fast object reuse" with high DBWR CPU
NOTE:8268775.8 - Bug 8268775 - High US enqueue contention during a login storm or session failover
NOTE:8702276.8 - Bug 8702276 - CF enqueue held for too long with fix 7593835
NOTE:873243.1 - Troubleshooting 'enq: TX - index contention' Waits in a RAC Environment.
NOTE:9071290.8 - Bug 9071290 - ORA-60 "deadlock detected" due to JS enqueue
NOTE:9239623.8 - Bug 9239623 - FLASHBACK EM queries or MMON in "control file sequential read" holding the CF enqueue blocking other sessions
NOTE:948668.1 - Sessions Hang Due to Self Deadlock on TT Enqueue
NOTE:9773415.8 - Bug 9773415 - GTX may deadlock with foreground on DX lock
NOTE:1072417.1 - Performance Degradation as a Result of 'enq: CF - contention'
NOTE:1275884.1 - High CPU by Sessions Holding DX Enqueue; Others Waiting 'enq: DX - contention'
NOTE:1317447.1 - Direct Path Insert - APPEND HINT and TM Enqueue LOCK Behaviour
NOTE:1343365.1 - Increase in TM enqueue in 11g release
NOTE:1349285.1 - High Waits On HW Enqueue due to insert into LOB column
NOTE:1380939.1 - Excessive CF enqueue when performing many parallel NOLOGGING DML
NOTE:15476.1 - FAQ: Detecting and Resolving Locking Conflicts and Ora-00060 errors
NOTE:18251.1 - OERR: ORA 60 "deadlock detected while waiting for resource"
NOTE:186854.1 - TO Lock "Temporary Table Object Enqueue" V8i/9i
NOTE:1065358.6 - HW Lock "HighWater Lock" V8/8i
@ BUG:8499385 - SPINNING INSIDE KCV_FIND_OFFLINE_RANGE()
BUG:8544896 - WAITING FOR 'ENQ: RO - FAST OBJECT REUSE' EVEN AFTER APPLYING 7385253
@ BUG:8702276 - CF ENQUEUE HELD FOR TOO LONG (BUG ENTERED TO FIX REGRESSION IN BLRS FOR 7593835)
BUG:9071290 - ORA-00060: DEADLOCK DETECTED DUE TO JS ENQUEUE
@ BUG:9239623 - STBH: MMON IN CONTROL FILE SEQUENTIAL READ BLOCKING OTHER SESSIONS
BUG:9773415 - ORA-600 [KGHALO4] REPORTED FROM GTX PROCESS
NOTE:1019527.6 - Script. to Check for Foreign Key Locking Issues for a Specific User
NOTE:1020008.6 - SCRIPT. FULLY DECODED LOCKING
NOTE:1020011.6 - SCRIPT. LOW COMPLEXITY LOCKING INFO
NOTE:1020012.6 - Script. to Return Locking Information (Medium Detail)
NOTE:102925.1 - Tracing sessions: waiting on an enqueue
NOTE:6413373.8 - Bug 6413373 - Lob HW enqueue contention in RAC environments with ASSM space management
NOTE:6976005.8 - Bug 6976005 - ORA-494 CF enqueue held for longer than needed with flashback enabled
NOTE:70120.1 - Locking Behavior. During Index Creation or Index Rebuild
NOTE:728539.1 - Find Blocking Sessions In SQLPLUS
NOTE:7291739.8 - Bug 7291739 - Contention with auto-tuned undo retention or high TUNED_UNDORETENTION
NOTE:730423.1 - Select With Local Function and Remote Tables Using a Dblink Hangs Due To Enq DX
NOTE:7385253.8 - Bug 7385253 - Slow Truncate / DBWR uses high CPU / CKPT blocks on RO enqueue
NOTE:740075.1 - 'enq HW - contention' For Busy LOB Segment
NOTE:747071.1 - PHYSICAL: ORA-00494: enqueue [CF] held for too long after Node Crash
NOTE:762085.1 - 'enq: RO - fast object reuse' contention when gathering schema/table statistics in parallel
NOTE:779552.1 - ORA-00494 Or ORA-600 [2103] During High Load After 10.2.0.4 Upgrade
NOTE:223303.1 - Correction to 9.2.0 foreign key constraint locking behaviour, per documentation Bug:2546492.
NOTE:28045.1 - ORA-600 [2103] "Timeout on 'Control file' or 'Checkpoint Progress' Enqueue"
NOTE:29787.1 - VIEW: "V$LOCK" Reference Note
NOTE:33453.1 - REFERENTIAL INTEGRITY AND LOCKING
NOTE:34566.1 - WAITEVENT: "enqueue" Reference Note
NOTE:34664.1 - TM Lock "DML Enqueue" V7/V8/V8i
NOTE:378302.1 - How to Set 'Cache Size' of a Sequence Object?
NOTE:419348.1 - How To Analyze the Wait Statistic: 'enq: HW - contention'
NOTE:432508.1 - High SQ Enqueue Contention with LOBs or Advanced Replication
NOTE:463136.1 - POOR PERFORMANCE DUE TO HUGE WAITS ON US ENQUEUE
NOTE:5177241.8 - Bug 5177241 - Truncate can hang converting RO enqueue SSX to X
NOTE:62354.1 - TX Transaction and Enq: Tx - Row Lock Contention - Example wait scenarios
NOTE:62365.1 - Troubleshooting "ORA-00060 Deadlock Detected" Errors
@ BUG:8268775 - PERF: HIGH US ENQUEUE CONTENTION DURING A LOGIN STORM OR SESSION FAILOVER
BUG:8318486 - CF ENQUEUE CONTENTION WHILE DUMPING REDO RECORDS IN PINNED BUFFER HISTORY
BUG:8477973 - ORA-2020 OCCUR
BUG:2546492 - ROW SHARE LOCK TAKEN ON PARENT TABLE WHEN DML DONE ON FOREIGN KEY COLUMN
BUG:5177241 - TRUNCATE AUD$ HANGS CONVERTING RO ENQUEUE SSX TO X
@ BUG:6413373 - LOB HWM ENQUEUE CONTENTION
@ BUG:6976005 - ORA-494 AND ORA-00600: [KEBM_MMON_MAIN_1], [28] DURING OPEN DATABASE
BUG:7291739 - CONTENTION UNDER AUTO-TUNED UNDO RETENTION
BUG:7385253 - DBWR IS CONSUMING HIGH CPU
NOTE:12637294.8 - Bug 12637294 - Deadlock of PS and BF locks during parallel query operations
@NOTE:286363.1 - Truncate Takes A Long Time -- Waits on RO enqueue
![Master Note: Locks, Enqueues and Deadlocks [ID 1392319.1] _Locks](https://support.oracle.com/epmos/adf/p_w_picpaths/t.gif){kind=small}
