Deadlock Detected Oracle Error
Contents |
January 15, 2014 - 9:12 pm UTC Category: Database – Version: 11.2 Latest Followup You Asked *** 2011-09-20 14:29:09.745 DEADLOCK DETECTED ( ORA-00060 ) [Transaction Deadlock] The following deadlock is not an ORACLE error. It is a oracle deadlock detected while waiting for resource deadlock due to user error in the design of an application or from issuing
Ora-00060 Deadlock Detected In Oracle 11g
incorrect ad-hoc SQL. The following information may aid in determining the deadlock: Deadlock graph: ---------Blocker(s)-------- ---------Waiter(s)--------- Resource Name process session holds query to detect deadlock in oracle waits process session holds waits TM-0000f4bc-00000000 42 575 SX SSX 48 5 SX SSX TM-0000f4bc-00000000 48 5 SX SSX 42 575 SX SSX session 575: DID 0001-002A-000002F0 session 5: DID 0001-0030-00000831 session 5: DID 0001-0030-00000831 oracle deadlock resolution session 575: DID 0001-002A-000002F0 Rows waited on: Session 575: no row Session 5: obj - rowid = 0000F38C - AAAPOMACrAABodTAAA (dictionary objn - 62348, file - 171, block - 427859, slot - 0) ----- Information for the OTHER waiting sessions ----- Session 5: sid: 5 ser: 13809 audsid: 4295982 user: 63/MI520SAASPERFT1_APP flags: 0x41 pid: 48 O/S info: user: oracle, term: UNKNOWN, ospid: 14422 image: oracle@aedbperf01 client details: O/S info: user:
Ora-00060 Deadlock Detected While Waiting For Resource In Oracle
Administrator, term: AEBL009, ospid: 1680:2480 machine: DIUS\AEBL009 program: DrteFrameworkServer.exe application name: DrteFrameworkServer.exe, hash value=3560566261 current SQL: DELETE FROM event WHERE event_id = &0 ========================= Tom, This is my first ever question on this website. I have heard a lot about u. We are getting deadlock when running our benchmark on table EVENT. Table EVENT has 9 FK constraints and no indexes on FK constraints. What should I look to get more information regarding: a) What is causing deadlocks? It is being no indexes on FK or INITTRANS. Table has default settings: PCTFREE 10 INITRANS 1 MAXTRANS 255 b) We noticed that deadlocks happen mostly during start of load and not towards the end c) will it still cause a deadlock if there are no rows in those referenced tables for the event that is being deleted? d) I ran a script to identify the missing FK indexes: =================== Changing data in table PERIOD will lock tableEVENT Create an index on table EVENT with the following columns to remove lock problem Column = PERIOD_ID (1) Changing data in table ROUTE will lock tableEVENT Create an index on table EVENT with the following columns to remove lock problem Column = ROUTE_ID (1) Changing data in table CALL_PLAN_TEMPLATE will lock table EVE
Early Adopter Program ArcGIS Ideas Esri Support Services ArcGIS Blogs ArcGIS Code Sharing Product Life Cycles Manage Cases Request Case Start Chat Back to results Print Share Is This ora 00060 deadlock detected more info in file Content Helpful? Search on GeoNet Submit to ArcGIS Ideas Error: ORA-00060: deadlock how to check deadlock in oracle detected while waiting for resource Error Message Oracle deadlocks occur when one or more sessions attempt to acquire
Ora 00060 Solution
a resource to a database object that is currently being held by a second session, which is also attempting to acquire a resource held by the first session. To https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:3932525800346405986 break the deadlock, Oracle releases the resource being held by one session and returns an error to allow the other session to proceed. In doing so, a trace file for the offending session is created in a directory based on the value for the user_dump_dest initialization parameter.While compressing a versioned geodatabase, the compress command may potentially encounter the Oracle error http://support.esri.com/technical-article/000010657 "ORA-00060: deadlock detected while waiting for resource". Cause If encountering the Oracle error during a compress, the deadlock being encountered is not the customary type of deadlock where two sessions are competing/blocking requests for the same row objects to update or delete, but blocking for the ITL slots in a segment's block header.For a very detailed description of the issue, review Oracle Metalink document 62354.1 "TX Transaction locks - Example wait scenarios" under the section, "Waits due to Insufficient 'ITL' slots in a Block".The blocking condition being encountered is likely to be on either the state_lineages table or on one of its two indexes: lineages_pk or lineage__id_idx2.The error typically only manifests itself when a compress is executed while a high number of users are concurrently editing the versioned geodatabase. Solution or Workaround To resolve the deadlocking error, the SDE user must re-create the state_lineage table indexes or table and increase the value for the number of initial transaction slots in the objects block's header.By default, ArcGIS sets the initrans value to 4 when creating all t
Social Links Printer Friendly About Search 8i | 9i | 10g | 11g | 12c | 13c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux Home » Articles » Misc » Here Deadlocks A deadlock https://oracle-base.com/articles/misc/deadlocks occurs when two or more sessions are waiting for data locked by each other, resulting http://oracle-error.blogspot.com/2008/10/ora-00060-deadlock-detected-while_20.html in all the sessions being blocked. Oracle automatically detects and resolves deadlocks by rolling back the statement associated with the transaction that detects the deadlock. Typically, deadlocks are caused by poorly implemented locking in application code. This article shows the steps necessary to identify the offending application code when a deadlock is detected. Create a test user. CREATE USER deadlock detected test IDENTIFIED BY test DEFAULT TABLESPACE users TEMPORARY TABLESPACE temp QUOTA UNLIMITED ON users; GRANT CONNECT, CREATE TABLE TO test; GRANT EXECUTE ON DBMS_LOCK TO test; Create a test schema. CONN test/test CREATE TABLE deadlock_1 ( id NUMBER ); CREATE TABLE deadlock_2 ( id NUMBER ); INSERT INTO deadlock_1 (id) VALUES (1); INSERT INTO deadlock_2 (id) VALUES (1); COMMIT; Start two SQL*Plus sessions, each logged into the test user, then run the following pieces 00060 deadlock detected of code, one in each session. -- Run in session 1. DECLARE l_deadlock_1_id deadlock_1.id%TYPE; l_deadlock_2_id deadlock_2.id%TYPE; BEGIN -- Lock row in first table. SELECT id INTO l_deadlock_1_id FROM deadlock_1 WHERE id = 1 FOR UPDATE; -- Pause. DBMS_LOCK.sleep(30); -- Lock row in second table. SELECT id INTO l_deadlock_2_id FROM deadlock_2 WHERE id = 1 FOR UPDATE; -- Release locks. ROLLBACK; END; / -- Run in session 2. DECLARE l_deadlock_1_id deadlock_1.id%TYPE; l_deadlock_2_id deadlock_2.id%TYPE; BEGIN -- Lock row in second table. SELECT id INTO l_deadlock_2_id FROM deadlock_2 WHERE id = 1 FOR UPDATE; -- Pause. DBMS_LOCK.sleep(30); -- Lock row in first table. SELECT id INTO l_deadlock_1_id FROM deadlock_1 WHERE id = 1 FOR UPDATE; -- Release locks. ROLLBACK; END; / The first piece of code gets a lock on a row in the DEADLOCK_1 table, it pauses for 30 seconds, then attempts to get a lock on a row in the DEADLOCK_2 table. The second piece of code does the same thing but in reverse, locking a row in the DEADLOCK_2 table, then the DEADLOCK_1 table. The call to the DBMS_LOCK.SLEEP procedure is only present to give you enough time to switch sessions. Eventually, one of the sessions will detect the deadlock, rollback its transaction and produce a deadlock error, while the other transaction completes successfully. A typical deadlock error is display
deadlock detected while waiting for resourceCause: Transactions deadlocked one another while waiting for resources.Action: Look at the trace file to see the transactions and resources involved. Retry if necessary.ORA-00060 error indicates that a dead lock occurred due to a resource contention with another session and Oracle rolled back your current statement to resolve the dead lock. The other session can proceed further as usual . Your current sessions rolled backed statements needs to be resubmitted for the execution after the required resources are available.These dead locks can occur in different scenarios: They can occur while doing parallel DML operations, while updating/deleting data from same tables from different sessions , while performing transactions on bitmap index tables and so on but the mentioned scenarios are the most common ones.At this point I think we would like to explore more about dead locks, so here are the in depth details on dead locks:What is dead lock, when it occurs and how to fix it?Dead lock occurs when two or more users waiting for the resources locked by each other. The users are stuck and they can not proceed as they may end up waiting indefinitely for the resources form each other. So when this condition occurs these users are stuck (deadly embraced) and cannot continue processing.Oracle automatically detects deadlocks and resolves them by rolling back one of the transactions/statements involved in the deadlock, thus releasing one set of resources/data locked by that transaction. The session that is rolled back will observe Oracle error: ORA-00060: deadlock detected while waiting for resource. Oracle will also produce detailed information in a trace file under database's UDUMP directory.Most commonly these deadlocks are caused by the applications that involve multi table updates in the same transaction and multiple applications/transactions are acting on the same table at the same time. These multi-table deadlocks can be avoided by locking tables in same order in all applications/transac