Re: Determine exact cause of deadlock based on trace file

gautrak_at_yahoo.com (Gautrak) wrote in message news:<76ceef56.0410181845.6aee3d01_at_posting.google.com>...
> ...
> - The tablespaces are locally managed migrated from dictionary managed
> tablespaces (segment space management : USER)
> - The database access is through application servers with connection
> pooling.
> - Primary key of table SK_TB_ANS is (NM_CID, SK_SV_ID, USR_ID, SK_QNO)
> - There is one more index on this table on (NM_CID, SK_SV_ID, USR_ID)
> - There is no foreign key on SK_TB_ANS
> - File 16 : Undo tablespace
> - File 14 : Tablespace of table SK_TB_ANS
> - Object# 42293 : table SK_TB_ANS
> - This has happened during a time when large number of users were
> accessing the application
> - Removing deletes is a major change in application and can be
> considered only if this situation can not be reduced by any
> improvements at database level.
>
>
> TIA,
> Gautrak
>
> ---------------------------------------
> Oracle9i Enterprise Edition Release 9.2.0.5.0 - 64bit Production
> With the Partitioning, OLAP and Oracle Data Mining options
> JServer Release 9.2.0.5.0 - Production
> ORACLE_HOME = /sklc/oracle/product/9.2
> System name: SunOS
> Node name: skdb
> Release: 5.9
> Version: Generic_117171-02
> Machine: sun4u
> Instance name: sklc
> Redo thread mounted by this instance: 1
> Oracle process number: 33
> Unix process pid: 13651, image: oracle_at_skdb (TNS V1-V3)
>
> *** 2004-10-15 06:09:27.286
> *** SESSION ID:(63.29) 2004-10-15 06:09:27.285
> DEADLOCK DETECTED
> Current SQL statement for this session:
> DELETE FROM SK_TB_ANS WHERE NM_CID = :1 AND SK_SV_ID = :2 AND USR_ID
> = :3 AND SK_QNO = :4
> 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-008f000c-00000002 33 63 X 49 55
> S
> TX-00110012-000030e5 49 55 X 34 35
> X
> TX-008f000c-00000002 34 35 X 33 63
> X
> session 63: DID 0001-0021-0000000A session 55: DID
> 0001-0031-00000008
> session 55: DID 0001-0031-00000008 session 35: DID
> 0001-0022-00000009
> session 35: DID 0001-0022-00000009 session 63: DID
> 0001-0021-0000000A
> Rows waited on:
> Session 55: obj - rowid = 00000000 - D/////AAQAAAFJNAAA
> (dictionary objn - 0, file - 16, block - 21069, slot - 0)
> Session 35: obj - rowid = 0000A535 - AAAKU1AAOAAB2LiAAV
> (dictionary objn - 42293, file - 14, block - 484066, slot - 21)
> Session 63: obj - rowid = 0000A535 - AAAKU1AAOAAB2LiABD
> (dictionary objn - 42293, file - 14, block - 484066, slot - 67)
> Information on the OTHER waiting sessions:
> Session 55:
> pid=49 serial=29 audsid=1105150 user: 78/sklc_user
> O/S info: user: admuser, term: , ospid: 9715, machine: skappser
> program: .kjs.memopt_at_skappser (TNS V1-V3)
> application name: .kjs.memopt_at_skappser (TNS V1-V3), hash value=0
> Current SQL Statement:
> INSERT INTO SK_TB_ANS (USR_ID , SK_ANS_TXT , SK_QNO ,SK_SV_ID,
> NM_CID,VC_CRTD_BY, DT_CRTD_DT, VC_MNTN_BY, DT_MNTN_DT) VALUES ( :1 ,
> :2 , :3 , :4 , :5 ,:6,SYSDATE,:7,SYSDATE)
> Session 35:
> pid=34 serial=29 audsid=1105135 user: 78/sklc_user
> O/S info: user: admuser, term: , ospid: 9715, machine: skappser
> program: .kjs.memopt_at_skappser (TNS V1-V3)
> application name: .kjs.memopt_at_skappser (TNS V1-V3), hash value=0
> Current SQL Statement:
> UPDATE SK_TB_ANS SET SK_ANS_TXT = :1 , VC_MNTN_BY = :2 ,
> DT_MNTN_DT = SYSDATE WHERE NM_CID = :3 AND SK_SV_ID = :4 AND USR_ID =
> :5 AND SK_QNO = :6
> End of information on OTHER waiting sessions.

I'm waiting for an expert's comment. I can't figure out how to get a "rows waited on" pointing to a block in an undo tablespace (your session 55 apparently waits on a block in undo datafile 16). And the rowid looks strange. You don't have any problem with your undo tablespace? Running out of space? Can't auto create more undo extents? (see dba_undo_extents)

Worst case I suppose is commit right before your DELETE. Most books recommend handling ORA-60 in your own code gracefully. Catch it and wait a few seconds and try again.

BTW, your tablespace allocation type, not segment space management, is USER. But I don't think it matters.

Yong Huang Received on Wed Oct 20 2004 - 15:43:16 CDT