Upgrade Pain01

Completed upgrade of OLTP database from 10.2.0.4 to 11.2.0.2

To my surprise, I found 199 dead locks and increasing fast in the alert log as shown:

> grep -ic ‘ORA-00060: Deadlock detected’ alert_db06.log
199

The trace file shows:

*** 2010-11-07 03:53:20.238
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-0003b69d-00000000 113 38 SX SSX 119 221 SX SSX
TM-0003b69d-00000000 119 221 SX SSX 113 38 SX SSX

session 38: DID 0001-0071-00000004 session 221: DID 0001-0077-00000004
session 221: DID 0001-0077-00000004 session 38: DID 0001-0071-00000004

Rows waited on:
Session 38: no row
Session 221: no row

—– Information for the OTHER waiting sessions —–
Session 221:
sid: 221 ser: 4 audsid: 795496700 user: 211/JOHN
flags: (0×45) USR/- flags_idl: (0×1) BSY/-/-/-/-/-
flags2: (0×40008) -/-
pid: 119 O/S info: user: oracle, term: UNKNOWN, ospid: 25907
image: oracle@proddb06
client details:
O/S info: user: john, term: unknown, ospid: 1234
machine: terminator program: JDBC Thin Client
application name: JDBC Thin Client, hash value=2546894660
current SQL:
DELETE FROM parent WHERE pk_id = :”SYS_B_0″

The following deadlock is not an ORACLE error. It is a deadlock due to the design of an application

Try telling this to the duhvelopers.

Before going further, I would like to thank Google and everyone who shares his/her knowledge and pain.

google: deadlock 11g and found Deadlock on Oracle 11g but Not on 10g

Root cause analysis:

Bad Design

Anecdotal Upgrade Testing

Upgrade

Not spending enough time on the internet

About these ads