---

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:

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:

RAC Specific

Diagnostics

There are a number of general diagnostic articles and scripts available to assist with the resolution of locking issues:

Scripts to help Diagnose Locking Issues

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.

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

Flashback Related

Dataguard Related

DX Distributed Transaction Enqueue

DX enqueue is used to protect distributed transactions.

HW High Water Enqueue

The HW enqueue is used to serialize the allocation of space beyond the high water mark of a segment.

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.

SQ Sequence Cache Enqueue

Serializes access to Oracle sequences.

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()

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

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/17252115/viewspace-742902/，如需轉載，請註明出處，否則將追究法律責任。