Error: ORA-00060: deadlock detected while waiting for resource
Oracle deadlocks occur when one or more sessions attempt to acquire 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 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 "ORA-00060: deadlock detected while waiting for resource".
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 the tables and indexes used by versioning. The initrans value is controlled in the dbtune.sde file under the keyword DATA_DICTIONARY, but is only referenced when the instance is initially created.
To increase the initrans value for the indexes, the SDE user rebuilds the index and specifies the new value.
SQL> ALTER INDEX lineages_pk REBUILD INITRANS 8;
SQL> ALTER INDEX lineages_id_idx2 REBUILD INITRANS 8;
For increasing the size on the state_lineages table, alter the table.
SQL> ALTER TABLE state_lineages INITRANS 8;
The limitation with the table is that the alter does not change existing data blocks, but only when new blocks are added as the table grows larger. If the deadlock is occuring on the table and altering the table does not solve the problem, then the table itself will have to be exported, dropped, and recreated. It will also require setting a larger initrans value and having the rows imported back into the new table.
An initrans value of 8 more be sufficient, but for larger organizations with lots of concurrent editors, a value of 8 still may not suffice. The Oracle DBA should monitor the instance and search for the frequency of blocking conditions waiting for ITL slots. If it is a common wait event, then the value should further be increased. For instructions on how to monitor blocking conditions, see Oracle's documentation.