| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: Lock Monitor Question
On Wed, 28 Jan 1998 00:01:57 -0600, Bill Ennis <ennis_at_starnetusa.com> wrote:
>Hi,
>
>I have a question regarding monitoring locks at the row level within
>Oracle. I'm using the v$lock table and am able to reference the
>all_objects table (with v$lock.id1 = all_objects.object_id) and find
>out basically what resource is being locked at a high level of
>granularity (i.e. Table name). However, I have been unable to track this
>down to the row level.
>
>I have a test program that performs a SELECT ... FOR UPDATE. It turns
>out that the select returns 2 rows. However, when I query the v$lock
>table for locks for this user I only see 1 exclusive lock. I would
>expect
>to see one exclusive lock for each row in the query set. Am I looking
>in the wrong place or is this something that is not available?
>
>Thanks,
>Bill
Oracle doesn't maintain a complete list of locks at the row level anywhere. That would be really expensive to do. For every row locked, we would have to allocate some ram and maintain a structure of locks. We support an (theoretically) infinite number of row level locks, we couldn't store them all in memory. Additionally, if I wanted to lock a row, I'd have to goto this list and inspect it to find if the row was already locked, another slowdown. This structure of locks would be a point of serialization as well -- 2 people can't really modify it easily at the same time.
Oracle maintains row level locks right with the data. In order to find if a row is locked, we goto the row. If its locked, we find out right then and there (and optionally will wait for it to become available). We don't goto a list to see if it is locked and then goto the data -- we just goto the data. Locks can sort of be considered an attribute of the row itself.
You cannot find out what rows in a table are locked unless you yourself goto that row and try to lock it.
Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Bethesda MD
http://govt.us.oracle.com/ -- downloadable utilities
Anti-Anti Spam Msg: if you want an answer emailed to you, you have to make it easy to get email to you. Any bounced email will be treated the same way i treat SPAM-- I delete it. Received on Wed Jan 28 1998 - 00:00:00 CST
 |
 |