Re: Dealock on Rollback Segment (I think)

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.

>