Results 1 to 7 of 7
  1. #1
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509

    Question Unanswered: who has a particular row locked

    I have a small problem that I am having problems figuring out. If I attempt to select a row using FOR UPDATE NOWAIT and it's already locked I want to determine who has the lock. I can look at the DDL lock on the table but if we have 500 people updating the table that doesn't tell me who has that particular row locked. I know that oracle sets up lock information on the row itself but is there anyway to access it? While we have 8i, 9i, 10g, and 11g databases, this particular table is on 8I. Of course I have the primary key or rowid to the specific row. Any help would be appreciated.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    $ORACLE_HOME/rdbms/admin/utllockt.sql

    let us know if this helps
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    Thanks Ana,
    I already looked at that. It only shows blocked locks. I want to know who has a lock that is not blocking another user but I know the rowid of the row that is locked. Thats the problem that is making me go crazy. LOL
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  4. #4
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    Quote Originally Posted by beilstwh View Post
    I want to know who has a lock that is not blocking another user but I know the rowid of the row that is locked.
    That's not possible. Oracle does not have a single place where locks are kept (unlike other databases which use a "Lock Manager"). Each row data contains a flag if it's locked or not and only when trying to acquire a lock on a row, Oracle will check if it's already locked by some other transaction.
    I will not read nor answer questions where the SQL code is messy and not formatted properly using [code] tags: http://www.dbforums.com/misc.php?do=bbcode#code

    Tips for good questions:

    http://tkyte.blogspot.de/2005/06/how...questions.html
    http://wiki.postgresql.org/wiki/SlowQueryQuestions
    http://catb.org/esr/faqs/smart-questions.html

  5. #5
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    Unfortunately that's what I thought too. I was hoping for someway to look at the redo for the row and get the info from there. Oh well. Thanks anyway.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  6. #6
    Join Date
    Jan 2013
    Posts
    1

    Locks on the system

    -- Try this:

    clear breaks;
    ttitle off;
    SET TERMOUT ON;
    set trimout ON
    set trimspool ON
    SET PAGESIZE 10000;
    set lines 300
    column object_name format a30
    column OS_USER_NAME format a12
    column module format a35
    column program format a30
    column object_type format a10
    column oracle_username format a10
    column process format a16
    column action format a33
    column locked_mode format 999999 heading 'L_MODE'
    column sid_ser format a15
    column ROW_WAIT_ROW# heading 'Row#'
    -- set markup html on;
    spool ora_locks
    SELECT
    OBJ.object_name
    ,OBJ.object_type
    ,ss.sid||','||ss.serial#||',@'||ss.inst_id Sid_Ser
    ,LOBJ.oracle_username
    ,LOBJ.os_user_name
    ,LOBJ.locked_mode
    ,ss.MODULE
    ,ss.PROCESS
    ,ss.PROGRAM
    ,ACTION
    FROM
    all_objects Obj
    ,gv$locked_object Lobj
    ,gv$session SS
    WHERE
    OBJ.object_id = LOBJ.object_id and
    SS.sid = LOBJ.session_id
    order by obj.object_name,sid_ser
    /

  7. #7
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    in SESSION #1 I did as below
    Code:
    SQL> select id from tt where id = 1 for update;
    
            ID
    ----------
             1
    
    SQL>
    in session #2 I did as below

    Code:
    [oracle@localhost ~]$ sqlplus / as sysdba
    
    SQL*Plus: Release 11.2.0.2.0 Production on Fri Jan 4 15:06:07 2013
    
    Copyright (c) 1982, 2010, Oracle.  All rights reserved.
    
    
    Connected to:
    Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
    With the Partitioning, OLAP, Data Mining and Real Application Testing options
    
    SQL> clear breaks; 
    ttitle OFF; 
    SET termout ON; 
    set trimout ON 
    set trimspool ON 
    SET pagesize 10000; 
    set lines 300 
    column object_name format a30 
    column os_user_name format a12 
    column MODULE format a35 
    column program format a30 
    column object_type format a10 
    column oracle_username format a10 
    column process format a16 
    column action format a33 
    column locked_mode format 999999 heading 'L_MODE' 
    column sid_ser format a15 
    column row_wait_row# heading 'Row#' 
    -- set markup html on; 
    spool ora_locks 
    SELECT OBJ.object_name, 
           OBJ.object_type, 
           ss.sid 
           ||',' 
           ||ss.serial# 
           ||',@' 
           ||ss.inst_id Sid_Ser, 
           LOBJ.oracle_username, 
           LOBJ.os_user_name, 
           LOBJ.locked_mode, 
           ss.MODULE, 
           ss.process, 
           ss.program, 
           action 
    FROM   all_objects Obj, 
           gv$locked_object Lobj, 
           gv$session SS 
    WHERE  OBJ.object_id = LOBJ.object_id 
           AND SS.sid = LOBJ.session_id 
    ORDER  BY obj.object_name, 
              sid_ser 
    
    / breaks cleared
    SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL>   2    3    4    5    6    7    8    9   10   11   12   13   14   15   16   17   18   19   20   21   22  SQL> 
    
    OBJECT_NAME                    OBJECT_TYP SID_SER         ORACLE_USE OS_USER_NAME  L_MODE MODULE                              PROCESS          PROGRAM               ACTION
    ------------------------------ ---------- --------------- ---------- ------------ ------- ----------------------------------- ---------------- ------------------------------ ---------------------------------
    TT                             TABLE      47,7373,@1      USER1      oracle    3 SQL*Plus                             13056            sqlplus@localhost.localdomain
                                                                                          (TNS V1-V3)
    
    
    SQL>
    usefulness of posted SQL is in the eye of the beholder.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

Posting Permissions

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