Jonathon, This produces ITL waits for sessions Y and Z; but this is not deadlock. The deadlock occurs due to a situation where the Session 1 waits for something to finish in Session 2, which in turn waits for Session 1 AND, this is important, Oracle detects it and kills one of them, rolling back the changes, making a deadlock detected error. Is this not the true error message that occured in the original thread?
In your example, sessions Y and Z will wait indefinitely until X commits or rolls back. This is not going to be detected by Oracle nor killed by it. So you wouldn't see a message DEADLOCK DETECTED in alert log. Therefore setting INITRANS higher is not going to help at all. Rather the application logic should be checked to remove a real locking conflict. Am I correct, or am I missing something here? Arup Nanda <Original Post> Hi I have been noticing some times following error with one table during update. DEADLOCK DETECTED Current SQL statement for this session: "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:" Is chaning of INITTRANS would help ? Thx -Seema </Original Post> ----- Original Message ----- To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: Friday, December 20, 2002 12:10 PM > > Set maxtrans to 2 on a table. > Insert three rows into the same block > and commit. > > Use three sessions to update one row > each. The third transaction has to wait > for one of the other two transactions to > commit, as there are insufficient ITL > (interested transaction list) entries for > three concurrent transactions on the same > block. > > Now repeat the experiment with: > rows A1, B1, C1 in block 1 > rows A2, B2, C2 in block 2. > > Session X updates row A1 and A2, > Session Y updates row B1 > Session Z updates row C2 > Session Y tries to update row B2 > and waits because the ITL is full > Session Z tries to update row C1 > and waits because the ITL is full > > With a little luck, Y will be waiting for Z > and Z will be waiting for Y (i.e. DEADLOCK) > but you may have to fiddle with a more complex > example, as both X and Y might end up waiting > for A. > > > It's easier to do this in 8.1 because MAXTRANS > can be set to 1, so you need only use two > sessions and two rows per block. > > > > Regards > > Jonathan Lewis > http://www.jlcomp.demon.co.uk > > Coming soon a new one-day tutorial: > Cost Based Optimisation > (see http://www.jlcomp.demon.co.uk/tutorial.html ) > > Next Seminar dates: > (see http://www.jlcomp.demon.co.uk/seminar.html ) > > ____England______January 21/23 > > > The Co-operative Oracle Users' FAQ > http://www.jlcomp.demon.co.uk/faq/ind_faq.html > > > > > > -----Original Message----- > To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> > Date: 20 December 2002 16:56 > > > >Jonathan, > > What do you mean by ITL starvation? And how would it result in a > >deadlock? > > > >Dan Fink > > > > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.net > -- > Author: Jonathan Lewis > INET: [EMAIL PROTECTED] > > Fat City Network Services -- 858-538-5051 http://www.fatcity.com > San Diego, California -- Mailing list and web hosting services > --------------------------------------------------------------------- > To REMOVE yourself from this mailing list, send an E-Mail message > to: [EMAIL PROTECTED] (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). > -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Arup Nanda INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (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).
