| 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. ??
Hello All,
Thomas's answer is correct, but I think a little more explanation would help, and there is a partial solution that was not mentioned.
There is no persistent record in the sga of row-level locking. If a session intends to modify a row, it gets the block in current mode and examines the interested transaction list in the block header. For each ITL entry, it checks the in-core transaction table (x$ktcxb) for the status of the transaction - if it is still active it then consults the undo record in the rollback segment to see whether the changes affect any of the same rows - if so it enqueues itself on the TX enqueue for the other transaction and waits. There is no row-level lock state object or enqueue. So as Thomas has said the only way that you can determine this is by following the same process (that is, trying to take the lock yourself). If you were really keen, you could write a stored procedure to dump the block, read the ITLs from the trace file, the dump the undo header for the status of the transaction, and then the undo block itself for the row-level information, but it would be a very ugly bit of code.
On the positive side, there is a partial solution that may be of some help. You can check to see whether anyone has a DML lock on the table. DML locks are type TM in v$lock, and the id2 value is the object number from obj$. If nobody has a DML lock on the table in question, then you can be sure that nobody and a row-level lock on the row. However, if someone does have a DML lock, you will have to go to the lengths mentioned above (and below) to find out whether there is a row-level lock on the row you are interested in.
Hope this heps,
Regards,
Steve Adams
steveadams_at_acslink.net.au
>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.
Received on Wed Feb 11 1998 - 00:00:00 CST
 |
 |