Re: Nuts and bolts about locking...

In article <wcurtis.2.00AD19DB_at_mindspring.com>

           wcurtis_at_mindspring.com "William Curtis" writes:

: Can anyone give an indepth explanation of exclusive row locking, rollback
: segments and slots, latches that are acquired. The resource ID 1&2 refer
: to a rollback segment and slot. When a second resource is attempting to
: modify that same row, why does SQL*DBA indicated contention on the
: resource ID 1& 2 (rollback segment & slot information?)

Based on dumping blocks and cross-referencing v$ tables with block data I think the following is probably fairly close.

It follows Oracle's Rule Zero: Never do now what you can postpone 'til later. In this case, the aim is for Oracle to do as little work as possible when the user issues a commit or rollback.

Each row has an interested transaction marker written to its header that points to the transaction entry in the transaction list (ITL) in that block. (The initial reserved space for the ITL is defined by INITRANS).

The transaction entry lists the transaction ID that can be looked up in V$TRANSACTION and the address of the bit of rollback segment where reversals to the transaction's changes start for that block (which is NOT necessarily the same as the rbs/slot that appears in the MONITOR LOCKS screen, the entry in V$TRANSACTION for the _starting_ rbs/slot is the thing that appears on MONITOR LOCK).

When the transaction commits or releases, NOTHING changes on the data block: the transaction marker stays on the row, the ITL entry stays in place.

If another user tries to read the row, it checks to see if there is a transaction marker on it; if there is then it checks things like SCNs on the transaction to sort out read-consistency etc, and where to find the rollback if that is needed.

If another user tries to LOCK the row, and the previous transaction is still live, then the second user has to wait: but since the row will only become free when the first transaction completes, the simplest thing to wait on is the first transaction, and this is represented within the database as rbs/slot where the transaction has started writing. So the first transaction has a write lock on that object and the second transaction simply waits on that write lock -- which is what you see in the MONITOR LOCK screen.

The elegance of this approach is two-fold:

   First Oracle needs only one lock for a transaction, irrespective    of how many rows are being affected.

   Second, a commit or rollback requires virtually no (database)    activity to take place.

Of course, it does mean that the 'shared row-level read locks for foreign key consistency' discussed in another thread cannot simply be an extension of the exclusive write-lock mechanism. But that's another story.

Hope this is of interest.

-- 
Jonathan Lewis