Re: Row level locks and Sessionid!

A copy of this was sent to Ram Pudupet <rpudupet_at_earthlink.net> (if that email address didn't require changing) On Fri, 09 Oct 1998 22:56:26 -0700, you wrote:

>H'yall,
>I had posted a question to this forum last week about locking issues and
>
>obtained responses that were really helpful. Let me continue this
>discussion further.
>Problem Stmt: If I try to lock a row, the stmt errors out if some other
>user has locked it. How do I obtain information about the sesson that
>has locked the row?
>Relevant info and issues: If I specify a wait option when requesting the
>
>lock, I can get the session information from the v$session and v$lock
>tables. However this would mean that the original session cannot query
>these tables because it is waiting on a lock. If I use a nowait, there
>is no wait information in the v$lock and v$ session tables. Just getting
>
>the object# wont do because many sesisons can have locks on different
>rows in the same table.
>I can get the rowid when I request a lock with the nowait option. I need
>
>to translate this to the information in the v$lock, and/or v$transaction
>
>tables.
>Has anyone had this problem before and if so what are my options? Where
>can I get information about the individual columns in the v$lock,
>v$session , and v$transaction tables?? There are a bunch of columns in
>there that I know can be used to get the info that I'm looking for.

Oracle does not keep a table of "session and rows they have locked" anywhere. the lock information in Oracle for row data is stored directly on the database block itself in the transaction headers and is not accessible. The best you can do is list the sessions that have locks on rows in the table (all sessions) but given a row -- unless you use 3 sessions (or 2 sessions, the blocker can see who they are blocking and can see what rows they are waiting on) -- you cannot find out definitively who has that row locked.

The sess

>Heeelllp!
>Ram
 

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Herndon VA

--
http://govt.us.oracle.com/    -- downloadable utilities
 
----------------------------------------------------------------------------
Opinions are mine and do not necessarily reflect those of Oracle Corporation
 
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.