Re: Deadlock Graph, and some questions.

On 12 Aug 2004 09:06:15 -0500, Galen Boyer <galenboyer_at_hotpop.com> wrote:

>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:
>
>A) 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?
>
>B) Is there Steve Adams type info found in the Resource Name?
>
>C) 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?

1. It is saying this statement ran into the deadlock. See also 'rows waited on'
2. Likely the first number is the address in v$transaction You also see session 15 is waiting for session 19 and session 19 is waiting for session 15.
3. You would need to identify the tables involved, which you can learn from the objn, which equals data_object_id in dba_objects. It would be nice to know which record was involved, as you have the rowid, you know which record it is. Now you should be able to find this situation in your code. I don't think a block dump is necessary.
4. The only interesting bit is probably xact: <hexadecimal number>. This should link back to v$transaction

Hth

--
Sybrand Bakker, Senior Oracle DBA