Thinking Out Loud

November 7, 2010

Upgrade Pain01

Filed under: oracle,upgrade — mdinh @ 2:07 pm

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

    Leave a Comment »

    No comments yet.

    RSS feed for comments on this post. TrackBack URI

    Leave a Reply

    Fill in your details below or click an icon to log in:

    WordPress.com Logo

    You are commenting using your WordPress.com account. Log Out / Change )

    Twitter picture

    You are commenting using your Twitter account. Log Out / Change )

    Facebook photo

    You are commenting using your Facebook account. Log Out / Change )

    Google+ photo

    You are commenting using your Google+ account. Log Out / Change )

    Connecting to %s

    The Rubric Theme Blog at WordPress.com.

    Follow

    Get every new post delivered to your Inbox.

    Join 273 other followers

    %d bloggers like this: