Fredo
Member
Hello,
We run stress tests on our platform and after 2 hours, we meet deadlocks on 2 tables.
My Platform :
E1 8.94 L1 on a Sun Fire V440 4 Gb RAM, 2 CPU, Solaris 9
Oracle 9.2.0.5 on a Sun Fire V440 8 Gb RAM, 4 CPU, Solaris 9
WebSphere 5.0.2.12 on 4 Sun Fire V240 4 Gb RAM, 2 CPU, Solaris 9
The jde error log :
15567 Thu Mar 23 14:27:14.206626 dbperfrq.c385
OCI0000178 - Unable to execute - UPDATE PRODDTA.F43090 SET PCAN8=:BND1,PCITM=:BND2,PCLITM=:BND3,PCAITM=:BND4,PCMCU=:BND5,PCCEFJ=:BND6,PCCXPJ=:BND7,PCCERS=:BND8,PCNROU=:BND9,PCTROU=:BND10,PCTFRQ=:BND11,PCORDS=:BND12,PCTFRD=:BND13,PCLTDJ=:BND14,PCDAYE=:BND15,PCDAYL=:BND16,PCLTQP=:BND17,PCLTNM=:BND18,PCLTTS=:BND19,PCALTD=:BND20,PCLLTD=:BND21,PCACST=:BND22,PCLCST=:BND23,PCNOOD=:BND24,PCQTYS=:BND25,PCQTYR=:BND26,PCQTYC=:BND27,PCQTYW=:BND28,PCQTYJ=:BND29,PCQTYA=:BND30,PCUODR=:BND31,PCUREC=:BND32,PCUPTD=:BND33,PCUONT=:BND34,PCUEAR=:BND35,PCULAT=:BND36,PCAODR=:BND37,PCAREC=:BND38,PCAPTD=:BND39,PCAONT=:BND40,PCAEAR=:BND41,PCALAT=:BND42,PCRONT=:BND43,PCREAR=:BND44,PCRLAT=:BND45,PCURCD=:BND46,PCURDT=:BND47,PCURAT=:BND48,PCURAB=:BND49,PCURRF=:BND50,PCROTP=:BND51,PCPOC=:BND52 WHERE ( PCAN8 = :KEY1 AND PCMCU = :KEY2 AND PCITM = :KEY3 )
15567 Thu Mar 23 14:27:14.207536 dbperfrq.c391
OCI0000179 - Error - ORA-00060: deadlock detected while waiting for resource
15567 Thu Mar 23 14:27:14.207914 jdb_drvm.c971
JDB9900401 - Failed to execute db request
15567 Thu Mar 23 14:27:14.208110 jdb_exet.c2752
JDB3600011 - Failed to perform Update for F43090
15567 Thu Mar 23 14:27:14.208314 jdb_exet.c6732
JDB9901232 - Canceling transaction because: TC033 UpdateTable: update failed
15567 Thu Mar 23 14:27:15.123987 xmlcallobj.cpp778
KNT0000195 - hEnv/hUser inval from man com queue
15567 Thu Mar 23 14:27:15.172951 xmlcallobj.cpp778
KNT0000195 - hEnv/hUser inval from man com queue
15567 Thu Mar 23 14:27:15.173363 xmlcallobj.cpp778
KNT0000195 - hEnv/hUser inval from man com queue
15567 Thu Mar 23 14:27:15.176925 xmlcallobj.cpp778
KNT0000195 - hEnv/hUser inval from man com queue
15567 Thu Mar 23 14:27:15.177135 xmlcallobj.cpp778
KNT0000195 - hEnv/hUser inval from man com queue
15567 Thu Mar 23 15:06:00.400783 jdb_ctl.c5580
JDB4200003 - OPEN TABLE NOT CLOSED = F4102
The oracle error log :
/oralog/jdeprod/bdump/jdeprod_s002_14300.trc
Oracle9i Release 9.2.0.5.0 - 64bit Production
JServer Release 9.2.0.5.0 - Production
ORACLE_HOME = /oracle/9ir2
System name: SunOS
Node name: s2-bdd
Release: 5.9
Version: Generic_118558-21
Machine: sun4u
Instance name: jdeprod
Redo thread mounted by this instance: 1
Oracle process number: 12
Unix process pid: 14300, image: oracle@s2-bdd (S002)
*** 2006-03-23 14:23:28.337
*** SESSION ID
129.650) 2006-03-23 14:23:28.336
DEADLOCK DETECTED
Current SQL statement for this session:
UPDATE PRODDTA.F4102 SET IBITM=:BND1,IBMCU=:BND2,IBMRPC=:BND3,IBUSER=:BND4,IBPID=:BND5,IB
JOBN=:BND6,IBUPMJ=:BND7,IBTDAY=:BND8 WHERE ( IBITM = :KEY1 AND IBMCU = :KEY2 )
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-001e0025-0000060c 12 129 X 11 234 X
TX-00090002-0000ebf0 11 234 X 12 129 X
session 129: DID 0001-000B-000004F1 session 234: DID 0001-000A-00000E35
session 234: DID 0001-000A-00000E35 session 129: DID 0001-000B-000004F1
Rows waited on:
Session 234: obj - rowid = 00007102 - AAAHEPAApAAALyqAAF
(dictionary objn - 28930, file - 41, block - 48298, slot - 5)
Session 129: obj - rowid = 00007100 - AAAHEAAAqAAALKeAAA
(dictionary objn - 28928, file - 42, block - 45726, slot - 0)
Information on the OTHER waiting sessions:
Session 234:
pid=11 serial=3956 audsid=122773 user: 23/PSFT
O/S info: user: psft, term: , ospid: 15567, machine: s2-btc
program: jdenet_k@s2-btc (TNS V1-V3)
application name: jdenet_k@s2-btc (TNS V1-V3), hash value=0
Current SQL Statement:
UPDATE PRODDTA.F43090 SET PCAN8=:BND1,PCITM=:BND2,PCLITM=:BND3,PCAITM=:BND4,PCMCU=:BND5,P
CCEFJ=:BND6,PCCXPJ=:BND7,PCCERS=:BND8,PCNROU=:BND9,PCTROU=:BND10,PCTFRQ=:BND11,PCORDS=:BND
12,PCTFRD=:BND13,PCLTDJ=:BND14,PCDAYE=:BND15,PCDAYL=:BND16,PCLTQP=:BND17,PCLTNM=:BND18,PCL
TTS=:BND19,PCALTD=:BND20,PCLLTD=:BND21,PCACST=:BND22,PCLCST=:BND23,PCNOOD=:BND24,PCQTYS=:B
ND25,PCQTYR=:BND26,PCQTYC=:BND27,PCQTYW=:BND28,PCQTYJ=:BND29,PCQTYA=:BND30,PCUODR=:BND31,P
CUREC=:BND32,PCUPTD=:BND33,PCUONT=:BND34,PCUEAR=:BND35,PCULAT=:BND36,PCAODR=:BND37,PCAREC=
:BND38,PCAPTD=:BND39,PCAONT=:BND40,PCAEAR=:BND41,PCALAT=:BND42,PCRONT=:BND43,PCREAR=:BND44
,PCRLAT=:BND45,PCURCD=:BND46,PCURDT=:BND47,PCURAT=:BND48,PCURAB=:BND49,PCURRF=:BND50,PCROT
P=:BND51,PCPOC=:BND52 WHERE ( PCAN8 = :KEY1 AND PCMCU = :KEY2 AND PCITM = :KEY3 )
Has anyome meet the same problem ?
Thanks in advance
Frédéric
We run stress tests on our platform and after 2 hours, we meet deadlocks on 2 tables.
My Platform :
E1 8.94 L1 on a Sun Fire V440 4 Gb RAM, 2 CPU, Solaris 9
Oracle 9.2.0.5 on a Sun Fire V440 8 Gb RAM, 4 CPU, Solaris 9
WebSphere 5.0.2.12 on 4 Sun Fire V240 4 Gb RAM, 2 CPU, Solaris 9
The jde error log :
15567 Thu Mar 23 14:27:14.206626 dbperfrq.c385
OCI0000178 - Unable to execute - UPDATE PRODDTA.F43090 SET PCAN8=:BND1,PCITM=:BND2,PCLITM=:BND3,PCAITM=:BND4,PCMCU=:BND5,PCCEFJ=:BND6,PCCXPJ=:BND7,PCCERS=:BND8,PCNROU=:BND9,PCTROU=:BND10,PCTFRQ=:BND11,PCORDS=:BND12,PCTFRD=:BND13,PCLTDJ=:BND14,PCDAYE=:BND15,PCDAYL=:BND16,PCLTQP=:BND17,PCLTNM=:BND18,PCLTTS=:BND19,PCALTD=:BND20,PCLLTD=:BND21,PCACST=:BND22,PCLCST=:BND23,PCNOOD=:BND24,PCQTYS=:BND25,PCQTYR=:BND26,PCQTYC=:BND27,PCQTYW=:BND28,PCQTYJ=:BND29,PCQTYA=:BND30,PCUODR=:BND31,PCUREC=:BND32,PCUPTD=:BND33,PCUONT=:BND34,PCUEAR=:BND35,PCULAT=:BND36,PCAODR=:BND37,PCAREC=:BND38,PCAPTD=:BND39,PCAONT=:BND40,PCAEAR=:BND41,PCALAT=:BND42,PCRONT=:BND43,PCREAR=:BND44,PCRLAT=:BND45,PCURCD=:BND46,PCURDT=:BND47,PCURAT=:BND48,PCURAB=:BND49,PCURRF=:BND50,PCROTP=:BND51,PCPOC=:BND52 WHERE ( PCAN8 = :KEY1 AND PCMCU = :KEY2 AND PCITM = :KEY3 )
15567 Thu Mar 23 14:27:14.207536 dbperfrq.c391
OCI0000179 - Error - ORA-00060: deadlock detected while waiting for resource
15567 Thu Mar 23 14:27:14.207914 jdb_drvm.c971
JDB9900401 - Failed to execute db request
15567 Thu Mar 23 14:27:14.208110 jdb_exet.c2752
JDB3600011 - Failed to perform Update for F43090
15567 Thu Mar 23 14:27:14.208314 jdb_exet.c6732
JDB9901232 - Canceling transaction because: TC033 UpdateTable: update failed
15567 Thu Mar 23 14:27:15.123987 xmlcallobj.cpp778
KNT0000195 - hEnv/hUser inval from man com queue
15567 Thu Mar 23 14:27:15.172951 xmlcallobj.cpp778
KNT0000195 - hEnv/hUser inval from man com queue
15567 Thu Mar 23 14:27:15.173363 xmlcallobj.cpp778
KNT0000195 - hEnv/hUser inval from man com queue
15567 Thu Mar 23 14:27:15.176925 xmlcallobj.cpp778
KNT0000195 - hEnv/hUser inval from man com queue
15567 Thu Mar 23 14:27:15.177135 xmlcallobj.cpp778
KNT0000195 - hEnv/hUser inval from man com queue
15567 Thu Mar 23 15:06:00.400783 jdb_ctl.c5580
JDB4200003 - OPEN TABLE NOT CLOSED = F4102
The oracle error log :
/oralog/jdeprod/bdump/jdeprod_s002_14300.trc
Oracle9i Release 9.2.0.5.0 - 64bit Production
JServer Release 9.2.0.5.0 - Production
ORACLE_HOME = /oracle/9ir2
System name: SunOS
Node name: s2-bdd
Release: 5.9
Version: Generic_118558-21
Machine: sun4u
Instance name: jdeprod
Redo thread mounted by this instance: 1
Oracle process number: 12
Unix process pid: 14300, image: oracle@s2-bdd (S002)
*** 2006-03-23 14:23:28.337
*** SESSION ID
129.650) 2006-03-23 14:23:28.336DEADLOCK DETECTED
Current SQL statement for this session:
UPDATE PRODDTA.F4102 SET IBITM=:BND1,IBMCU=:BND2,IBMRPC=:BND3,IBUSER=:BND4,IBPID=:BND5,IB
JOBN=:BND6,IBUPMJ=:BND7,IBTDAY=:BND8 WHERE ( IBITM = :KEY1 AND IBMCU = :KEY2 )
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-001e0025-0000060c 12 129 X 11 234 X
TX-00090002-0000ebf0 11 234 X 12 129 X
session 129: DID 0001-000B-000004F1 session 234: DID 0001-000A-00000E35
session 234: DID 0001-000A-00000E35 session 129: DID 0001-000B-000004F1
Rows waited on:
Session 234: obj - rowid = 00007102 - AAAHEPAApAAALyqAAF
(dictionary objn - 28930, file - 41, block - 48298, slot - 5)
Session 129: obj - rowid = 00007100 - AAAHEAAAqAAALKeAAA
(dictionary objn - 28928, file - 42, block - 45726, slot - 0)
Information on the OTHER waiting sessions:
Session 234:
pid=11 serial=3956 audsid=122773 user: 23/PSFT
O/S info: user: psft, term: , ospid: 15567, machine: s2-btc
program: jdenet_k@s2-btc (TNS V1-V3)
application name: jdenet_k@s2-btc (TNS V1-V3), hash value=0
Current SQL Statement:
UPDATE PRODDTA.F43090 SET PCAN8=:BND1,PCITM=:BND2,PCLITM=:BND3,PCAITM=:BND4,PCMCU=:BND5,P
CCEFJ=:BND6,PCCXPJ=:BND7,PCCERS=:BND8,PCNROU=:BND9,PCTROU=:BND10,PCTFRQ=:BND11,PCORDS=:BND
12,PCTFRD=:BND13,PCLTDJ=:BND14,PCDAYE=:BND15,PCDAYL=:BND16,PCLTQP=:BND17,PCLTNM=:BND18,PCL
TTS=:BND19,PCALTD=:BND20,PCLLTD=:BND21,PCACST=:BND22,PCLCST=:BND23,PCNOOD=:BND24,PCQTYS=:B
ND25,PCQTYR=:BND26,PCQTYC=:BND27,PCQTYW=:BND28,PCQTYJ=:BND29,PCQTYA=:BND30,PCUODR=:BND31,P
CUREC=:BND32,PCUPTD=:BND33,PCUONT=:BND34,PCUEAR=:BND35,PCULAT=:BND36,PCAODR=:BND37,PCAREC=
:BND38,PCAPTD=:BND39,PCAONT=:BND40,PCAEAR=:BND41,PCALAT=:BND42,PCRONT=:BND43,PCREAR=:BND44
,PCRLAT=:BND45,PCURCD=:BND46,PCURDT=:BND47,PCURAT=:BND48,PCURAB=:BND49,PCURRF=:BND50,PCROT
P=:BND51,PCPOC=:BND52 WHERE ( PCAN8 = :KEY1 AND PCMCU = :KEY2 AND PCITM = :KEY3 )
Has anyome meet the same problem ?
Thanks in advance
Frédéric
