Master Note: Locks, Enqueues and Deadlocks [ID 1392319.1]

rongshiyuan發表於2012-09-07
ASTENQ
Master Note: Locks, Enqueues and Deadlocks [ID 1392319.1]
 
In this Document

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:

Document 34566.1 "enqueue" Reference Note
Document 29787.1 Details of V$LOCK view and lock modes


A complete list of enqueues can be found at by querying V$LOCK_TYPE:

select substr(type,1,2),substr(name,1,30),substr(description,1,40)
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 34664.1 TM Lock "DML Enqueue" V7/V8/V8i
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

Document 873243.1 Troubleshooting 'enq: TX - index contention' Waits in a RAC Environment.

Diagnostics

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

Document 102925.1 Tracing sessions: waiting on an enqueue
Document 15476.1 FAQ: Detecting and Resolving Locking Conflicts and Ora-00060 errors

Scripts to help Diagnose Locking Issues

Document 1020008.6 SCRIPT. FULLY DECODED LOCKING
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 18251.1 OERR: ORA 60 "deadlock detected while waiting for resource"
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

Document 12637294.8 Bug 12637294 Deadlock of PS and BF locks during parallel query operations

CF Controlfile Enqueue

CF enqueue serializes controlfile transactions, reads and writes on shared portions of the controlfile.

General

Document 28045.1 ORA-600 <2103> "Timeout on 'Control file' or 'Checkpoint Progress' Enqueue"
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 9239623.8 Bug 9239623 - FLASHBACK EM queries or MMON in "control file sequential read" holding the CF enqueue blocking other sessions
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

Document 747071.1 PHYSICAL: ORA-00494: enqueue held for too long after Node Crash

DX Distributed Transaction Enqueue

DX enqueue is used to protect distributed transactions.

Document 1275884.1 High CPU by Sessions Holding DX Enqueue; Others Waiting 'enq: DX - contention'
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 1065358.6 HW Lock "HighWater Lock" V8/8i
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

Document 9071290.8 Bug 9071290 - ORA-60 "deadlock detected" due to JS 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 762085.1 'enq: RO - fast object reuse' contention when gathering schema/table statistics in parallel


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 853652.1 RAC and 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

Document 186854.1 TO Lock "Temporary Table Object Enqueue" V8i/9i

TT Tablespace Operations Enqueue

This enqueue is used to avoid deadlocks when we are performing various types of tablespace operation.

Document 948668.1 Sessions Hang Due to Self Deadlock on TT Enqueue

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 463136.1 POOR PERFORMANCE DUE TO HUGE WAITS ON US ENQUEUE

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

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

相關文章