Tuesday, 3 September 2013

ORA-00060: deadlock detected while waiting for resource// Performance issue

ORA-00060: deadlock detected while waiting for resource
Deadlocks in Oracle with logically disjoint transactions usually involve unindexed foreign keys:
There are two issues associated with unindexed foreign keys. 
The first is the fact that a table lock will result if you update the parent records primary key (very very unusual) or if you delete the parent record and the child’s foreign key is not indexed.
Locks in Oracle are managed at the row level. Concurrent disjoint transactions should not interfere with one another. Unindexed foreign keys are an exception, since it can result in a complete TABLE LOCK.
I am testing after how many ORA-00060 errors a recursive transaction aborts. This is a transaction 
which calls itself recursively when it fails, and is in a deadlock state with another transaction 
which also calls itself recursively when it fails.
That is, is there a limit when 2 such deadlocked procedures will give up? Is this because of 
recursive calls or deadlocks?

A script that can find foreign keys across all users that could cause blocking locks.
select owner, table_name, constraint_name,
       cname1 || nvl2(cname2,’,'||cname2,null) ||
       nvl2(cname3,’,'||cname3,null) || nvl2(cname4,’,'||cname4,null) ||
       nvl2(cname5,’,'||cname5,null) || nvl2(cname6,’,'||cname6,null) ||
       nvl2(cname7,’,'||cname7,null) || nvl2(cname8,’,'||cname8,null)
    from ( select b.owner,
                  max(decode( position, 1, column_name, null )) cname1,
                  max(decode( position, 2, column_name, null )) cname2,
                  max(decode( position, 3, column_name, null )) cname3,
                  max(decode( position, 4, column_name, null )) cname4,
                  max(decode( position, 5, column_name, null )) cname5,
                  max(decode( position, 6, column_name, null )) cname6,
                  max(decode( position, 7, column_name, null )) cname7,
                  max(decode( position, 8, column_name, null )) cname8,
                  count(*) col_cnt
             from (select substr(table_name,1,30) table_name,
                          substr(constraint_name,1,30) constraint_name,
                          substr(column_name,1,30) column_name,
                     from all_cons_columns ) a,
                  all_constraints b
            where a.constraint_name = b.constraint_name
              and b.owner = a.owner
              and b.constraint_type = ‘R’
              and b.status = ‘ENABLED’
            group by b.owner, b.table_name, b.constraint_name
         ) cons
   where col_cnt > ALL
           ( select count(*)
               from all_ind_columns i
              where i.table_owner = cons.owner
                and i.table_name = cons.table_name
                and i.column_name in (cname1, cname2, cname3, cname4,
                                      cname5, cname6, cname7, cname8 )
                and i.column_position <= cons.col_cnt
              group by i.index_name
   order by 1, 2, 3;

If you will get  any output then you can fix your dead lock issue.
And also you can resolve  Performance issue