Re: Who has the row locked?

Hello Massis

"Massis Isagholian" <massis_at_att.com> schrieb im Newsbeitrag news:ZNzy9.399$hK4.31751_at_bgtnsc05-news.ops.worldnet.att.net...
> I have an application that needs to determine what process has locked a
> specific row on given table. My application needs to retrieve the
following
> information from the database server:
>
> Session ID
> Session Status
> DBase User ID
> OS User ID
> OS Process ID
> Machine Name (client)
> Program Name (ex: myprogram.exe)
> Other lock related info as appropriate
>
> My application is not thread-safe, hence, I prefer not to use multiple
> threads to obtain the information listed above.
>
> I'd appreciate your quick response.
>

If you need to know which application locked a specific row in a table you might have a problem. ORACLE does NOT have and keep a memory of the locked rows in any other place than the block where the row is in. Remember ORACLE is not Sybase or SQL server. ORACLE does not have any limits on how many rows a process can lock or it does not do lock escalation (row locks, page locks, table locks) like some other RDBMSes do.

You can only see that a row is locked by a certain process when you try to access the row with e.g. "select * from table where pk=value for update;". If the selecting process is hanging you can have a look in the data dictionary who is blocking the process (e.g. select * from dba_waiters [run catblock first to get this view]).

If you want to know who is locking 1,2,... rows (no information about which row) on a tables you can have look in v$locked_object.

May I ask you why you need to know who is locking a row?

Have a nice day

Gregor

--
-----------------------------------------------------------------------
Dipl.-Ing. Gregor J. Theis (3x ORACLE OCP DBA 8, 8i, 9i)
mailto:gregor.theis_at_ib-theis.de http://www.ib-theis.de