Database blocking is a situation where the statement run by one user
locks a record or set of records and another statement run by the same user or
different user requires a conflicting lock type on the record or records,
locked by the first user.
Database blocking issue is a very common scenario in any application.
How to identify the blocking session? Or more specifically how to identify the blocking rows and objects? How to resolve database blocking issue? Let’s see one by one.
How to identify the blocking session? Or more specifically how to identify the blocking rows and objects? How to resolve database blocking issue? Let’s see one by one.
How to Identify the blocking session:
1. DBA_BLOCKERS : Gives information only about the blocking session.
SQL> select * from dba_blockers;
HOLDING_SESSION
—————
252
2. v$LOCK : Gives details of
blocking and waiting session.
SQL> 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
/
SQL> 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
/
SID ‘ISBLOCKING’ SID
———- ————- ———-
244 IS BLOCKING 252
———- ————- ———-
244 IS BLOCKING 252
To get more specific details use the below query:
SQL> select s1.username || ‘@’ || s1.machine
|| ‘ ( SID=’ || s1.sid || ‘ ) is blocking ‘
|| s2.username || ‘@’|| s2.machine || ‘ ( SID=’ || s2.sid || ‘ ) ‘ AS blocking_status
from v$lock l1, v$session s1, v$lock l2, v$session s2
where s1.sid=l1.sid and s2.sid=l2.sid
and l1.BLOCK=1 and l2.request > 0
and l1.id1 = l2.id1
and l2.id2 = l2.id2 ;
|| ‘ ( SID=’ || s1.sid || ‘ ) is blocking ‘
|| s2.username || ‘@’|| s2.machine || ‘ ( SID=’ || s2.sid || ‘ ) ‘ AS blocking_status
from v$lock l1, v$session s1, v$lock l2, v$session s2
where s1.sid=l1.sid and s2.sid=l2.sid
and l1.BLOCK=1 and l2.request > 0
and l1.id1 = l2.id1
and l2.id2 = l2.id2 ;
BLOCKING_STATUS
——————————————————————————–
PK@host1( SID=244 ) is blocking PY@Host2)
——————————————————————————–
PK@host1( SID=244 ) is blocking PY@Host2)
How to Identify the locked object:
SQL> select * from v$lock ;
ADDR
KADDR
SID TY
ID1
ID2
LMODE REQUEST CTIME BLOCK
—————- —————- ———- – ———- ———- ——- ———- ———- ———-
0000000451723DE8 0000000451723E20 244 TX 1310745 3139497 6 0 166 1
—————- —————- ———- – ———- ———- ——- ———- ———- ———-
0000000451723DE8 0000000451723E20 244 TX 1310745 3139497 6 0 166 1
000000046032AFE0
000000046032B000 252
TX 1310745
3139497
0 6
33
0
TYPES OF LOCKS - UL, TX amd TM
1. UL is a user-defined lock This is a lock defined with the DBMS_LOCK package.
2. TX lock is a row transaction lock; it’s acquired once for every transaction that changes data. Number of objects are being changed does not matter. The ID1 and ID2 columns point to the rollback segment and transaction table entries for that transaction.
3. TM lock is a DML lock. It’s acquired once for each object that’s being changed. The ID1 column identifies the object being modified.
1. UL is a user-defined lock This is a lock defined with the DBMS_LOCK package.
2. TX lock is a row transaction lock; it’s acquired once for every transaction that changes data. Number of objects are being changed does not matter. The ID1 and ID2 columns point to the rollback segment and transaction table entries for that transaction.
3. TM lock is a DML lock. It’s acquired once for each object that’s being changed. The ID1 column identifies the object being modified.
So to find the object that is being blocked we can use ID1 from the
v$lock.
SQL> select object_name from dba_objects where object_id=307193;
OBJECT_NAME
————–
OBJ1
————–
OBJ1
How to Identify the locked row ?
SQL> select do.object_name,
row_wait_obj#, do.data_object_id, row_wait_file#, row_wait_block#, row_wait_row#,
dbms_rowid.rowid_create ( 1, do.data_object_id, ROW_WAIT_FILE#, ROW_WAIT_BLOCK#, ROW_WAIT_ROW# )
from v$session s, dba_objects do
where sid=252
and s.ROW_WAIT_OBJ# = do.OBJECT_ID ;
row_wait_obj#, do.data_object_id, row_wait_file#, row_wait_block#, row_wait_row#,
dbms_rowid.rowid_create ( 1, do.data_object_id, ROW_WAIT_FILE#, ROW_WAIT_BLOCK#, ROW_WAIT_ROW# )
from v$session s, dba_objects do
where sid=252
and s.ROW_WAIT_OBJ# = do.OBJECT_ID ;
OBJECT_NAME
——————————————————————————–
ROW_WAIT_OBJ# DATA_OBJECT_ID ROW_WAIT_FILE# ROW_WAIT_BLOCK# ROW_WAIT_ROW#
————- ————– ————– ————— ————-
DBMS_ROWID.ROWID_C
——————
OBJ1
307193 307193 5 455 0
AABK/5AAFAAAAHHAAA
From this, we get the row directly:
SQL> select * from obj1 where rowid=’ ABGC/5AAFAAGFTRDAA’ ;
——————————————————————————–
ROW_WAIT_OBJ# DATA_OBJECT_ID ROW_WAIT_FILE# ROW_WAIT_BLOCK# ROW_WAIT_ROW#
————- ————– ————– ————— ————-
DBMS_ROWID.ROWID_C
——————
OBJ1
307193 307193 5 455 0
AABK/5AAFAAAAHHAAA
From this, we get the row directly:
SQL> select * from obj1 where rowid=’ ABGC/5AAFAAGFTRDAA’ ;
Getting the sql query that is being blocked
If you got the sid it should be easy by using the following sql :
SQL>select s.sid, q.sql_text from v$sqltext q, v$session s
where q.address = s.sql_address
and s.sid = 252;
If you got the sid it should be easy by using the following sql :
SQL>select s.sid, q.sql_text from v$sqltext q, v$session s
where q.address = s.sql_address
and s.sid = 252;
SID SQL_TEXT
—– —————————————————————-
252 update obj1 set bar=:”SYS_B_0″ where bar=:”SYS_B_1″
—– —————————————————————-
252 update obj1 set bar=:”SYS_B_0″ where bar=:”SYS_B_1″
Finding the blocking session SID and Serial#.
SQL> Select blocking_session, sid, serial#,
wait_class,seconds_in_wait From v$session where blocking_session is not NULL
order by blocking_session;
BLOCKING_SESSION SID
SERIAL# WAIT_CLASS SECONDS_IN_WAIT
—————- ———- ———- ————————————————–
244 252 11049 Application 1634
—————- ———- ———- ————————————————–
244 252 11049 Application 1634
Solution to resolve locking
Kill the blocking session.
SQL> alter system kill session 244,11049′ immediate;
Kill the blocking session.
SQL> alter system kill session 244,11049′ immediate;
System altered.
Do you like this post? Please share this article.
HTML Link Code:
Post a Comment