Re: ITL Deadlock Example?

I thought I was pretty careful in performing the steps for my test case--I had 2 rows in each of 3 blocks and tested with only two of the blocks using two different sessions exactly as described.

To answer your question, in the deadlock graph I am trying to reproduce and understand, the session is holding an exclusive (X) and waiting for a share (S).

The DML being performed is a simple update. No function or procedural calls are involved. No triggers exist on the table. No parallel DML, autonomous transactions, or distributed transactions are involved. The table is not partitioned.

I saw posts on this kind of scenario on the Ixora site and in Google and that is what led me to believe it is an ITL shortage issue. The even stranger thing is that the ORA-0060 error has been raised about 10 times in the past 3 days and the trace file has the SAME name each time. As of today the error has occurred once and the trace file name has finally changed.

My environment is SunOS 5.8, 8.1.7.3 (64-bit), OLTP database (~60Gb) with ~100 concurrent users.

Thanks again.
-w

• Original Message --- To: Multiple recipients of list ORACLE-L <ORACLEL @fatcity.com>

>
>Something has gone wrong with your test, you've
>produced a normal data deadlock. The ITL deadlock
>graph you should see from my description would have
>two lines with the cross-over on an X lock and an S
>lock on each line.
>
>But the deadlock graph you are trying to pin down
>does look very odd - as you say it does look like
>a self-deadlock.
>
>Because of the usual mess-up from email, I can't
>tell if your session is holding an exclusive and
>waiting for a share, or holding a share and waiting
>for an exclusive, though.
>
>There are seven different reasons for TX/4 lock
>waits (hence deadlocks) that I've found so far -
>but I don't think I've seen one quite like this.
>Are you using any of:
> distributed transaction
> partitioned tables
> parallel DML
> Autonomous transactions
>
>
>Jonathan Lewis
>http://www.jlcomp.demon.co.uk
>
>Author of:
>Practical Oracle 8i: Building Efficient Databases
>
>Next Seminar - Australia - July/August
>http://www.jlcomp.demon.co.uk/seminar.html
>
>Host to 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 <ORACLE-
L_at_fatcity.com>
>Date: 22 May 2002 16:15
>
>
>|Thanks for the replies (K,Jonathan,Anjo). I'm
getting
>|deadlocks to occur but they're not producing the
>|deadlock graphs I was expecting to see.
>|
>|I'm looking to reproduce the scenario that will
>|generate the following kind of deadlock graph--where
>|it looks like a self-deadlock:
>|
>| ---------Blocker(s)-------- -

--


>|------Waiter(s)---------

>|Resource Name          process session holds waits  

>|process session holds waits

>|TX-00050032-00002143        22      23     

>|X             22      23           S

>|session 23: DID 0001-0016-000017E7      session 23: 

>|DID 0001-0016-000017E7

>|Rows waited on:

>|Session 23: no row

>|

>|But, the deadlock graph that my testing is 

generating 


>|looks like:

>|                      ---------Blocker(s)--------  --

--


>|-----Waiter(s)---------

>|Resource Name          process session holds waits  

>|process session holds waits

>|TX-00030052-00001fb9        11      11     

>|X             12      15           X

>|TX-00040058-000023ef        12      15     

>|X             11      11           X

>|session 11: DID 0001-000B-00000002 session 15: DID 

>|0001-000C-00000002

>|session 15: DID 0001-000C-00000002 session 11: DID 

>|0001-000B-00000002

>|Rows waited on:

>|Session 15: obj - rowid = 0000153E - 

AAABU+AAFAAAALIAAA


>|Session 11: obj - rowid = 0000153E - 

AAABU+AAFAAAAACAAA


>|

>|

>|It is my understanding that the deadlock graph I am 

>|trying to reproduce is caused by ITL shortage 

>|deadlocks. I have verified, via 

>|dbms_rowid.rowid_block_number(), that I am updating 

>|rows in different blocks.

>|

>|Thanks again.

>|-w

>|

>

>

>-- 

>Please see the official ORACLE-L FAQ: 

http://www.orafaq.com


>-- 

>Author: Jonathan Lewis

>  INET: jonathan_at_jlcomp.demon.co.uk

>

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


-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Walter K
  INET: ORA1034_at_sbcglobal.net

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