ora 00060 deadlock detected while waiting for resource

#Ora 00060 deadlock detected while waiting for resource code

Why is ora-00060 deadlock detected while waiting for resource? Select the session id and kill the process which is holding the lock on schema user. Below query will give you the lock details.

You can check table lock from v$lock and dba_objects view. How do I check if an object is locked in SQL Developer? Shared resources such as read-only files do not lead to deadlocks.ĭeadlock frequency can sometimes be reduced by ensuring that all applications access their common data in the same order – meaning, for example, that they access (and therefore lock) rows in Table A, followed by Table B, followed by Table C, and so on. How can we avoid deadlock?ĭeadlocks can be prevented by preventing at least one of the four required conditions: The third way to overcome the deadlock situation is by killing one or more processes. The second way is to roll back, as the operating system keeps a record of the process state and it can easily make a process roll back to its previous state due to which deadlock situation can be easily eliminate. It is a deadlock due to user error in the design of an application or from issuing incorrect ad-hoc SQL.” How can deadlock be removed? Poor application design – As noted in the trace file: “The following deadlock is not an ORACLE error. Too high activity – Re-running the job during a less busy period can fix this ORA-00060 deadlock error. How do I fix Ora 00060 deadlock detected? Add INITRANS – In certain conditions, increasing INITRANS for the target tables and indexes(adding slots to the ITL) can relieve deadlocks.Tune the application – Single-threading related updates and other application changes can often remove deadlocks.Identify the SQL statements in both the current session and the waiting session(s).

Locate the error messages in the alert log.

#Ora 00060 deadlock detected while waiting for resource code

In summary, the steps necessary to identify and rectify code causing deadlocks are: How do you check if there is a deadlock in Oracle? The error specifically means that we attempted to write (a masked value) and Oracle encountered a deadlock situation which caused it to rollback the existing transaction. SQL> ALTER INDEX ptmytidx2 REBUILD INITRANS 8 įor increasing the size on the payments table, alter the table.What is Ora 00060 deadlock detected while waiting for resource? SQL> ALTER INDEX paymt_pk REBUILD INITRANS 8 To increase the initrans value for the indexes, the user rebuilds the index and specifies the new value. To resolve the deadlocking error, the user must re-create the payments table indexes or table and increase the value for the number of initial transaction slots in the objects block's header.įor 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 payments table or on one of its two indexes: paymt_pk or ptmytidx2. Can read from Ī trace file is generated for the offending session in diag destination. In a database, a deadlock is a situation in which two or more transactions are waiting for one another to give up locks. ORA-00060: deadlock detected while waiting for resource