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_at_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_at_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_at_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