| 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: Detecting the user that has a lock on a particular row. ??
A copy of this was sent to "Rodney Barrett" <rbarrett_at_solutionpt.com> (if that email address didn't require changing) On Wed, 4 Feb 1998 13:07:58 -0600, you wrote:
>Can anyone help me figure out how to detect what oracle username has a "for
>update" lock on a particular lock on a row in a particular table?
>
>In other Words, I would like to write a stored function that accepted a
>TABLE_NAME and PRIMARY KEY. The function would then return the Oracle
>USERNAME that has that row locked for update purposes. It would return a
>null, if no one has that particular row locked.
>
>Thanks in advance for any help.
>
>Rodney
>
>
You won't be able to tell who, you can tell if it is locked but not by who -- and you'll only be able to tell if the row is locked if you are willing to you yourself lock it (that is, you can't just ask if row such and such is locked -- you can try to lock it and fail, hence discovering it is locked or try to lock it and succeed).
The reason you can't tell "who" has it is that you need to be blocked on that row to be able to see the session that is blocking you. If you are blocked however, you cannot 'see' who is blocking you (your blocked). If you are not blocked, no one is blocking you -- hence you won't see them. You need three sessions to see blockers and blockees. One to get a lock, another to attempt to get the same row locked, and another which can see both the blocker and the waiter.
The only way to tell if a particular row is locked is to attempt to lock it yourself. You would "select * from T where PK = :x for update nowait". If it succeeds, it was not locked (but now it is, by you). If it fails, someone has it locked.
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 Feb 04 1998 - 00:00:00 CST
 |
 |