Re: Dealock on Rollback Segment (I think)

Just my two bits:

Recreating the indexes (or any indexes AT ALL) are a BAD idea in this case. If the app does in fact update every row then index maint. is going to be HORRENDOUS. As far as I know MAXTRANS still defaults to 255, so that's probably not it unless the consultant specifically over-rode it (it might help to see the table creation script)

My guess is that the partitioning did not work on ROWID range like the consultant thought it did. Again, it would help to see the table creation ...

Dave

"Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk> wrote in message news:978039211.5428.0.nnrp-07.9e984b29_at_news.demon.co.uk...
>
> 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
>
>
>
Received on Thu Dec 28 2000 - 20:15:31 CST