Re: how to test locks

Mladen Gogala <mgogala_at_earthlink.net> wrote:
>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

For the useful answer, see other people's comments, or the documentation for SELECT ... FOR UPDATE NOWAIT

>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

24 bytes - if you're going to be pedantic, at least be correct.

(I also note my documentation says "in earlier versions, VMS systems had ITL entries of a different size than UNIX systems").

>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.

Several other places too, including on Oracle's Data Server Internals seminars.

-- 
Andrew Mobbs - http://www.chiark.greenend.org.uk/~andrewm/