Re: Dealock on Rollback Segment (I think)

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?

>