Skip to main content
Delphix

Resolving ORA-00060 Error: deadlock detected while waiting for resource (KBA1853)

 

 

Issue

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.

Troubleshooting

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

 

Resolution

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. 

External Links

Referenced articles are: