ORA-00060-DEADLOCK DETECTED

Thu Dec 01 14:53:28 2016
ORA-00060: Deadlock detected. More info in file /u01/app/oracle/diag/rdbms/db_name/db_name/trace/db_name_ora_120412.trc.
Thu Dec 01 14:53:40 2016
ORA-00060: Deadlock detected. More info in file /u01/app/oracle/diag/rdbms/db_name/db_name/trace/db_name_
ora_107905.trc.
[oracle@ trace]$



*** 2016-12-01 14:53:37.906
DEADLOCK DETECTED ( ORA-00060 )

[Transaction Deadlock]

The following deadlock is not an ORACLE error. It is a
deadlock due to user error in the design of an application
or from issuing incorrect ad-hoc SQL. The following
information may aid in determining the deadlock:


Deadlock condition : generally this situations can be seen at OS perspective at a common pace , but when it occurs at the DB level then surely it involves developers and DBA to look kindle at the code and infrastructure manipulation perspective ..

Usual cause of deadlocks is due to poor coding skills and improper management of DB resources 

DBA need to work with the Apps teams closely to resolve this issue. 

Let me give an example here : 

If a query is accessing the block,which is accessed by another query might cause contention & result in the conditions of dead lock ..
The same condition can be ruled out at OS perspective using Mutual exclusion and Semaphores.
Where as with DB perspective we need to change the parameter for a particular table which causing dead lock 

There are 2 parameter with respect to concurrent access of blocks 

ini_trans
max_trans  which states mini and max amount of users can access the DB in a given single point of time

ini_trans is 1 by default and max_trans 255

To avoid issues with deadloack ini_trans can be tune as per the requirement of apps and load on the DB for a particular block