How to Troubleshoot Deadlock Detected Error within censhare

This document will show how you analyse a constraint error step by step (Oracle).

Background

Deadlocks in Oracle are not always attributed to the censhare Application Logic.

Oracle deadlocks lead to a trace file containing more information about the deadlock. Trace files are located in the directory specified by the USER_DUMP_DEST init parameter. This can be found with:

CODE

sqlplus> show parameters

The essential in the trace file is:

CODE

Rows waited on:
Session 174: obj - rowid = 00000000 - D ///// AADAAABkTAAA
(dictionary objn - 0, file - 3, block - 6419, slot - 0)
Session 68: obj - rowid = 00000000 - D ///// AADAAABkTAAA
(dictionary objn - 0, file - 3, block - 6419, slot - 0)

If it were an object:

TIP

Mac Calculator Application - Representation "programmer"

If it is not an object (as in the example above):

CODE

set linesize 1000
set pagesize 1000
SELECT tablespace_name, segment_type, owner, segment_name FROM dba_extents de WHERE file_id = 3 <--- file = 3 and 6419 between block_id AND block_id + blocks - 1;   <--- block = 6419

Sometimes it shows only "no row" - then this doesn't mean a typical deadlock.

CODE

Rows waited on:
Session 174: obj - rowid = 00000000 - D ///// AADAAABkTAAA
(dictionary objn - 0, file - 3, block - 6419, slot - 0)
Session 45: no row

Possible causes in this case

There are many possible causes for deadlock. As a general rule, we first check for Missing Foreign Key Indexes, then for high "ITL waits" and if the problem can not be found, we finally follow this article and try to compare the Deadlock Graph with one of the examples there

Missing Foreign Key Indexes

This is checked with the Admin Client "Check Foreign Key Indexes"

Insufficient ITL slots

This can usually be seen via the following query:

CODE

set linesize 1000
set pagesize 1000
SELECT t.OWNER, t.OBJECT_NAME, t.OBJECT_TYPE, t.STATISTIC_NAME, t.VALUE FROM 
v $ segment_statistics t WHERE t.STATISTIC_NAME = 'ITL waits' AND t.VALUE> 0;

OWNER OBJECT_NAME OBJECT_TYPE STATISTIC_NAME VALUE
-------------- ---------------------- -------------- ----------------------- ----------
CORPUS ASSET TABLE ITL waits 90

Then you should increase the number of slots - costs space in the DB blocks, but also increases the performance, since it comes to fewer waits.

Example for increasing ITL slots for a table:

`ALTER TABLE asset INITRANS 3;Z`

Example for increasing ITL slots for an index:

`ALTER INDEX asset_idx2 INITRANS 4;`

Reference tickets: 2816281,