Deadlock Graph, and some questions.

From: Galen Boyer <galenboyer_at_hotpop.com>
Date: 12 Aug 2004 09:06:15 -0500
Message-ID: <uwu04e9nh.fsf@standardandpoors.com>

Here is a snippet from a trace file, and I have put in "?????" delimeters for my particular questions:

SESSION ID:(15.5983) 2004-08-12 09:35:51.418 DEADLOCK DETECTED A ?????????????? Current SQL statement for this session: INSERT INTO deal_asset_h ( id
,asset_id, colat_id, dl_id
,target_group_id
) VALUES ( :b5
,:b4, :b3, :b2
,:b1
)
- PL/SQL Call Stack ----- object line object handle number name 209D634C 42 package body GRERS.P_DEAL_ASSET 21481988 1101 package body GRERS_1X.ETL_DML 20701F0C 1376 package body GRERS_1X.ETL_CMBS 20701F0C 2192 package body GRERS_1X.ETL_CMBS 20701F0C 2661 package body GRERS_1X.ETL_CMBS 20701F0C 2690 package body GRERS_1X.ETL_CMBS 20651B64 1 anonymous block 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 B ??????????????????? TX-0003001b-00009a1d 16 15 X 17 19 S TX-000a0005-00009a24 17 19 X 16 15 S session 15: DID 0001-0010-00000052 session 19: DID 0001-0011-0000001C session 19: DID 0001-0011-0000001C session 15: DID 0001-0010-00000052 Rows waited on: C ??????????????????? Session 19: obj - rowid = 0008881C - AAAAAAAALAAABLqAAA (dictionary objn - 559132, file - 11, block - 4842, slot - 0) Session 15: obj - rowid = 0008844C - AAAAAAAAMAAAfvaAAA (dictionary objn - 558156, file - 12, block - 130010, slot - 0) Information on the OTHER waiting sessions: Session 19: pid=17 serial=319 audsid=489269 user: 67/<none> O/S info: user: boyerg, term: , ospid: 2396:2328, machine: MHF2\BOYERG4 program: sqlplus.exe application name: SQL*Plus, hash value=3669949024 Current SQL Statement:

INSERT INTO deal_asset_h

                   (
                   id


                  ,asset_id, colat_id, dl_id

                  ,target_group_id

                   )
            VALUES
                   (
                   :b5


                  ,:b4, :b3, :b2

                  ,:b1

                   )

End of information on OTHER waiting sessions.

D ??????????????????????

PROCESS STATE

Process global information:

         process: 1C251E10, call: 1C2DB20C, xact: 1CBDF420, curses: 1C27DE4C, usrses: 1C27DE4C
      ----------------------------------------
      SO: 1C251E10, type: 2, owner: 00000000, flag: INIT/-/-/0x00
      (process) Oracle pid=16, calls cur/top: 1C2DB20C/1C2DB0E0, flag: (0) -
                int error: 0, call error: 0, sess error: 0, txn error 0
      (post info) last post received: 0 0 101
                  last post received-location: kgldnp: post after freeing latch
                  last process to post me: 1c251e10 41 0
                  last post sent: 0 0 15
                  last post sent-location: ksasnd
                  last process posted by me: 1c24f110 1 6
        (latch info) wait_event=0 bits=10
          holding     20a5098 Parent+children enqueue hash chains level=4 
            Location from where latch is held: ksqcmi: kslgpl: 
            Context saved from call: 0
            state=busy
            recovery area:

Dump of memory from 0x1C24E174 to 0x1C24E17C

Questions:

Is the SQL displayed and the information surrounding it saying that the deadlock was detected while this sql was executing, or is it saying that this SQL a culprit in the deadlock?
Is there Steve Adams type info found in the Resource Name?
How would I go about learning what is found at these addresses, and more importantly, when should I decide that I need to dig this deeply? My main process up until now for solving deadlocks has been to analyze the order of transaction processing and objects being accessed. I have taken a system dump, (I think this was the correct syntax) but not sure what useful information to glean out of the file.

alter system dump datafile 11 block min 4842 block max 4842;

D) Is there alot of deadlock clue information found in the PROCESS STATE info, or is the most useful information between A and D?

-- 
Galen Boyer

Received on Thu Aug 12 2004 - 09:06:15 CDT