Re: Dealock on Rollback Segment (I think)

Jus an FYI in case you are interested. I rebuilt the table, increasing initrans to the number of process being run,(overkill I am sure, but we need to get this done ASAP) and I increased pctfree to prevent the chaining/migration. I will post the results later this week, after they run the the month-end procesess.

Patrick

In article <978119851.10866.1.nnrp-12.9e984b29_at_news.demon.co.uk>,   "Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk> wrote:
>
> Sorry,
>
> My 'quick example' was set up to show proper row
> locking - I have a vague memory that mode 4 will
> become visible when an ITL deadlock occurs. It's
> a little messier to test that of course, as you have
> to get some data blocks full and clean before trying
> to get the conflict.
>
> Since none of the columns being updated is
> indexed, the deadlock is not due to the
> indexes.
>
> The point about the chained rows is VERY interesting.
> Although your processes are not colliding on rows
> (and probably even blocks because of your rowid
> range methods - I am assuming that your consultant
> did this the easy way, and covers block ranges from
> row 0 to row 32767) you could still have ITL conflicts
>
> Assume a row in block X chains into block Y, and
> process A wants to update the row, but process B
> is responsible for the rows that START in block Y.
>
> You would have to be a bit unlucky to get a deadlock
> unless the chain count was pretty high - but it is
> possible. I suppose the granularity of the rowid ranges,
> and the distribution algorithm would also affect the risk.
>
> I'll look into it.
>
> --
> 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
 <92iim5$lr8$1_at_nnrp1.deja.com>...
> >Jonathan,
> > Thanks for the explanation on the rollbeck segment header lock.
 That
> >helps. But I am still confused on the deadlock. The lock mode in the
> >deadlock is 6 for both objects. The requested mode is 4, not 6.
 From
> >everything I can find on MetaLink, this indicates what you had said
 in a
> >earlier post, that the deadlock could be due to insufficient 'ITL'
 slots
> >in a block. But I am still somewhat confused.
> >
> >I spoke to the developers and glanced at the code. None of the
 columns
> >being updated are indexed, so the indexes shouldn't be directly
 involved
> >in the deadlock. I manually analyzed the table in test and
 production.
> >The chain_cnt in production is 6 times larger than chain_cnt in test.
> >Does this mean that more blocks in production are full than are full
 in
> >test? Could this inidcate the problem? If not, do you have other
 ideas
> >on where to look?
> >
>
>

Sent via Deja.com
http://www.deja.com/ Received on Tue Jan 02 2001 - 09:10:05 CST