RE: please, locks are at block or row level.?

TX locks take locks against rows, not blocks. When you execute DML against a table, a TX enqueue (or lock) is implicitly allocated for the duration of your transaction. That transaction points to a rollback segment header, which in turn will point to space in the rollback segment where=20 the before images of the data affected by your transaction will be recorded. Now, as particular data blocks are affected, an ITL xlot in the data block's transaction layer is allocated and pointed to that rollback segment slot. So, now we know that this transaction is "showing an interest" in this data block. Finally, as a particular row in the data block is updated, the entry in the block's row table is updated to point to that particular ITL slot. So, in this way, locks can be enforced at the row-level. Note some important points about this model: 1.) There is no "master list" of locked rows. This is good, because it's a key Oracle's ability to scale, and to not have to deal with lock escalation, which other databases need to=20 deal with.
2.) It fits very well into the whole read consistency model, with the block pointing to the rollback segments that are needed to provide previous snapshots of the data. 3.) Note that with "large" updates, simply marking the rollback segment header as committed is good enough. The blocks do not have to be revisited. The next time the block is visited, Oracle will check if the pointer in the ITL still points to a valid, uncommitted transaction. If the transaction is committed,=20 Oracle will "cleanout" the block at that time.

I could go on into block cleanout details, and failure, etc, but I think this is long enough.

Finally, for your 3rd question, see DBMS_ROWID package.

Hope that helps,

-Mark

-----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org]On Behalf Of Juan Carlos Reyes Pacheco
Sent: Monday, October 04, 2004 11:50 AM
To: oracle-l_at_freelists.org
Subject: please, locks are at block or row level.?

Hi 3 questions please, if you can lend me your time one moment, 1. I have a confusion, locks are at block level or row level 2. Where is the overhead if they are in the same block, and I lock at = the
same time all rows in the same block?
3. how can I know in which block is a row?

Thanks :)
=20
=20

Juan Carlos Reyes Pacheco
OCP

--
http://www.freelists.org/webpage/oracle-l
--
http://www.freelists.org/webpage/oracle-l