Re: Dealock on Rollback Segment (I think)

I suspect this is down to ITL deadlocks on index blocks. Although the table updates can be isolated quite easily by using rowid ranges (I assume this is what your consultant did) you may still find that index entries for 8 different rows, being updated by 8 different processes, are all in the same index leaf block.

If this happens, and you don't have enough space for Oracle to create 8 separate transaction entries in the leaf block, one process has to wait for another process to commit and release an ITL (interested transaction list) entry.

If this happens in a number of different blocks, you are eventually likely to end up with a deadlock on the ITL, even though no two processes are interested in the same rows.

In this sort of circumstance, you need to recreate the relevant indexes with INITRANS=8 - to match the 8 concurrent processes you are running.

--
Jonathan Lewis
Yet another Oracle-related web site:  http://www.jlcomp.demon.co.uk

Practical Oracle 8i:  Building Efficient Databases

Publishers:  Addison-Wesley
See a first review at:
http://www.ixora.com.au/resources/index.htm#practical_8i
More reviews at: http://www.jlcomp.demon.co.uk/book_rev.html



buckeye714_at_my-deja.com wrote in message <92ftga$jq4$1_at_nnrp1.deja.com>...


>One of the 8 process get's an ora-00060.  I have included the Deadlock

>graph from the trac file below:

>

>*** 2000-12-28 09:13:15.153

>*** SESSION ID:(27.30638) 2000-12-28 09:13:15.088

>DEADLOCK DETECTED

>Current SQL statement for this session:

>UPDATE CUST SET

>CONTRIB_01=:b1,CONTRIB_02=:b2,CONTRIB_03=:b3,GROSS_CONTRIB=:b4,PROF=:b4,

>CACC_01=:b1,CACC_02=

>:b2,CACC_03=:b3,TOTAL_CACC=:b9,EQUITY_01=:b10,EQUITY_02=:b11,EQUITY_03=:

>b12,TOTAL_EQUITY=:b13,ROE_01=:b14,RO

>E_02=:b15,ROE_03=:b16,TOTAL_ROE=:b17,ROE_SEGMENT=:b18,PRI_DEP_BAL=:b19,P

>RI_LN_BAL=:b20 WHERE ROWID = :b21

>----- PL/SQL Call Stack -----

>  object      line  object

>  handle    number  name

>b3730844       135  package body CPDEV.UPD_AGG_CUST

>b371b0f0         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-00060026-0000a001        35      27     X             36      25

>S

>TX-0004002b-00009e12        36      25     X             35      27

>S

>session 27: DID 0001-0023-00000002      session 25: DID

>0001-0024-00000002

>session 25: DID 0001-0024-00000002      session 27: DID

>0001-0023-00000002

>Rows waited on:

>Session 25: no row

>Session 27: no row