Resolving ORA-00060 Error: deadlock detected while waiting for resource (KBA1853)
While masking an Oracle database, you can sometimes encounter an ORA-00060 error which will cause your masking job to fail. This is not common, but certainly possible; particularly with older versions of 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.
In the Delphix Masking logs you will see an error like:
[JOB_ID_41_486] Error updating batch [JOB_ID_41_486] ORA-00060: deadlock detected while waiting for resource
In the database alert log you can expect to see messages similar to:
Fri Feb 08 02:25:28 2019 ORA-00060: deadlock resolved; details in file /u01/oracle/diag/rdbms/cvclms/Cvclms/trace/Cvclms_ora_169403.trc
In the trace file, you can see some indication with messages like:
DEADLOCK DETECTED (id=0xbeebf155) Chain Signature: 'enq: TX - contention'<='row cache lock' (cycle) Chain Signature Hash: 0x84e96288 The following deadlock is not an Oracle error. Deadlocks of this type can be expected if certain SQL statements are issued. The following information may aid in determining the cause of the deadlock. Resolving deadlock by signaling ORA-00060 to 'instance: 1, os id: 169403, session id: 1324' dump location: /u01/oracle/diag/rdbms/cvclms/Cvclms/trace/Cvclms_ora_169403.trc
Keep in mind that there are a number of ways to entice an ORA-00060 error and the above example log information represents only one of those ways. The way to resolve this issue depends on exactly what circumstances generated the error. Oracle has some advice on how to determine what kind of deadlock occurred in their article:
How to Identify ORA-00060 Deadlock Types Using Deadlock Graphs in Trace (Doc ID 1507093.1)
This error happens because of lock contention while trying to obtain write locks to specific data in a table. With Delphix Masking, this generally relates to the use of the "Threads" masking job configuration parameter. When you increase threads beyond 1, there can be multiple things writing a given table at the same time. Also, using ROWID as a Logical Key in the Rule Set will alleviate the issue here.
As a general rule, Oracle handles multiple writers fairly well, but there are some scenarios where this will break down. How to resolve these issues starts with what kind of deadlock occurred. Once you have determined the kind of deadlock, then you can proceed to resolve that specific issue.
Possibly you are seeing the error
ORA-00060: deadlock detected due to restriction in the number of available Initial Transaction Slots (ITLs).
- This value is controlled by an Oracle parameter INITRANS.
- From Oracle INTRANS tips, here are some notes:
- "The maximum value suggested for INITRANS is 100 and settings over this size rarely improve performance.".
- The article mentions a script for measuring ITL or row lock waits.
- Advice on troubleshooting can be found in the referenced external page below.
Be aware this symptom can be caused by factors other than INITRANS, eg. using Bitmap Indexing. Please see referenced external pages in the below section.
Referenced articles are:
- How to Identify ORA-00060 Deadlock Types Using Deadlock Graphs in Trace (Doc ID 1507093.1) (Oracle Login needed)
- Oracle INITRANS tips
- Trouble understanding INITRANS in oracle
- INITRANS Cause of deadlock
- DBMS | Bitmap Indexing