Is This Content Helpful?
We're glad to know this article was helpful.
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.
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;
SQL> ALTER TABLE state_lineages INITRANS 8;