Lock
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: Lock

  1. #1
    Join Date
    Jan 2001
    Posts
    138
    Is there a way for a user to see who is holding lock on a row? For example, if User #1 is updating a row in a table and holds lock on that, can another user while trying to access the same row find out who is holding the lock? Is there a mechanism to do this? I thought since users don't have access to v$lock etc., they can't get that info. Thanks.

  2. #2
    Join Date
    Feb 2001
    Posts
    41
    yes there is what version of database are you using?
    Best Regards,
    Harsh Shah

  3. #3
    Join Date
    Jan 2001
    Posts
    138

    re: Lock

    We are on 8.0.6

  4. #4
    Join Date
    Mar 2001
    Posts
    314
    You can give your users select permission on v_$lock (and not on v$lock - this applies to all v$ views) so that they can see the data. It is NOT ADVISABLE to directly give this right though!

    What you should do is to create a procedure/function in the SYS/SYSTEM schema (any DBA schema will do) to return the relevant information to the user. You have to give execute permission on the procedure/function to the user(s) but no select on any v_$'s necessary. This will be more secure as you have control over the info you pass to the user(s).

    This will work regardless of your oracle version (should work!).

    -amar

  5. #5
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    As I've written a couple of times in this forum: *there is no way* to find out:
    a) who is locking a particular row
    b) which rows are locked by a particular user

    The reason for this is that row locks are held in a database blocks themselves, no system view/table holds this information. What you can find out from the data dictionary is only:
    a) who is having a lock on a particular table
    b) what kind of lock he/she holds on that table

    Example:

    5 diferent users have some different rows locked on the same table TABLE_A. Now you want to change one of those locked rows. Of course your update will fail and now you want to find out who is locking this row. All that you'll be able to find out will be which 5 users have locks on that table TABLE_A, but you won't be able to find out which of those 5 users is holding lock on your row. Of course, in case only one user has got lock on that table when your update failed you know that that user was holding a lock on your row, but this is a very particular case, not a general answer for your general problem.
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  6. #6
    Join Date
    Apr 2001
    Posts
    219
    There is a document on Metalink on how to get rowids for users on a specific object. The document number is 132629.1. The document requires you to use Pro*C, but explains everything.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width