Home » SQL & PL/SQL » SQL & PL/SQL » how to get the user who locked a record (oracle db10g)
how to get the user who locked a record [message #423453] Thu, 24 September 2009 06:19 Go to next message
ator
Messages: 38
Registered: March 2009
Member
Hi,

this is what I'm trying to do. When someone want to modify a record which is already locked i would like to know who is the user that locked it. So far i succeed only to get the users that locked record on a table but not the specify record.

Is it possible or i have to create my own table and save trace on it when the record are locked and release?

Sorry for my english and thanks,
Ator.


PS: if this is not the right forum i apologize Smile
Re: how to get the user who locked a record [message #423454 is a reply to message #423453] Thu, 24 September 2009 06:27 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
I don't quite get what you're asking for?

You say that you can get the user that locked the record, but don't know which record is locked.

Surely as you're trying to update the record, then you do know which record is locked, as you're trying to update it.

Re: how to get the user who locked a record [message #423455 is a reply to message #423453] Thu, 24 September 2009 06:49 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You cannot know who hold a record until you try to also lock it and are blocked.

Regards
Michel

[Updated on: Thu, 24 September 2009 07:36]

Report message to a moderator

Re: how to get the user who locked a record [message #423460 is a reply to message #423454] Thu, 24 September 2009 07:33 Go to previous messageGo to next message
ator
Messages: 38
Registered: March 2009
Member
I didn't explain myself well then.
this is te situatione now:

User A-> locked record 1 of table XXX
User B -> locked record 3 of table XXX

when the user C try to update the record 1 of table XXX the onlock trigger raise. So far i can get that on table XXX User A and user B locked a record on that table.

My question is, can i (through select on existing oracle tables) get that is User A that locked the record?


I hope i explained myself this time Smile

Thanks,
Ator
Re: how to get the user who locked a record [message #423461 is a reply to message #423460] Thu, 24 September 2009 07:35 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Michel Cadot wrote on Thu, 24 September 2009 13:49
You cannot know who hold a record until you try to also lock it and are blocked.

Regards
Michel

[Updated on: Thu, 24 September 2009 07:36]

Report message to a moderator

Re: how to get the user who locked a record [message #423467 is a reply to message #423460] Thu, 24 September 2009 09:02 Go to previous messageGo to next message
ayush_anand
Messages: 417
Registered: November 2008
Senior Member
Quote:
My question is, can i (through select on existing oracle tables) get that is User A that locked the record?


yes but not from the session that is blocked

Session 1
SQL> select package_id
  2  from kdlv_packages
  3  where package_id = 30006
  4  for update 
  5  /

PACKAGE_ID
----------
     30006

SQL> select username,
  2  v$lock.sid,
  3  id1, id2,
  4  lmode,
  5  request, block, v$lock.type
  6  from v$lock, v$session
  7  where v$lock.sid = v$session.sid
  8  and v$session.username = USER
  9  /

USERNAME                              SID        ID1        ID2      LMODE    REQUEST      BLOCK TY
------------------------------ ---------- ---------- ---------- ---------- ---------- ---------- --
XXMITG                                107      46803          0          3          0          2 TM
XXMITG                                107     131108       9342          6          0          2 TX

SQL> select object_name, object_id from all_objects where object_id = 46803;

OBJECT_NAME                     OBJECT_ID
------------------------------ ----------
KDLV_PACKAGES                       46803

SQL> SELECT (SELECT username
  2            FROM v$session
  3           WHERE SID = a.SID) blocker, a.SID, ' is blocking ',
  4         (SELECT username
  5            FROM v$session
  6           WHERE SID = b.SID) blockee, b.SID
  7    FROM v$lock a, v$lock b
  8   WHERE a.BLOCK = 1 AND b.request > 0 AND a.id1 = b.id1 AND a.id2 = b.id2
  9  /

BLOCKER                               SID 'ISBLOCKING'                     BLOCKEE                               SID
------------------------------ ---------- -------------------------------- -------------------------
XXMITG                                107  is blocking                     XXMITG                                138


session 2
 select package_id
 from kdlv_packages
 where package_id = 30006
 for update 
 /
--is blocked until session one commits or rollback

please note i connected in both sessions as xxmitg user

[Updated on: Thu, 24 September 2009 09:05]

Report message to a moderator

Re: how to get the user who locked a record [message #423509 is a reply to message #423453] Thu, 24 September 2009 21:13 Go to previous message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
Nice.

But let me re-iterate what Michel has already said so that there is no confusion across replies in this post.

As a general case, it is NOT POSSIBLE to find the user who has a lock on a row.

In the special situation where user B is waiting to acquire a lock on a row which is currently locked by user A, it is possible to find the row, I believe the rowid, whicn is the source of contention using various v$/x$ tables. But this is a special case in which Oracle exposes this information. Thus as was explained by ayush_anand you need at least three transactions to get this information along with some moderately intelligent SQL.

Once again, as a general case, it is NOT POSSIBLE to find the user who has a lock on a row.

I will try to explain although Oracle Internals is not my area of expertise. Oracle does not use what most would call a "TRADITIONAL LOCK MANAGER". As such there is no "LIST" of locks kept anywhere that one can interrogate at will to see which rows are locked. There was such a thing at one time. I remember back in 1985 using it to diagnose a locking problem on an Oracle 5 database (or was it Oracle 3?). Anyways, the lack of an old style lock manager is by Oracle's estimation a good thing because they believe that LOCK MANAGER = LACK OF CONCURRENCY AND SCALABILITY. Tom Kyte of asktomhome talks about this in several of his posts. Indeed as you read his posts you may see a hint of pride in his writing in that he is confident the lack of a traditional lock manager in Oracle is one of the major differences that shows Oracle's technology superiority over other RDBMSes. Last I knew it worked something like this:

When a row is locked, Oracle places the transaction ID of the transaction doing the locking, somewhere in the block (block header?/row header? (I forget)) to indicate that this row is locked by the specified transaction. If a second transaction wishes to lock this row, it checks for the existence of this transaction id. If it finds a transaction id has laid claim to a row, it is not done yet, it next must check to see if the transaction is in fact still a valid transaction in the system. If so, the second transaction requests to be blocked and at that point the blocker/blocked and object of contection are recorded hence the ability to find a locked row if this locked row is the cause of a block between transactions. If the transaction finds that the transaction id is invalid, then it writes its own transation id over top of the old and it now is the transaction locking the row. I believe there are several moments of opportunity during which Oracle can clear "dead lock indicators" from blocks but I forget what these are.

Using this kind of strategy you can see that a list of locks is not necessary except for specific scenarios.

I saw commentary on finding locked rows that block but I cannot find the article again sorry. It is out on the NET somewhere so look for it. It offers a SQL statement which I believe gives you the ROWID of the locking row. I think there is also a way to determine the table the row lives on via block address decoding etc. As you can see, even when the data is exposed, it is not necessarily easy to extract in a useful way; you must put some effort into it.

If I have too grossly mis-stated facts or someone wishes to add more clarity over me, please do so, I will not be offended in any way. Indeed I look forward to a better understanding.

Good luck, Kevin
Previous Topic: query
Next Topic: Pivoting or Transposing
Goto Forum:
  


Current Time: Sat Dec 10 20:37:17 CST 2016

Total time taken to generate the page: 0.12956 seconds