Drop Down MenusCSS Drop Down MenuPure CSS Dropdown Menu

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;

Comments

Popular posts from this blog

PostgreSQL pgBadger

PostgreSQL Pgbadger Installation On Linux

How to configure Replication Manager (repmgr) ?

What's is the difference between streaming replication Vs hot standby vs warm standby ?

PostgreSQL Sequence