Error Ora-04020 Deadlock Detected While Trying To Lock Object
Contents |
SQL TuningSecurityOracle UNIXOracle LinuxMonitoringRemote supportRemote plansRemote servicesApplication Server ApplicationsOracle FormsOracle PortalApp UpgradesSQL ServerOracle ConceptsSoftware SupportRemote Support Development Implementation Consulting StaffConsulting PricesHelp Wanted! Oracle PostersOracle Books Oracle Scripts Ion Excel-DB Don Burleson Blog ora-04024: self-deadlock detected while trying to mutex pin cursor
ORA-04020: deadlock detected whileOra-04020 Deadlock Detected While Trying To Lock Object Materialized View
trying to lock object tips Oracle Error Tips There are also many things to consider when the ORA-04020 is associated
Oracle Ora-04024
with a deadlock between concurrent sessions or within a single session that deadlocks itself. The most obvious workaround for any ORA-040020 is to serialize the processing to perform the tasks one-at-a-time so that
Ora 04020 Deadlock Detected While Trying To Lock Object Table
they don't conflict with each other. Also see these notes on the concept of the perpetual embrace. ORA-4020 did occur in following situations: 1. Concurrently analyzing different partitions of the same base object can fail with an ORA-4020 "deadlock detected". 2. Creating different materialized views against the same base table may result in a ORA-4020 deadlock if executed concurrently (see MOSC Note 103077.1). 3. You can receive ora-04021 an ORA-4020 when jobs insert into a partitioned IOT and another job concurrently tries to move one of the partitions. 4. You run utlrp.sql and get the ORA-04020 error with dbms_standard. The Oracle oerr utility book note this on the ORA-04020 error: ORA-04020 deadlock detected while trying to lock object Cause: While trying to lock a library object, a deadlock is detected. Action: Retry the operation later. MOSC provides a very good rundown of ORA-04020, and more specifically, things that commonly cause deadlock, "this article explains the most common scenarios for ORA-04020 Deadlocks and how to avoid them." There are two types of deadlocks: We can distinguish two types of deadlocks: 1. Self Deadlocks It occur when one session tries to get a lock on a resource that he already has in some way. Normally, the Oracle engine should detect those situations and should avoid the signaling of the ORA-04020 to the end-users. When a self deadlock detection occurs, Oracle generates a trace file in the user_dump_dest. It is only considered as a bug if an ORA-04020 is signaled to the end-user. 2. Deadlocks between concurrent sessions The ORA-04020 deadlock error usually occurs when two user processes c
detected while trying to lock object... From:
Ask a Question Need help? Post your question and get tips & solutions from a community of 418,538 IT Pros https://bytes.com/topic/oracle/answers/680424-ora-04020-deadlock-while-issuing-revoke-privilege-public & Developers. It's quick & easy. ora-04020 deadlock while issuing revoke privilege from public P: 3 raccoon keep getting an ora-04020: deadlock detected while trying to lock object XDB. XDNDBKDJjilnd The statement I'm trying to issue is: REVOKE execute on abc."descript_T" FROM PUBLIC; I am not able to find a solution for this besides the fact deadlock detected of just try the operation again at a later time. I did try it again and again throughout the day, but get the same error every single time. There's nothing in the alert log, but there is user dump file generated every single time. It looks like the following: The dump file is similar to the following: deadlock detected while >>A deadlock among DDL and parse locks is detected. This deadlock is usually due to user errors in the design of an application or from issuing a set of concurrent statements which can cause a deadlock. .... ORA-04020: deadlock detected while trying to lock object XDB.XDNDBKDJjilnd -------------------------------------------------------- object waiting waiting blocking blocking handle session lock mode session lock mode -------- -------- -------- ---- -------- -------- ---- 4269c48 26535dc 2ae1d5c X 26535dc 2ae2428 S -------------------------------------------------------- ---------- DUMP OF WAITING AND BLOCKING LOCKS ---------- -------------------------------------------------------- ------------- WAITING LOCK ------------- ---------------------------------------- SO: 2ae1d5c, type: 35, owner: 29a8ed8, flag: INIT/-/-/0x00 LIBRARY OBJECT LOCK: lock=2ae1d5c handle=4269c48 request=X call pin=0 session pin=0 user=26535dc session=2652d70 count=0 flags=[00] savepoint=0 LIBRARY OBJECT HANDLE: handle=4269c48 name=XDB.XDNDBKDJjilnd hash=93440965 timestamp=12-02-2001 10:22:49 How do you resolve this ? I'd appreciate any help. Jul 19 '07 #1 Post Reply Share this Question 4 Replies Expert 5K+ P: 8,127 debasisdas An ORA-4020 occurs when a session tries to lock a library object and a deadlock is detected. It mostly occur during the co