Ora-00060 The Following Deadlock Is Not An Oracle Error
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 ora-00060 deadlock detected while waiting for resource oracle Burleson Blog
oracle deadlock resolution Fixing ORA-00060: deadlock detected while waiting for resource Oracle Database Tips by Burleson Consulting November 24, 2015 Question: ora 00060 solution I am getting this ORA-00060: deadlock detected while waiting for resource during a large update, against two separate tables. What I don't understand is why a deadlock can occur when havingHow To Check Deadlock In Oracle
an update on two different tables, but I expect that the deadlock is ITL related ? java.sql.SQLException: ORA-00060: deadlock detected while waiting for resource at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:112) DEADLOCK DETECTED Current SQL statement for this session: UPDATE DBRT.DBRT_REALOP_DC_CYCLICS SET ... WHERE DBRT_TROD_PK = :43 The following deadlock is not an ORACLE error. It is a deadlock due to user error in the design of ora 00060 deadlock detected more info in file an applicationor from issuing incorrect ad-hoc SQL. The following information may aid in determining the deadlock: Deadlock graph: ---------Blocker(s)-------- ---------Waiter(s)--------- Resource Name process session holds waits process session holds waitsTX-0007000a-00019a9f 42 44 X 28 39 STX-001a0017-0000896d 28 39 X 42 44 Ssession 44: DID 0001-002A-00028438 session 39: DID 0001-001C-00004319 session 39: DID 0001-001C-00004319 session 44: DID 0001-002A-00028438Rows waited on:Session 39: obj - rowid = 0003559F - AAA1WfAB3AAAAAAAAA (dictionary objn - 218527, file - 119, block - 0, slot - 0) Session 44: obj - rowid = 0003DFE5 - AAA9/lADDAAAAAAAAA (dictionary objn - 253925, file - 195, block - 0, slot - 0) Answer: Whenever you have competing DML running against the same data, you run the risk of a deadlock. This deadlock condition is an age-old issue known as the perpetual embrace. This infinite loop is caused by either an application or bad ad hoc SQL, but Oracle is clever enough to recognize it and throw the ORA-00060 rather than continuing the deadlock situation. Using the above dump, you can see the objects that were locked, one in file 119 (ROWID=0003559F) and file 1
SQL TuningSecurityOracle UNIXOracle LinuxMonitoringRemote supportRemote plansRemote servicesApplication Server ApplicationsOracle FormsOracle PortalApp UpgradesSQL ServerOracle ConceptsSoftware SupportRemote Support Development Implementation Consulting how to remove deadlock in oracle StaffConsulting PricesHelp Wanted! Oracle PostersOracle Books Oracle Scripts Ion
How To Check Deadlock In Oracle 11g
Excel-DB Don Burleson Blog
Oracle Deadlock Detection Script
Oracle deadlocks tips Oracle Database Tips by Burleson Consulting What is an Oracle deadlock? Whenever you have competing DML running against http://www.dba-oracle.com/t_ora_00060_deadlock_detected_while_waiting_for_resource.htm the same data, you run the risk of a deadlock. This deadlock condition is an age-old issue known as the "perpetual embrace"! The doc note that a retry may work: ORA-00060: deadlock detected while waiting for resource Cause: Transactions deadlocked one another while waiting for resources. Action: Look at http://www.dba-oracle.com/t_deadlock.htm the trace file to see the transactions and resources involved. Retry if necessary. Deadlocks in Oracle result in this error: ORA-00060: deadlock detected while waiting for resource If your ORA-00060 is caused by competing resources, the perpetual embrace happens when the aborted task (Task B) attempts to lock a row which is being held by another task (Task A), which, in-turn, is waiting for task B to release a lock. To prevent a perpetual wait, Oracle aborts the transaction that caused the deadlock. See my notes here on resolving the deadlock detected error. Resolving Oracle deadlocks There are several remedies for resolving aborted tasks from deadlocks: Tune the application - Single-threading related updates and other application changes can often remove deadlocks. Re-scheduling batch update jobs to low-update times an also help. Add INITRANS - In certain conditions, increasing INITRANS for the target tables and indexes(adding s
here for a quick overview of the site Help Center Detailed answers to any questions you might have Meta Discuss the workings and policies of this site About http://stackoverflow.com/questions/17358088/finding-cause-of-deadlock-error-from-oracle-trace-file Us Learn more about Stack Overflow the company Business Learn more about hiring https://wiki.scn.sap.com/wiki/pages/viewpage.action?pageId=414089456 developers or posting ads with us Stack Overflow Questions Jobs Documentation Tags Users Badges Ask Question x Dismiss Join the Stack Overflow Community Stack Overflow is a community of 6.2 million programmers, just like you, helping each other. Join them; it only takes a minute: Sign up Finding cause of deadlock error from how to oracle trace file up vote 11 down vote favorite 8 I have been getting this "ora-00060 deadlock detected while waiting for resource" error often now in my application when multiple users are using the application. I have got the trace file from the oracle Admin, but need help in reading it. Below is bits of data from the trace file, which i hope would help in deadlock in oracle locating the cause. *** 2013-06-25 09:37:35.324 DEADLOCK DETECTED ( ORA-00060 ) [Transaction Deadlock] The following deadlock is not an ORACLE error. It is a deadlock due to user error in the design of an application or from issuing incorrect ad-hoc SQL. The following information may aid in determining the deadlock: Deadlock graph: ---------Blocker(s)-------- ---------Waiter(s)--------- Resource Name process session holds waits process session holds waits TM-000151a2-00000000 210 72 SX SSX 208 24 SX SSX TM-000151a2-00000000 208 24 SX SSX 210 72 SX SSX session 72: DID 0001-00D2-000000C6 session 24: DID 0001-00D0-00000043 session 24: DID 0001-00D0-00000043 session 72: DID 0001-00D2-000000C6 Rows waited on: Session 72: no row Session 24: no row ----- Information for the OTHER waiting sessions ----- Session 24: sid: 24 ser: 45245 audsid: 31660323 user: 90/USER flags: (0x45) USR/- flags_idl: (0x1) BSY/-/-/-/-/- flags2: (0x40009) -/-/INC pid: 208 O/S info: user: zgrid, term: UNKNOWN, ospid: 2439 image: oracle@xyz.local client details: O/S info: user: , term: , ospid: 1234 machine: xyz.local program: current SQL: delete from EMPLOYEE where EMP_ID=:1 ----- End of information for the OTHER waiting sessions ----- Information for THIS session: ----- Current SQL Statement for this session (sql_id=dyfg1wd8xa9qt) ----- delete from EMPLOYEE where EMP_ID=:1 =============