Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Re: locking issues

Re: locking issues

From: <MTPConsulting_at_aol.com>
Date: Tue, 8 Aug 2000 11:54:18 EDT
Message-Id: <10583.114091@fatcity.com>


I think it should be pointed out that by far the most common reason for deadlocks is application errors. Process A locks table/row 1 (implictly or explictly), process B locks table/row 2. Then process A attempts to lock table/row 2 and waits and process B attempts to lock table/row 1 and waits. Sometimes this is very subtle, through foreign keys without indexes or thru triggers or procedures/functions calling other procedure/functions, but in my experience, most deadlock problems are like this. Train your developers to always ALWAYS access tables in exactly the same sequence within a transaction.

I don't have a copy of Steve's book to hand, so I don't understand the issue being raised about PCTFREE settings. But, I haven't run into this before and suspect it's not a frequent occurance. I'll be interested in seeing if this does solve this particular issue.

Marc Perkowitz
MTP Systems Consulting, Ltd.

In a message dated 8/8/2000 10:20:55 AM Central Daylight Time, lwang_at_messagemedia.com writes:

<< Steve,  

 What is the name of you book? where is selling it?  My shop gets several dead lock every day.  

 Linda  

-----Original Message-----

 Sent: Monday, August 07, 2000 4:44 PM
 To: Multiple recipients of list ORACLE-L    

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

 Sent: Tuesday, 8 August 2000 4:55
 To: Multiple recipients of list ORACLE-L    

 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 )

Received on Tue Aug 08 2000 - 10:54:18 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US