Solved

Oracle 9i , how do I find the deadlock object in the trace file?

Posted on 2003-12-10
3
2,017 Views
Last Modified: 2013-12-11
I have many UNIX servers with many Oracle instances. When a certain application is run on the database server, it creates a deadlock, which in turn hits a nasty Oracle 9.2.3 bug which corrupts Oracle Home and takes out all the Oracle instances on that server. I have lost 20 Oracle instances on 4 UNIX (HP-UX 9000 11.0 & 11.11) ( AIX 5.1) database servers. I need to find out what ojbect is causing the deadlock, so that I can have the developers fix the issue. How do I read the trace file, to find the deadlock table or object?  Following is a portion of one of the trace files.

 With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.3.0 - Production
ORACLE_HOME = /oracle/app/oracle/product/920
System name:    HP-UX
Node name:      dbsrv2
Release:        B.11.00
Version:        U
Machine:        9000/800
Instance name: lsu9
Redo thread mounted by this instance: 1
Oracle process number: 12
Unix process pid: 16414, image: oracle@dbsrv2 (TNS V1-V3)
*** 2003-11-08 14:57:33.744
*** SESSION ID:(18.437) 2003-11-08 14:57:33.700
DEADLOCK DETECTED
Current SQL statement for this session:
delete from dependency$ where d_obj#=:1
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-000075a0        12      18     X             12      18           X
session 18: DID 0001-000C-00000002      session 18: DID 0001-000C-00000002
Rows waited on:
Session 18: no row
Information on the OTHER waiting sessions:
End of information on OTHER waiting sessions.
===================================================
PROCESS STATE
-------------
Process global information:
     process: c0000000094581f0, call: c0000000095986a8, xact: c00000000a3c7850,
curses: c0000000094bfb50, usrses: c0000000094c4540
  ----------------------------------------
  SO: c0000000094581f0, type: 2, owner: 0000000000000000, flag: INIT/-/-/0x00
  (process) Oracle pid=12, calls cur/top: c0000000095986a8/c000000009598468, fla
g: (0) -
            int error: 0, call error: 0, sess error: 0, txn error 0
  (post info) last post received: 0 0 0
              last post received-location: No post
              last process to post me: none
Standard input
0
Comment
Question by:bonniekittelson
  • 2
3 Comments
 
LVL 7

Accepted Solution

by:
yoren earned 250 total points
ID: 9982799
Since there's no particular row that's getting locked, you may be getting deadlocks due to insufficient transaction slots. Try increasing the INITRANS storage parameter on your tables. As far as determining what table is missing, try adding the following line to your init.ora and see if you get anything more.

event='60 trace name errorstack level 10'

(You can find more on this at http://www.orafaq.com/faqdbain.htm)
0
 
LVL 7

Expert Comment

by:yoren
ID: 9982802
Correction - I meant "determining what table is deadlocking" instead of "determining what table is missing"
0
 

Author Comment

by:bonniekittelson
ID: 10012825
I will put the trace in and see if I can get any more information on this error. I will let you know.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Schema creation in Oracle12c 6 48
Processing of multiple cursor 6 47
Query to identify changes between rows of two tables 8 47
Oracle - SQL Parse String 5 34
Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This video shows how to recover a database from a user managed backup

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question