Re: how to test locks

Mladen Gogala <mgogala_at_earthlink.net> wrote in message news:<pan.2002.01.23.03.00.36.167637.1171_at_earthlink.net>...
> On Wed, 23 Jan 2002 02:15:26 -0500, Thomas wrote:
>
> > Hi everybody,
> >
> > I have a simple question. How can i test weather a record in a table is
> > locked by another user without causing another lock.
> >
> > regards
> > thomas
>
>
> That is very simple and I'm sure that the most of us here do that every day:
> you find the database block for that record (rowid contains file and block
> id), dump the block from oradebug and see whether the row header contains
> the pointer to a valid ITL in that block. The very first byte of the row
> header is the offset into the ITL table. ITLs are 23 bytes in size and
> contain a XID of the transaction that is interested in the block. After
> you do that, ask yourself whether the obtained information was worth the
> trouble. Oh yes, and exact descriptions on the database blocks can be
> obtained only in the Scott Gosset's "top gun" class.
> Oh, did I forget to mention that you have to check whether the block is in
> SGA (v$bh) and if it is, then you have to look there. ITLs in the disk
> block may contain invalid information and be scheduled for "delayed
> cleanup". It's a very simple thing indeed.

And Here is a Simple Answer...

Select those rows by saying.

Select * from blah where blah=blah for update nowait;

if this returns a ora -0054 then u'r Rows are locked else u'r free...

Regards,
Ganesh R Received on Wed Jan 23 2002 - 06:41:34 CST