oracle Tuning - 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;