Lock record

tiendh
tiendh used Ask the Experts™
on
How can I know who lock record by
select * from  ... where ...
for update.
And what rowid of record.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2008
Commented:
Grant role 'select_catalog_role' to the user, you can then query v$ views like v$locked_object, v$lock to get the information regarding locks.



 To find the session locking a specific record perform the following steps:
 1) Open a new session and retrieve its SID. One way to find the current session's  
    ID is by issuing the following statement:
       SELECT DISTINCT sid FROM v$mystat;
    NOTE: you need the select privilege on this view first if you want to use it.      
    (this SID will from now on be referred to as 'mysid')
 2) From this session launch an update statement on the record that is blocked
    --> this will cause the session to wait for the blocking transaction to
        be either committed or rollbacked.
    NOTE: use an update statement that does not make any real changes.
 3) Open a new session that has select privileges on the v$lock view.
 4) SELECT id1 FROM v$lock WHERE SID=(mysid)  
                             AND id2!=0;
    This will return the first lock identifier that is being used by mysid.
 5) This lock identifier is identical to the one that is being used by the  
    blocking session.  Thus the next step is to perform a select on v$lock
    retrieving the session who has the same lock identifier:
    SELECT * FROM v$lock WHERE SID!=(mysid) AND ID1=(lock_identifier);
 
    Example query result:
 
    ADDR     KADDR          SID TY       ID1       ID2     LMODE   REQUEST     CTIME     BLOCK
    -------- -------- --------- -- --------- --------- --------- --------- --------- ---------
    03D4AEE4 03D4AFB0        15 TX    196615       281         6         0      3069         1
 
 6) Further information (username / terminal / machine) can be queried from  
    v$session.
 
 
 
 The following function returns the SID,username, terminal and object that is
 blocking the session whose SID is specified as the argument of the function:
 
 CREATE OR REPLACE FUNCTION find_blocker(f_sid number) RETURN varchar2 IS
 
 blocking_session  number;
 blocking_user     varchar2(50);
 blocking_terminal varchar2(50);
 blocked_in_obj    varchar2(50);
 
 BEGIN
 
  BEGIN
   SELECT sid INTO blocking_session  
   FROM v$lock  
   WHERE block=1
   AND   id1= (SELECT id1 FROM v$lock WHERE sid=f_sid AND id2!=0);
   EXCEPTION WHEN NO_DATA_FOUND THEN  
                  return 'This SID does not appear to be blocked';
  END;
 
 SELECT username,terminal INTO blocking_user,blocking_terminal  
 FROM v$session  
 WHERE sid=blocking_session;
 
 SELECT object_name INTO blocked_in_obj  
 FROM dba_objects WHERE object_id=
                        (SELECT row_wait_obj# FROM v$session WHERE sid=f_sid);
 
 RETURN 'SID '||blocking_session||' (USER='||blocking_user||
        ')(TERM='||blocking_terminal||') is holding LOCK on rowid in '||
        blocked_in_obj||' table.';
END;

Commented:
The simplest query (without any tech details)

SELECT
SYS.V_$LOCK.SID,
SERIAL#,
MACHINE,
PROGRAM,
OBJECT_NAME,
OSUSER
FROM
SYS.V_$LOCK,
SYS.V_$SESSION,
ALL_OBJECTS
WHERE
SYS.V_$LOCK.SID = SYS.V_$SESSION.SID
AND SYS.V_$LOCK.ID1 = ALL_OBJECTS.OBJECT_ID
AND SYS.V_$LOCK.TYPE = 'TM'
ORDER BY
SYS.V_$LOCK.SID,
MACHINE,
PROGRAM

You can then kill any session that is causing problems using

alter system kill session '<SID>,<serial#>'

Commented:
You have to run the above query from SYSTEM!
Should you be charging more for IT Services?

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Author

Commented:
I want to get the value of lock record , can i ?

Commented:
what do u mean by "value"  .. is it the rowid ?

Author

Commented:
yes, it mean i want to know which row was lock

Commented:
SELECT row_wait_row#, row_wait_obj# FROM v$session WHERE sid= <SID which you have got from my above query>

You will get rowid in row_wait_row# and object id in row_wait_obj#

If a file i/o locked the row_wait_file# will give the requried value.

Hope it helps.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial