Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: How to know which row is locked ?

Re: How to know which row is locked ?

From: <Clive>
Date: 1998/10/04
Message-ID: <36172801.257967188@news.demon.co.uk>#1/1

On Sat, 3 Oct 1998 13:40:31 -0700, Jeremiah Wilton <jeremiah_at_wolfenet.com> wrote:

>
>On Sat, 3 Oct 1998,Clive Bostock wrote:
>
>> I'm afraid it's not possible. The locking is done inside the database
>> block header. This is what make Oracle so scalable. Unfortunatley
>> there is no way (supplied by Oracle) to determine what row is locked.
>
>Nothing is impossible! >:-)
>
>It seems to me that the only time that a lock is a problem is if someone
>else gets caught up waiting for it. When that happens you can find out
>the sid of who is waiting by looking at v$session_wait, for a session
>waiting on an 'enqueue' event.
>
>Then you can go look at v$session for that sid, and you will see columns
>in there for ROW_WAIT_FILE#, ROW_WAIT_BLOCK# and ROW_WAIT_ROW#. Then you
>can use the sprintf function to return the rowid thusly:
>
>$rowid = sprintf("%08X.%04X.%04X", $block, $row, $file);
>
>So, for instance, if you have found a session waiting for the lock:
>
>13:35:40 SQL> r
> 1 select ROW_WAIT_OBJ#, ROW_WAIT_FILE#, ROW_WAIT_BLOCK#, ROW_WAIT_ROW#
> 2 from v$session
> 3* where sid = 33
>
>ROW_WAIT_OBJ# ROW_WAIT_FILE# ROW_WAIT_BLOCK# ROW_WAIT_ROW#
>------------- -------------- --------------- -------------
> 1679 288 1689 16
>
>You could use perl or anything else that can use the standard sprintf
>function to return the rowid:
>
>unixhost% perl -e 'print sprintf("\n%08X.%04X.%04X\n\n",1689,16,288);'
>
>00000699.0010.0120
>
>unixhost%
>
>If you don't have something this nifty, you can use the windows calculator
>in "scientific" mode to turn the decimal numbers from v$session into hex
>to form the rowid in the form "block.row.file".
>
>--
>Jeremiah Wilton http://www.wolfenet.com/~jeremiah
>

That's a different situation, you are having to create a condition of contention to identify the row. The original problem was not regarding blocking but identifying which non blocking row locks a process has. At least that's the way I read it. I don't think that it is practicable for Oracle to keep track of stats regarding all locks as you would be entering into the arena of a lock pool - limiting scalability.

Regards,

Clive. Received on Sun Oct 04 1998 - 00:00:00 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US