Message-Id: <10583.114055@fatcity.com> From: "Steve Adams" Date: Tue, 8 Aug 2000 22:43:55 +1000 Subject: RE: locking issues This is a multi-part message in MIME format. ------=_NextPart_000_0093_01C0018A.2213A000 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit Hi Kevin, Please note my second email to Joe (attached), and consider rebuilding the table with INITRANS 2. The fact mentioned here that one of the statements is an insert supports my suggestion that this is an ITL entry shortage. Regards, Steve Adams http://www.ixora.com.au/ http://www.oreilly.com/catalog/orinternals/ http://www.christianity.net.au/ -----Original Message----- From: Toepke, Kevin M [mailto:ktoepke@cms.cendant.com] Sent: Tuesday, 8 August 2000 22:54 To: Multiple recipients of list ORACLE-L Subject: RE: locking issues All: I am the developer working with Joe on resolving the Duhveloper's problems (I didn't write the code, I'm just charged with fixing it. Ugh!) First of all, Steve Adams suggested we look at the PCTFREE on the tables involved. All have a PCTFREE of 10. First, Rachel's questions. > > you mean the fact that the trace file specifically states > that it is an > application problem doesn't hold any weight with the developers? Not with the Duhveloper that wrote the code. He still maintains it can't be his problem?!?!? He's turned the problem over to us stating to his manager that it is a DBA issue. > > do the users log in as separate accounts or does everyone log > in as the same > userid? Separate accounts. > > can you reproduce it with only one or two users on the > system, each of whom > is tracking what they are doing? It has been reproduced with exactly 2 users on the system each accessing the screen for a different Invoice. > > you are deadlocking on an insert statement???? are there > triggers on the > table that reference another table that could be locked? Deadlocking is occurring on and INSERT statement and an UPDATE statement (both on the same table). There are no triggers on the table. There is a single-column FK on the table. And it is properly indexed. The table that the deadlocking is occurring on is range partitioned by its Primary Key column. Kevin > >From: "Joseph S. Testa" > >Reply-To: ORACLE-L@fatcity.com > >To: Multiple recipients of list ORACLE-L > >Subject: locking issues > >Date: Mon, 07 Aug 2000 10:54:45 -0800 > > > >I'm hitting up against locking(including deadlock) issues. > Here is what > >we've done so far: > > > >using utllockt, dba_waiters, etc all of the normal delivered > stuff has > >been used. > > > >I'm sure there is a way to see what the values of bind > variables are, i > >think its an event, but i dont remember. > > > >According to the duhvelopers, its an oracle issue not an app issue(i > >disagree totally), short of me going thru their code line by > line(and i > >want to hurt someone since it has goto statements in a > stored proc), is > >there anything else i can do to determine the sql(on deadlock i get a > >trace file so i can see what is happening). > > > >At first it we thought it had to do with a bitmap index but > we converted > >it to a normal index and it didnt go away. > > > >here is part of the deadlock trace file(we're already ran > the event for > >60, to get more info). > > > >It appears that there is a share lock issue, anyone willing to take a > >stab at this? > > > > > >DEADLOCK DETECTED > >Current SQL statement for this session: > >INSERT INTO ATTEMPT_STATE ( > >ATTEMPT_STATE_OID,ATTEMPT_OID,ACTUAL_DATE,PLAN_DATE, > >SEQUENCE_NUMBER,NAME ) VALUES ( :b1,:b2,:b3, NULL ,:b4,:b5 ) > >----- PL/SQL Call Stack ----- > > object line object > > handle number name > >82feecd4 296 package body FTC.ATTEMPT_STATE_CHANGE_PKG > >82ff75f0 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 > >TX-0002001d-0003050c 12 20 X 13 > >18 S > >TX-00050022-000302db 13 18 X 12 > >20 S > >session 20: DID 0001-000C-00000002 session 18: DID > >0001-000D-00000002 > >session 18: DID 0001-000D-00000002 session 20: DID > >0001-000C-00000002 > >Rows waited on: > >Session 18: no row > >Session 20: no row > > > > > >thanks, joe > > > > > >-- > >Author: Joseph S. Testa > > INET: teci@oracle-dba.com > > > >Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 > >San Diego, California -- Public Internet access / > Mailing Lists > >-------------------------------------------------------------------- > >To REMOVE yourself from this mailing list, send an E-Mail message > >to: ListGuru@fatcity.com (note EXACT spelling of 'ListGuru') and in > >the message BODY, include a line containing: UNSUB ORACLE-L > >(or the name of mailing list you want to be removed from). You may > >also send the HELP command for other information (like subscribing). > > ______________________________________________________________ > __________ > Get Your Private, Free E-mail from MSN Hotmail at http://www.hotmail.com -- Author: Rachel Carmichael INET: carmichr@hotmail.com Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists -------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru@fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Author: Toepke, Kevin M INET: ktoepke@cms.cendant.com Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists -------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru@fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). ------=_NextPart_000_0093_01C0018A.2213A000 Content-Type: message/rfc822 Content-Transfer-Encoding: 7bit Content-Disposition: attachment From: "Steve Adams" To: "Joseph S. Testa" Cc: "Multiple recipients of list ORACLE-L" Subject: RE: locking issues Date: Tue, 8 Aug 2000 09:54:30 +1000 Message-ID: MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit X-Priority: 3 (Normal) X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook IMO, Build 9.0.2416 (9.0.2910.0) X-MimeOLE: Produced By Microsoft MimeOLE V5.00.2314.1300 Importance: Normal In-Reply-To: <398F4A60.AA53F7B7@oracle-dba.com> Hi Joe, You may have a PCTFREE of just 1 or 0 that is causing this, or you may have row lengths such that is it possible to fill the block entirely with a higher PCTFREE. In the latter case it may be better to rebuild with INITRANS 2 rather than increasing PCTFREE. However, in general the problem is an inadequate PCTFREE setting. Regards, Steve Adams http://www.ixora.com.au/ http://www.oreilly.com/catalog/orinternals/ http://www.christianity.net.au/ -----Original Message----- From: jtesta@mail.the-word.net [mailto:jtesta@mail.the-word.net]On Behalf Of Joseph S. Testa Sent: Tuesday, 8 August 2000 9:47 To: Steve Adams Cc: Multiple recipients of list ORACLE-L Subject: Re: locking issues Steve, we were thinking that but looking at oracle paper on it, didnt really point that as the issue. out initrans is 1 and maxtrans is 255. so in theory what would the pctfree be that would be causing this as an issue? thanks, joe Steve Adams wrote: > > Hi Joe, > > The fact that you are waiting for a shared TX lock indicates that the > duhvelopers are right. It is an ITL entry shortage. Look for inadequate > PCTFREE settings. This is treated in more detail on page 48 of my book. > > Regards, > Steve Adams > http://www.ixora.com.au/ > http://www.oreilly.com/catalog/orinternals/ > http://www.christianity.net.au/ > > -----Original Message----- > From: Joseph S. Testa [mailto:teci@oracle-dba.com] > Sent: Tuesday, 8 August 2000 4:55 > To: Multiple recipients of list ORACLE-L > Subject: locking issues > > I'm hitting up against locking(including deadlock) issues. Here is what > we've done so far: > > using utllockt, dba_waiters, etc all of the normal delivered stuff has > been used. > > I'm sure there is a way to see what the values of bind variables are, i > think its an event, but i dont remember. > > According to the duhvelopers, its an oracle issue not an app issue(i > disagree totally), short of me going thru their code line by line(and i > want to hurt someone since it has goto statements in a stored proc), is > there anything else i can do to determine the sql(on deadlock i get a > trace file so i can see what is happening). > > At first it we thought it had to do with a bitmap index but we converted > it to a normal index and it didnt go away. > > here is part of the deadlock trace file(we're already ran the event for > 60, to get more info). > > It appears that there is a share lock issue, anyone willing to take a > stab at this? > > DEADLOCK DETECTED > Current SQL statement for this session: > INSERT INTO ATTEMPT_STATE ( > ATTEMPT_STATE_OID,ATTEMPT_OID,ACTUAL_DATE,PLAN_DATE, > SEQUENCE_NUMBER,NAME ) VALUES ( :b1,:b2,:b3, NULL ,:b4,:b5 ) > ----- PL/SQL Call Stack ----- > object line object > handle number name > 82feecd4 296 package body FTC.ATTEMPT_STATE_CHANGE_PKG > 82ff75f0 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 > TX-0002001d-0003050c 12 20 X 13 18 > S > TX-00050022-000302db 13 18 X 12 20 > S > session 20: DID 0001-000C-00000002 session 18: DID > 0001-000D-00000002 > session 18: DID 0001-000D-00000002 session 20: DID > 0001-000C-00000002 > Rows waited on: > Session 18: no row > Session 20: no row > > thanks, joe > > -- > Author: Joseph S. Testa > INET: teci@oracle-dba.com > > Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 > San Diego, California -- Public Internet access / Mailing Lists > -------------------------------------------------------------------- > To REMOVE yourself from this mailing list, send an E-Mail message > to: ListGuru@fatcity.com (note EXACT spelling of 'ListGuru') and in > the message BODY, include a line containing: UNSUB ORACLE-L > (or the name of mailing list you want to be removed from). You may > also send the HELP command for other information (like subscribing). -- Joe Testa http://www.oracle-dba.com Oracle 8i documentation online http://www.oracle-dba.com/oracle-docs ------=_NextPart_000_0093_01C0018A.2213A000 Content-Type: message/rfc822 Content-Transfer-Encoding: 7bit Content-Disposition: attachment From: "Steve Adams" To: "Joseph S. Testa" , "Multiple recipients of list ORACLE-L" Subject: RE: locking issues Date: Tue, 8 Aug 2000 07:35:07 +1000 Message-ID: MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit X-Priority: 3 (Normal) X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook IMO, Build 9.0.2416 (9.0.2910.0) X-MimeOLE: Produced By Microsoft MimeOLE V5.00.2314.1300 Importance: Normal In-Reply-To: Hi Joe, The fact that you are waiting for a shared TX lock indicates that the duhvelopers are right. It is an ITL entry shortage. Look for inadequate PCTFREE settings. This is treated in more detail on page 48 of my book. Regards, Steve Adams http://www.ixora.com.au/ http://www.oreilly.com/catalog/orinternals/ http://www.christianity.net.au/ -----Original Message----- From: Joseph S. Testa [mailto:teci@oracle-dba.com] Sent: Tuesday, 8 August 2000 4:55 To: Multiple recipients of list ORACLE-L Subject: locking issues I'm hitting up against locking(including deadlock) issues. Here is what we've done so far: using utllockt, dba_waiters, etc all of the normal delivered stuff has been used. I'm sure there is a way to see what the values of bind variables are, i think its an event, but i dont remember. According to the duhvelopers, its an oracle issue not an app issue(i disagree totally), short of me going thru their code line by line(and i want to hurt someone since it has goto statements in a stored proc), is there anything else i can do to determine the sql(on deadlock i get a trace file so i can see what is happening). At first it we thought it had to do with a bitmap index but we converted it to a normal index and it didnt go away. here is part of the deadlock trace file(we're already ran the event for 60, to get more info). It appears that there is a share lock issue, anyone willing to take a stab at this? DEADLOCK DETECTED Current SQL statement for this session: INSERT INTO ATTEMPT_STATE ( ATTEMPT_STATE_OID,ATTEMPT_OID,ACTUAL_DATE,PLAN_DATE, SEQUENCE_NUMBER,NAME ) VALUES ( :b1,:b2,:b3, NULL ,:b4,:b5 ) ----- PL/SQL Call Stack ----- object line object handle number name 82feecd4 296 package body FTC.ATTEMPT_STATE_CHANGE_PKG 82ff75f0 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 TX-0002001d-0003050c 12 20 X 13 18 S TX-00050022-000302db 13 18 X 12 20 S session 20: DID 0001-000C-00000002 session 18: DID 0001-000D-00000002 session 18: DID 0001-000D-00000002 session 20: DID 0001-000C-00000002 Rows waited on: Session 18: no row Session 20: no row thanks, joe -- Author: Joseph S. Testa INET: teci@oracle-dba.com Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists