Search

Join this Blog for direct reference of any post into your inbox. To join just click on "Join this Site" under "Follower" then Login with your Email.*** DECLARATION: I Maintain this Blog for Helping Myself While at Work and Welcome any body Needing Help!!!.*** CAUTION: Using any of the script from this Blog may contain at Own Risk. These scripts May or May not have been Tested.***

Sunday, 29 April 2012

Find whose lock is blocking‎

Blocking Locks in Oracle
To understand the situation whose lock is blocking follows this example. It clearly showing which object is locked and who is doing blocking.
Just open two sessions and issue the following command on session 1

CREATE TABLE SHAHID_TEST
(
ID VARCHAR2(3),
SAL NUMBER(6,2));

INSERT INTO SHAHID_TEST VALUES ('A', 6000);
INSERT INTO SHAHID_TEST VALUES ('B', 4000);

SELECT * FROM SHAHID_TEST;

COMMIT;

SELECT * FROM SHAHID_TEST FOR UPDATE;

Now on session 2, if you try to update a row what happens?

UPDATE SHAHID_TEST
SET ID='A' WHERE ID='A';

The statements will hang, block by the lock as the session 1 is holding the entire table through the above command ‘SELECT * FROM SHAHID_TEST FOR UPDATE’;
Now try to identify the block session. Oracle provides the different views that display the information on locks:

DBA_BLOCKERS – Shows non-waiting sessions holding locks being waited-on
DBA_DDL_LOCKS – Shows all DDL locks held or being requested
DBA_DML_LOCKS – Shows all DML locks held or being requested
DBA_LOCK_INTERNAL – Displays 1 row for every lock or latch held or being requested with
the username of who is holding the lock
DBA_LOCKS – Shows all locks or latches held or being requested
DBA_WAITERS – Shows all sessions waiting on, but not holding waited for locks
V$LOCK – view lists the locks currently held within the Oracle server, and outstanding requests for a lock or latch.
V$LOCKED_OBJECT – view combines session, rollback, and locked object information for locks acquired by transactions in the system.

SELECT * FROM V$LOCK;

ADDR
KADDR
SID
TYPE
ID1
ID2
LMODE
REQUEST
CTIME
BLOCK
682BE9CC
682BE9DC
6
TX
12
16
4
2
1722905
0
682BE980
682BE990
2
MR
13
0
4
0
1722907
0
682BE934
682BE944
6
TX
12
16
4
0
1722905
1

Note in above output block column, if a session holds a lock that is blocking another session then BLOCK=1
After finding the SID of blocker you can try this query:
select s.sid, q.sql_text from v$sqltext q, v$session s
where q.address = s.sql_address
and s.sid = &sid
order by piece;

To avoid having to stare at the table and cross-compare ID1's and ID2's, try this query:
select l1.sid, ' IS BLOCKING ', l2.sid
from v$lock l1, v$lock l2
where l1.block =1 and l2.request > 0
and l1.id1=l2.id1 and l1.id2=l2.id2;

To identify the lock object:
Select object_name from dba_objects where object_id=16 ;

To identify the locked row:
select row_wait_obj#, row_wait_file#, row_wait_block#, row_wait_row#
from v$session where sid=6;

Note: Try to query several aspect related to the lock from the above mention views

1 comments:

  1. Lock Out Shop Richmond Indiana Full Service 24 Hr Locksmith Since 1992
    locksmith Richmond Indiana

    ReplyDelete