Detection of Deadlock errors and investigation

As per Wiki, In concurrent programming, a deadlock is a situation in which two or more competing actions are each waiting for the other to finish, and thus neither ever does.

In telecommunication systems, deadlocks occur mainly due to lost or corrupt signals instead of resource contention.

An effective way to avoid database deadlocks is to follow this approach from the Oracle Locking Survival Guide:

Application developers can eliminate all risk of enqueue deadlocks by ensuring that transactions requiring multiple resources always lock them in the same order.

In oracle database, when deadlock occur the 'ORA-00060' error is written to alert log and the location of trace file for more details .

Before 11g, alert logs were found using the below option

show parameter BACKGROUND_DUMP_DEST

and from 11g, Location of alert logs are found by the below SQL, 

select * from   V$DIAG_INFO where name ='Diag Trace'
   INST_ID NAME                     VALUE
---------- ------------------------ ------------------------------------------
         1 Diag Trace               /database/diag/rdbms/orcl/ORCL/trace
Run the below command to look for deadlock information in alert log
ex:
grep -i deadlock alert_ORCL.log
ORA-00060: Deadlock detected. More info in file /database/diag/rdbms/orcl/ORCL/trace/ORCL_ora_5495.trc

the particular trce file can be studied to find greater information on the deadlock.

shows 5 lines from the occurrence  of deadlock in trace files
grep -A 5 "current SQL:" /database/diag/rdbms/orcl/ORCL/trace/*.trc

shows 5 lines from the occurrence of deadlock in trace files
grep -A 5 'ORA-00060' /database/diag/rdbms/orcl/ORCL/trace/*.trc  

shows the tracefiles containing deadlock errors in trace files and display their time & size
 grep -l 'ORA-00060' /database/diag/rdbms/orcl/ORCL/trace/*.trc   |xargs ls -ltrh