Who blocked my session?
Wednesday, July 15, 2009
How Do The Pills Yeduc Work
I recently came across the following article Natalka 
published in orafaq.com I hope will be useful. When a user or developer tells you that your session is blocked, "understanding by blocking the execution of a DML which after launch stops responding, you know how useful it can be to identify not only who is doing it, but that object is or better yet, you can identify the exact record your session is waiting to lock.
Building block I will use the table emp
SCOTT schema
update all records
Session 1> update emp 2 September 1000 * salt = 1.05;
14 rows updated.
without commit open another session of SQL * Plus variable
update SQL> set sqlprompt "Session 2>" Session 2>
and this update only one record
Session 2> update emp 2 September
salt = 1000 WHERE empno = 7369 3;
session that is blocking a new session in SQL * Plus check the contents of v $ lock
showing the blocks that currently exist in the base and the relationship between who blocked who, though not
---- REQUEST BLOCK STEM ---- -------- ------- - ------- ------- --- ---- --------- ------- -------
. Going further, you can see which session is being blocked by comparing ID1 and ID2 . The blocked session will have the same values \u200b\u200band ID1 ID2 the blocking, and, as is making the lock request can not be obtained, will REQUEST> 0 . In the above query you can see that SID 137 is blocking the SID 144. Session 1 is for SID 137 and SID 144 is Session 2 that is blocked. To make the comparison not only see data that you can execute query
following SQL> select l1.sid
6 and l1.id2 = l2.id2;
L1.SID
There is even more information in the table v $ lock, but to read, is then necessary to understand a little more about the types of blocks and columns cryptic
There are only three types of blocking, TX, TM and UL. UL is a block defined by the user-defined package that DBMS_LOCK. TX is a block of transaction records, is obtained each time a transaction is to alter data, regardless of the number of objects that will be involved in that transaction. columns ID1 and ID2 point to the rollback segments and the table entries transaction of that transaction.
TM is a DML lock. Obtained each time an object is altered. ID1 column
Lock Modes can see more information by observing the lock modes TM and TX.
columns and LMOD REQUEST use both the same number as the lock modes in order of incremental exclusive: from 0 for non-blocking, up to 6 for exclusive lock. A session should be an exclusive lock TX so that you can change the data; LMOD
be 6. If you can not get exclusive lock because some of the records you want to alter are blocked by another session, then make the request for exclusive TX;
LMOD be 0 because it is not blocking, and REQUEST be 6. This can be seen in the previously executed query
KADDR SID TY ID1 ADDR ; ID2 LMOD REQUEST BLOCK STEM
-------- -------- ------- -- ------- ------- -------- -------- ------- ------- 2887B03C 2887B068 144 TX 196639 3891 0 6 323 0 27CF5D14 27CF5D54 TX 196 639 3891 6 137 , 0570 1 Session 2
observes that
DML ; SID TY ID1 ID2 LMOD STEM REQUEST ; BLOCK -------- -------- ------- -- -------- -------- ------- -------- ------- ------- 0E3CBE90 0E3CBEC0 144 TM 69948 0 3 0 323 0 0E3CBE90 0E3CBEC0 137 TM 69948 0 3 0 570 0 Finding the locked object Now we know that each record points to an object TM locked, you can use the field to identify
ID1. SQL> select object_name from dba_objects WHERE object_id = 69948;
-------------------------- OBJECT_NAME ------------------------------------- EMP is sometimes enough just to know what the object is, however, can further deepen their identifying not only the object but also the data block and even the accurate record on the waiting Session 2. Finding
locked record
This information is obtained from v $ session
by the entrance to the locked session
ROW_WAIT_OBJ # # # ROW_WAIT_BLOCK ROW_WAIT_ROW #
2 ; dbms_rowid.rowid_create (1, row_wait_obj #, row_wait_file #, row_wait_block #, row_wait_row #)
3 from v$session s, dba_objects do
4 where sid = 144
EMP 69948 4 147 0 AAARE8AAEAAAACTAAA
with which we can inspect the registry directly
SQL> select * from scott.emp WHERE rowid = 'AAARE8AAEAAAACTAAA';
EMPNO ENAME ; JOB MGR HIREDATE ; SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ---------- 7369 SMITH CLERK 7902 17-DEC-80 800 20
Building block I will use the table emp
SCOTT schema
. I open a SQL * Plus session and set the following parameters SQL> set sqlprompt "Session 1> Session 1 > set LINESIZE 200
update all records
Session 1> update emp 2
14 rows updated.
update SQL> set sqlprompt "Session 2>" Session 2>
and this update only one record
Session 2> update emp 2 September
salt = 1000 WHERE empno = 7369 3;
after pressing ENTER is not of immediate returns to the prompt, here's the lock.
Identifying session that is blocking a new session in SQL * Plus check the contents of v $ lock
showing the blocks that currently exist in the base and the relationship between who blocked who, though not
explicitly SQL> select * from v $ lock; KADDR SID ADDR TY ID1 ID2 LMOD
---- REQUEST BLOCK STEM ---- -------- ------- - ------- ------- --- ---- --------- ------- -------
........ ........ ....... .. ....... ....... ....... ......... ....... .......
2887B03C 2887B068 144 TX 196639 3891 0 6 323 0
0E3CBE90 0E3CBEC0 144 TM 69948 0 3 0 323 0
0E3CBE90 0E3CBEC0 137 TM 69948 0 3 0 570 0 27CF5D14
27CF5D54 TX 196 639 3891 6 137 ; 0 570 1
last column shows the BLOCK , if a session holds a lock that in turn blocks to another you'll see that
BLOCK = 1 2887B03C 2887B068 144 TX 196639 3891 0 6 323 0
0E3CBE90 0E3CBEC0 144 TM 69948 0 3 0 323 0
0E3CBE90 0E3CBEC0 137 TM 69948 0 3 0 570 0 27CF5D14
27CF5D54 TX 196 639 3891 6 137 ; 0 570 1
last column shows the BLOCK , if a session holds a lock that in turn blocks to another you'll see that
. Going further, you can see which session is being blocked by comparing ID1 and ID2 . The blocked session will have the same values \u200b\u200band ID1 ID2 the blocking, and, as is making the lock request can not be obtained, will REQUEST> 0 . In the above query you can see that SID 137 is blocking the SID 144. Session 1 is for SID 137 and SID 144 is Session 2 that is blocked. To make the comparison not only see data that you can execute query
following SQL> select l1.sid
6 and l1.id2 = l2.id2;
L1.SID
3
6 and s2.sid = l2.sid
7 and l1.block = 1
8 and l2.request > 0
9 and l1.id1 = l2.id1
10 and l2.id2 = l2.id2;
ESTADO_BLOQUEOS
------------------------------------------------------------------------------------------- SCOTT @ testserver
(SID = 137) is blocking SCOTT @ testserver (SID = 144)
6 and s2.sid = l2.sid
7 and l1.block = 1
8 and l2.request > 0
9 and l1.id1 = l2.id1
10 and l2.id2 = l2.id2;
ESTADO_BLOQUEOS
------------------------------------------------------------------------------------------- SCOTT @ testserver
(SID = 137) is blocking SCOTT @ testserver (SID = 144)
There is even more information in the table v $ lock, but to read, is then necessary to understand a little more about the types of blocks and columns cryptic
ID1 and ID2
lock type and columns ID1 / ID2
In this case the type of DML lock is unique as it was I who launched the instruction blocks. However, your can get this information from v $ lock
without much effort.
The first thing is to see the column TYPE
. There are dozens of types of locks, however, most are system. The latter are usually very brief and does not help set the library cache, undo logs, etc. if you mess with
v $ lock. lock type and columns ID1 / ID2
In this case the type of DML lock is unique as it was I who launched the instruction blocks. However, your can get this information from v $ lock
without much effort.
The first thing is to see the column TYPE
. There are dozens of types of locks, however, most are system. The latter are usually very brief and does not help set the library cache, undo logs, etc. if you mess with
There are only three types of blocking, TX, TM and UL. UL is a block defined by the user-defined package that DBMS_LOCK. TX is a block of transaction records, is obtained each time a transaction is to alter data, regardless of the number of objects that will be involved in that transaction. columns ID1 and ID2 point to the rollback segments and the table entries transaction of that transaction.
TM is a DML lock. Obtained each time an object is altered. ID1 column
identifies the object in question.
Lock Modes can see more information by observing the lock modes TM and TX.
columns and LMOD REQUEST use both the same number as the lock modes in order of incremental exclusive: from 0 for non-blocking, up to 6 for exclusive lock. A session should be an exclusive lock TX so that you can change the data; LMOD
be 6. If you can not get exclusive lock because some of the records you want to alter are blocked by another session, then make the request for exclusive TX;
LMOD be 0 because it is not blocking, and REQUEST be 6. This can be seen in the previously executed query
KADDR SID TY ID1 ADDR ; ID2 LMOD REQUEST BLOCK STEM
-------- -------- ------- -- ------- ------- -------- -------- ------- ------- 2887B03C 2887B068 144 TX 196639 3891 0 6 323 0 27CF5D14 27CF5D54 TX 196 639 3891 6 137 , 0570 1 Session 2
observes that
ID1 and ID2 , which is requesting a lock TX ( LMOD = 0, REQUEST = 6), points to the entry of transactions and rollback of Session 1 that is what determines the session Session 2 blocks. may also find yourself with TX in mode 4 blocks of shared mode (Shared). If a block does not find any tickets available Invoked Transaction List (ITL - Interest Transaction List) and have records that will alter, then the session gets a TX lock in mode 4 while waiting for input (ITL). If you notice that there is contention on a lock object for TX-4, you will probably need to increase the INITRANS parameter . Locks TM
generally are required and acquired in mode 3, also called an exclusive shared registry (Shared-Row Exclusive) and Mode 6.
DDL operations require exclusive lock TM. (Note that the CREATE TABLE statement does not require a lock-TM and is not needed because the object in question does not exist yet!) DML operations require a Shared-Row Exclusive lock. Selected records up on v $ lock
, you can see from the TM lock levels that are of type ADDR KADDR DDL operations require exclusive lock TM. (Note that the CREATE TABLE statement does not require a lock-TM and is not needed because the object in question does not exist yet!) DML operations require a Shared-Row Exclusive lock. Selected records up on v $ lock
DML ; SID TY ID1 ID2 LMOD STEM REQUEST ; BLOCK -------- -------- ------- -- -------- -------- ------- -------- ------- ------- 0E3CBE90 0E3CBEC0 144 TM 69948 0 3 0 323 0 0E3CBE90 0E3CBEC0 137 TM 69948 0 3 0 570 0 Finding the locked object Now we know that each record points to an object TM locked, you can use the field to identify
ID1. SQL> select object_name from dba_objects WHERE object_id = 69948;
-------------------------- OBJECT_NAME ------------------------------------- EMP is sometimes enough just to know what the object is, however, can further deepen their identifying not only the object but also the data block and even the accurate record on the waiting Session 2. Finding
locked record
This information is obtained from v $ session
SQL> select row_wait_obj #, row_wait_file #, row_wait_block # #
row_wait_row 2 from v $ session 3 WHERE sid = 144; ROW_WAIT_FILEROW_WAIT_OBJ # # # ROW_WAIT_BLOCK ROW_WAIT_ROW #
----------- - -------------- --------------- -------------
69 948 4 ; 147 0
it returns the object ID, the file number on the block in the data file and record in the block is waiting for the session. If this information sounds familiar, it is because these are the four components of an extended ROWID. You can build the extended ROWID of the record from these components using DBMS_ROWID package. ROWID_CREATE function takes these data as arguments to return the
ROWID SQL> select do.object_name, row_wait_obj #, row_wait_file #, row_wait_block #, row_wait_row #, 69 948 4 ; 147 0
it returns the object ID, the file number on the block in the data file and record in the block is waiting for the session. If this information sounds familiar, it is because these are the four components of an extended ROWID. You can build the extended ROWID of the record from these components using DBMS_ROWID package. ROWID_CREATE function takes these data as arguments to return the
2 ; dbms_rowid.rowid_create (1, row_wait_obj #, row_wait_file #, row_wait_block #, row_wait_row #)
3 from v$session s, dba_objects do
4 where sid = 144
5 and s.row_wait_obj# = do.object_id;
OBJECT_NAM ROW_WAIT_OBJ# ROW_WAIT_FILE# ROW_WAIT_BLOCK# ROW_WAIT_ROW# DBMS_ROWID.ROWID_C ---------- ------------- -------------- --------------- ------------- ------------------EMP 69948 4 147 0 AAARE8AAEAAAACTAAA
with which we can inspect the registry directly
SQL> select * from scott.emp WHERE rowid = 'AAARE8AAEAAAACTAAA';
EMPNO ENAME ; JOB MGR HIREDATE ; SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
With the above you can identify a session that is blocking and how to get the record that is blocked waiting for the session.
Subscribe to:
Post Comments (Atom)
0 comments:
Post a Comment