Board index » delphi » Getting UserId of User Locking a Record in Oracle

Getting UserId of User Locking a Record in Oracle

We are using DOA to connect to an Oracle database.  When someone tries to
edit a record that another user is already editing they get an Oracle
message stating that the record is locked by another user.  Is there any way
to get the userid of the user that has the record locked?
--

Regards
Kevin Campbell
Program Manager
TMA Corporation/ Bath, ME

 

Re:Getting UserId of User Locking a Record in Oracle


Kevin,
How are you accessing Oracle...BDE, ODA, or some other way?  It is possible
for a single user to cause this error if your access method is not properly
designed.  On the other hand, this error can be valid for multi-user update
of the same row, though it is unlikely if you are employing the standard
pessimistic locking mechanism.  The window for contention is too small.
It is possible to get locking information from the server.  I use Toad (IMO
an essential tool for Oracle developers) for this kind of server management.
You can get a freeware version at www.quest.com.

Quote
"Kevin Campbell" <kcamp...@nospam.bath.tmac.com> wrote in message

news:3c20eeec$1_1@dnews...
Quote
> We are using DOA to connect to an Oracle database.  When someone tries to
> edit a record that another user is already editing they get an Oracle
> message stating that the record is locked by another user.  Is there any
way
> to get the userid of the user that has the record locked?
> --

> Regards
> Kevin Campbell
> Program Manager
> TMA Corporation/ Bath, ME

Re:Getting UserId of User Locking a Record in Oracle


Hello Kevin !

Oracle has two views:
- V$LOCK. It will return a list of all locks in the system (instance).
And it has column SID - which is session identifier.
- V$SESSION. It will return a list of sessions. And this view also
has column SID.

So, you need to join this two views by SID column and you
will get interesting for you information. You should have DBA
priviliges to get access to this views, either DBA should grant
SELECT permission to you on this views.

Regards,
Dmitry

--
===========================================
Dmitry L. Arefiev, director of gs-soft.ru ltd.
Solutions for successful companies

Author of NCOCI8 - Freeware Delphi to Oracle8i direct access

ICQ: 50741007
EMail: daref...@gs-soft.ru
Company: http://www.gs-soft.ru
NCOCI8: http://www.da-soft.com

Quote
"Kevin Campbell" <kcamp...@nospam.bath.tmac.com> wrote in message

news:3c20eeec$1_1@dnews...
Quote
> We are using DOA to connect to an Oracle database.  When someone tries to
> edit a record that another user is already editing they get an Oracle
> message stating that the record is locked by another user.  Is there any
way
> to get the userid of the user that has the record locked?
> --

> Regards
> Kevin Campbell
> Program Manager
> TMA Corporation/ Bath, ME

Re:Getting UserId of User Locking a Record in Oracle


Please read my original post.  I am using DOA to attach to Oracle.  I do not
consider this message an error.  I expect to get it.  We have a multi-user
system and we lock the record immediately upon starting the editing process
and release the lock after the changed record is posted.  This is how we
want it to work.  This way no other user can update the record while someone
is editing it.  My question is, can I get the name of the user that has the
record locked?  I know I can get a list of users that have locks on the
table but I can't figure out how to find out which record they have locked.
I use the following SQL to get a list of users that have locks on a table:

select os_user_name

from sys.v_$locked_object l1, sys.all_objects o1

where l1.OBJECT_ID = o1.OBJECT_ID

and o1.OBJECT_NAME = :name

Name is the table name that I'm interested in.  Is there a way to go to the
next step and find out which record the use has locked?  What I want to do
is intercept the standard Oracle message saying "Record locked by another
user" and replace it with the message "Record locked by: 'UserName'".
--

Regards
Kevin Campbell
Program Manager
TMA Corporation/ Bath, ME

"Nick J. Pavlina" <npavl...@lanframe.com> wrote in message
news:3c21476d$1_2@dnews...

Quote
> Kevin,
> How are you accessing Oracle...BDE, ODA, or some other way?  It is
possible
> for a single user to cause this error if your access method is not
properly
> designed.  On the other hand, this error can be valid for multi-user
update
> of the same row, though it is unlikely if you are employing the standard
> pessimistic locking mechanism.  The window for contention is too small.
> It is possible to get locking information from the server.  I use Toad
(IMO
> an essential tool for Oracle developers) for this kind of server
management.
> You can get a freeware version at www.quest.com.

> "Kevin Campbell" <kcamp...@nospam.bath.tmac.com> wrote in message
> news:3c20eeec$1_1@dnews...
> > We are using DOA to connect to an Oracle database.  When someone tries
to
> > edit a record that another user is already editing they get an Oracle
> > message stating that the record is locked by another user.  Is there any
> way
> > to get the userid of the user that has the record locked?
> > --

> > Regards
> > Kevin Campbell
> > Program Manager
> > TMA Corporation/ Bath, ME

