Hi folks,
we receive an ORA-00060 in our database occasionally. I fully understand what could cause a deadlock. I have read several Oracle notes and threads but we do neither
- update a PK
- use a FK
- nor have a bitmap index in place
The weird thing is that the deadlock occurs when two completely unrelated tables are updated. In both cases a status column is updated with the respective PK.
Here is the trace file excerpt:
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
TX-000a001a-0011f392 21 175 X 22 176 X
TX-00050012-0002d3fd 22 176 X 21 175 X
session 175: DID 0001-0017-0000127B session 176: DID 0001-0017-00001273
session 176: DID 0001-0017-00001273 session 175: DID 0001-0017-0000127B
Rows waited on:
Session 175: obj - rowid = 00004040 - AAAEEOAAFAAD4kOAAg
(dictionary objn - 16448, file - 5, block - 1018126, slot - 32)
Session 176: obj - rowid = 00004012 - AAAEEcAAFAAG6OUAAk
(dictionary objn - 16402, file - 5, block - 1811348, slot - 36)
----- Information for the OTHER waiting sessions -----
Session 176:
sid: 176 ser: 2271 audsid: 2291749 user: 35/SMARTLD
flags: (0xe5) USR/- flags_idl: (0x1) BSY/-/-/-/-/-
flags2: (0x40008) -/-
pid: 22 O/S info: user: oracle, term: UNKNOWN, ospid: 3700
image: oracle@blnn726x.ww004.siemens.net (S001)
client details:
O/S info: user: smartld, term: JDBC, ospid: 1234
machine: blnn725x program: SMART-LD Web Application 2.4.17
application name: SMART-LD Web Application 2.4.17, hash value=682445801
current SQL:
update st_pr_prg_aufrufe set speicherung = :1 where vbs_id = :2
----- End of information for the OTHER waiting sessions -----
Information for THIS session:
----- Current SQL Statement for this session (sql_id=cy1wynvmwusug) -----
update st_td_arbeitsdatensatz set status = :1 where id = :2
===================================================
PROCESS STATE
As you can see tables st_pr_prg_aufrufe and st_td_arbeitsdatensatz are updated. Tables, ROWIDs and blocks are completely different.
I am completely lost where to start digging. Is anyone able to guide me into the right direction?
My system is:
- Red Hat Enterprise Linux Workstation release 6.3 (Santiago)
- Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production; With the Partitioning and Real Application Testing options