Deadlock analyzes

trace file:

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 graph:
———Blocker(s)——– ———Waiter(s)———
Resource Name process session holds waits process session holds waits
TM-00021851-00000000 142 644 SX 128 483 SX SSX
TX-000a0005-0001da7d 128 483 X 142 644 X
session 644: DID 0001-008E-00000053 session 483: DID 0001-0080-0000007E
session 483: DID 0001-0080-0000007E session 644: DID 0001-008E-00000053
Rows waited on:
Session 483: no row
Session 644: obj – rowid = 00021F2B – AAAh8rABKAAAHKMAAD
(dictionary objn – 139051, file – 74, block – 29324, slot – 3)
Information on the OTHER waiting sessions:
Session 483:
pid=128 serial=202 audsid=281953658 user: 59/TASGEN
O/S info: user: aswlsadm, term: unknown, ospid: 1234, machine: aipsnc11
program: JDBC Thin Client
application name: JDBC Thin Client, hash value=2546894660
Current SQL Statement:
delete FISR_CLS where FDSVCSCH_ARL_CD = :1 and FLT_NR = :2 and OPS_SUFFIX = :3 and FLT_DT = to_date(:4, ‘YYYY-MM-DD’) and DPT_STA_CD = :5 and ARR_STA_CD = :6 and DBV_CD = :7 and DEPT_CD = :8 and CLS_COS_CD = :9
End of information on OTHER waiting sessions.
Current SQL statement for this session:
delete from fisr_clspktvar where FDSVCSCH_ARL_CD = :1 and DBV_CD = :2 and DEPT_CD = :3 and DPT_STA_CD = :4 and ARR_STA_CD = :5 and CLS_COS_CD = :6 and FLT_NR = :7 and OPS_SUFFIX = :8 and FLT_DT = TO_DATE (‘2012-04-19’, ‘YYYY-MM-DD’) and PKT_POL_NR = 2202 and PKT_POL_VAR_ND = 2
===================================================
PROCESS STATE

Get object name

from TM-00021851-00000000  – get 00021851 and convert to decimal – that is object_id

select *
from dba_objects
where object_id = to_number(‘00021851′,’XXXXXXXX’)

Different Lock Types and Modes

The most common lock types seen in deadlock graphs are TX and TM locks. These may appear held / requested in a number of modes. It is the lock type and modes which help determine what situation has caused the deadlock.

Lock Mode	Mode    Requested	Probable Cause
TX	X (mode 6)	Application row level conflict. Avoid by recoding the application to ensure  rows are always locked in a particular order.
TX	S (mode 4)	There are a number of reasons that a TX lock may be requested in S mode. See Document 62354.1 for a list of when TX locks are requested in mode 4.
TM	SSX (mode 5) or S (mode 4)	This is usually related to the existence of foreign key constraints where the columns are not indexed on the child table. See Document 33453.1 for how to locate such constraints. See below for locating the OBJECT being waited on

Although other deadlock scenarios can happen the above are the most common.

https://support.oracle.com/CSP/main/article?cmd=show&type=NOT&id=15476.1