Re: Dealock on Rollback Segment (I think)

Just a quick confirmation from tests against 8.1.7 -

1. If a process has to wait for an ITL slot to become empty, you get a TX lock requested in mode 4 with an ID1/ID2 that matches the TX lock of the session it is waiting for.
2. If a chained row has to be updated, then Oracle needs an ITL entry in the block containing the head, and an ITL lock in the block containing the column that is to be updated (and it may need an ITL lock in all blocks that the row chains across anyway, but I haven't checked that - there are too many minor variations possible).

The test also showed up an (irrelevant) oddity. I was using an 8K block size, with pctfree = 0. When I tried to insert a row of about 8000 bytes to fill the block, I ended up with a total row length of 7,965 bytes, and a row chained INSIDE the same block.

My inserted row was:

    varchar2(4000), varchar2(4000), varchar2(90)

The result was
Row 1:

    varchar2( 3904), varchar2(4000), varchar2(90) Row 2:

    varchar2(96) - next rowid, same block row 1.

In other words the first 96 bytes of column 1 of my row had been inserted as one row in the block, with the rest of the row the target of a chain into the same block !!

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



Jonathan Lewis wrote in message
<978119851.10866.1.nnrp-12.9e984b29_at_news.demon.co.uk>...


>

>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

>