Re: Dealock on Rollback Segment (I think)

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?

Thanks Again.
Patrick

PS. We reran the code in test and it worked fine once again. Since this is production, the developers don't want to change the code (familiar story..). We are looking for the underlying source of the problem, which would seem to a difference between the production environment and the test environment.

In article <978101892.24546.0.nnrp-08.9e984b29_at_news.demon.co.uk>,   "Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk> wrote:
>
> The lock on the rollback segment header is just
> the cute way that Oracle queues behind the
> blocking transaction - in effect it attempts to
> acquire a lock on the transaction entry that the
> blocking transaction has put in the rollback segment
> header block. When the blocking transaction
> commits or rolls back, the lock can be acquired
> and the transaction can then proceed with the
> work needed to acquire the correct image of the
> targeted data block.
>
> As a demonstration, create a sample table
> from session 1, insert a row into the table and commit;
> from session 1 update the row, but don't commit
> from session 2 insert a second row into the table, but don't
 commit
> from session 2 update the FIRST row - the process will hang.
>
> At this point (from a 3rd session) you should see 3 TX locks:
> One lock will be from the first session, with LMODE=6
> One lock will be from the second session with LMODE = 6
> These two locks will be for different ID1/ID2 values.
> The third lock will be from the second session with REQUEST=6
> The ID1/ID2 values for this lock will match those from the FIRST
> session.
>
> --
> 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
 <92i6s1$bv1$1_at_nnrp1.deja.com>...
> >
> > Additionaly, according to OEM Top Sessions, the
> >lock causing the problem is being held on a rollback segment. I am
 not
> >sure if that is normal or if it could provide a clue as to what the
> >problem is.
> >
>
>

Sent via Deja.com
http://www.deja.com/ Received on Fri Dec 29 2000 - 11:46:47 CST