Re:Getting UserId of User Locking a Record in Oracle


This is true.  but neither of these views will give me the record that is
locked.  I can get a list of users that have locks on a table but I can't
get it down to which record(s) they have locked.  This is the SQL to get the
list of users:

select os_user_name
from sys.v_$locked_object l1, sys.all_objects o1

where l1.OBJECT_ID = o1.OBJECT_ID

and o1.OBJECT_NAME = :name

As I said this just gives me the users that have locks within the table
specified by :NAME but it doesn't tell me the exact record that is locked.
I want to replace the standard Oracle message "Record locked by another
user" with one that says "Record locked by: UserName".
--

Regards
Kevin Campbell
Program Manager
TMA Corporation/ Bath, ME

Quote
"Dmitry Arefiev" <daref...@da-soft.com> wrote in message

news:3c21bb63$1_2@dnews...
Quote
> Hello Kevin !

> Oracle has two views:
> - V$LOCK. It will return a list of all locks in the system (instance).
> And it has column SID - which is session identifier.
> - V$SESSION. It will return a list of sessions. And this view also
> has column SID.

> So, you need to join this two views by SID column and you
> will get interesting for you information. You should have DBA
> priviliges to get access to this views, either DBA should grant
> SELECT permission to you on this views.

> Regards,
> Dmitry

> --
> ===========================================
> Dmitry L. Arefiev, director of gs-soft.ru ltd.
> Solutions for successful companies

> Author of NCOCI8 - Freeware Delphi to Oracle8i direct access

> ICQ: 50741007
> EMail: daref...@gs-soft.ru
> Company: http://www.gs-soft.ru
> NCOCI8: http://www.da-soft.com

> "Kevin Campbell" <kcamp...@nospam.bath.tmac.com> wrote in message
> news:3c20eeec$1_1@dnews...
> > We are using DOA to connect to an Oracle database.  When someone tries
to
> > edit a record that another user is already editing they get an Oracle
> > message stating that the record is locked by another user.  Is there any
> way
> > to get the userid of the user that has the record locked?
> > --

> > Regards
> > Kevin Campbell
> > Program Manager
> > TMA Corporation/ Bath, ME

Re:Getting UserId of User Locking a Record in Oracle


Quote
Kevin Campbell wrote:
> Please read my original post.  I am using DOA to attach to Oracle.  I do not
> consider this message an error.  I expect to get it.  We have a multi-user
> system and we lock the record immediately upon starting the editing process
> and release the lock after the changed record is posted.  This is how we
> want it to work.  This way no other user can update the record while someone
> is editing it.  My question is, can I get the name of the user that has the
> record locked?  I know I can get a list of users that have locks on the
> table but I can't figure out how to find out which record they have locked.
> I use the following SQL to get a list of users that have locks on a table:
> ...

Since you are explicitly doing the locking, you might just a litte
something to store a record with userinfo in a separate table with a
separate transaction.  Commit this transaction and other users should be
able to see it.  Clean up when the "real" transaction finishes.
Since the other transaction is committed (immediately) and cannot be
rolled back in case of e.g. a power-OFF situation, you may find that you
need to clean out records from the table (even if those records won't do
any harm).

Aage J.

Re:Getting UserId of User Locking a Record in Oracle


We are not explicitly doing the locking.  Oracle is automatically locking
when we edit the record.  We just have it set to Lock immediate rather than
do optimistic locking.  I'm asking if there is something in Oracle that I
can read to find out the record that is locked.  Oracle must know which
record is locked, It gives a message if another user tries to edit a locked
record.
--

Regards
Kevin Campbell
Program Manager
TMA Corporation/ Bath, ME

Quote
"Aage Johansen" <aagjo...@offline.no> wrote in message

news:3C2250EA.BBED502B@offline.no...
Quote
> Kevin Campbell wrote:
> > Please read my original post.  I am using DOA to attach to Oracle.  I do
not
> > consider this message an error.  I expect to get it.  We have a
multi-user
> > system and we lock the record immediately upon starting the editing
process
> > and release the lock after the changed record is posted.  This is how we
> > want it to work.  This way no other user can update the record while
someone
> > is editing it.  My question is, can I get the name of the user that has
the
> > record locked?  I know I can get a list of users that have locks on the
> > table but I can't figure out how to find out which record they have
locked.
> > I use the following SQL to get a list of users that have locks on a
table:
> > ...

> Since you are explicitly doing the locking, you might just a litte
> something to store a record with userinfo in a separate table with a
> separate transaction.  Commit this transaction and other users should be
> able to see it.  Clean up when the "real" transaction finishes.
> Since the other transaction is committed (immediately) and cannot be
> rolled back in case of e.g. a power-OFF situation, you may find that you
> need to clean out records from the table (even if those records won't do
> any harm).

> Aage J.

Other Threads