Re: Row level locks and Sessionid! - thankyou Thomas (Kyte)

On Sun, 11 Oct 1998 12:48:08 GMT, tkyte_at_us.oracle.com (Thomas Kyte) wrote:

>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.
 Thankyou Thomas - at last sanity is restored!

Best regards,

Clive (Premium On-site Support UK)

--
Clive Bostock