Re: Determine exact cause of deadlock based on trace file

On 18 Oct 2004 19:45:05 -0700, gautrak_at_yahoo.com (Gautrak) wrote:

>Hi,
>
>Below is a deadlock graph from the trace file generated.
>
>Kindly help me to derive exact cause and recommended changes in
>database to reduce the probability of these happening.
>
>- 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.
>
>-----------------------------------------------------------------------

This quote from near the top of the trace pretty well sums up your chances of fixing the problem from within the database:

" It is a deadlock due to user error in the design of an application or from issuing incorrect ad-hoc SQL. :" Received on Tue Oct 19 2004 - 07:50:52 CDT