Ora Error Stack 60
Contents |
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 Us
Ora 00060 Solution
Learn more about Stack Overflow the company Business Learn more about hiring developers ora-00060 deadlock detected while waiting for resource oracle 11g or posting ads with us Stack Overflow Questions Jobs Documentation Tags Users Badges Ask Question x Dismiss Join the Stack
Ora 00060 Deadlock Detected More Info In File
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 ORA-00060: deadlock detected while waiting for resource oracle deadlock resolution up vote 14 down vote favorite 5 I have a series of scripts running in parallel as a nohup on an AIX server hosting oracle 10g. These scripts are written by somebody else and are meant to be executed concurrently. All the scripts are performing updates on a table. I am getting the error, ORA-00060: deadlock detected while waiting for resource As I googled for this, I how to check deadlock in oracle found, http://www.dba-oracle.com/t_deadly_perpetual_embrace_locks.htm Even though the scripts are performing updation on the same table concurrently, they are performing updates on different records of the table determined by the WHERE clause with no overlaps of records between them. So would this have caused the error?. Will this error happen regardless of where the updates are performed on a table?. Should I avoid concurrent updates on a table at all times?. Strangely I also found on the nohup.out log, PL/SQL successfully completed after the above quoted error. Does this mean that oracle has recovered from the deadlock and completed the updates successfully or Should I rerun those scripts serially? Any help would be welcome. Thanks in advance. database oracle unix plsql aix share|improve this question edited Dec 15 '14 at 21:20 Lajos Veres 10.8k72446 asked Jun 19 '10 at 8:00 wowrt 242238 add a comment| 3 Answers 3 active oldest votes up vote 7 down vote accepted You can get deadlocks on more than just row locks, e.g. see this. The scripts may be competing for other resources, such as index blocks. I've gotten around this in the past by engineering the parallelism in such a way that different instances are
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
Ora-00060 Trace File
Excel-DB Don Burleson Blog
ora-00060 deadlock detected while waiting for resource in informatica Oracle parallel DML: Deadlock Detected:ORA-00060 Oracle Database Tips by Burleson ConsultingAttempting To Break Deadlock By Signaling Ora-00060
Question: I'm having trouble running a parallel DML statement. Here is the code for Parallel DML, I'm running only one session which is current. I don't know how comes http://stackoverflow.com/questions/3074827/ora-00060-deadlock-detected-while-waiting-for-resource I got an error saying DEADLOCK DETECTED: SQL> alter session enable parallel dml; Session altered. SQL> SQL> alter session set db_file_multiblock_read_count=128; Session altered. SQL> set timin on SQL> SQL> SQL> UPDATE /*+ full(myobjects) parallel(myobjects, 8)*/ myobjects 2SET object_name = upper(object_name); update /*+ full(myobjects) parallel(myobjects, 8)*/ * ERROR at line 1: ORA-12801: error signaled in parallel query server P015 ORA-00060: http://www.dba-oracle.com/t_parallel_dml_deadlock_detected.htm deadlock detected while waiting for resource Answer:It's one of two issues: Deadly embrace - Competing DML tasks cause perpetual deadlock ITL shortage - More ITL's needed for parallel DML ORA-00060 and Competing Updates Do you have competing updates running? This may be an age-old issue known as the perpetual embrace! The docs 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 the trace file to see the transactions and resources involved. Retry if necessary. 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.ORA-00060 and INITRANS Shortage There can also be a ORA-00060 "deadlock detected" where the table and index INITRANS is set too low. The "Interested Transaction List" and dead
to the resource contention with other session. Oracles itself does a rollback on your current session and resolve the error. Other session can proceed as usual. The alert.log will record the error with all details. Current session http://www.acehints.com/2011/06/troubleshoot-fix-ora-00060-deadlock.html will be rolled back and need to resubmitted once the required resources are available. Deadlock detected while waiting for resource. In most of the cases the deadlock are caused by application errors. Dead lock Dead lock happens when a session (sess1) wants resource locked by another session (sess2), But that session also wants the resource which is locked by sess1. These can be happened between more than two sessions also with same deadlock detected kind of scenario. Dead Lock Example create table temp ( num number, txt varchar2(10) ); insert into temp values ( 1, 'First' ); insert into temp values ( 2, 'Second' ); commit; select rowid, num, txt from temp; ROWID NUM TXT ------------------ ---------- ---------- AAAAv2AAEAAAAqKABC 1 First AAAAv2AAEAAAAqKABD 2 Second Sess1: update temp set txt='ses1' where num=1; Sess2: update temp set txt='ses2' 00060 deadlock detected where num=2; update temp set txt='ses2' where num=1; > Sess2 is now waiting for the TX lock held by Sess1 Sess1: update temp set txt='sess1' where num=2; This update would cause Sess1 to wait on the TX lock held by Sess2, but Sess2 is already waiting on this session. This causes a deadlock scenario so one of the sessions signals an ORA-60. Sess2: ORA-60 error Sess1: Still blocked until Sess2 completes the transaction (either commits or rolls back) as ORA-60 only rolls back the current statement and not the entire transaction. How to check the error details Ora-00060 error normally records the details in alert.log and also in the trace file. The trace file will be created in USER_DUMP_DEST and sometimes in background_dump_dest. Trace file will contain a deadlock graph and additional information. ----------------------------------------------------------------------- DEADLOCK DETECTED Current SQL statement for this session: update temp set txt='ses2' where num=1 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 proce