Cuando un usuario o desarrollador te dice que tiene su sesión bloqueada, —entendiendo por bloqueo la ejecución de una instrucción
la cual después de lanzarla deja de responder— sabes cuan útil puede ser identificar no solo quien lo está haciendo, sino también cual objeto es ó mejor aún, puedes identificar el registro exacto que tu sesión está esperando bloquear.
. Abro una sesión de SQL*Plus y configuro los siguiente parámetros
sin dar commit abro otra sesión de SQL*Plus actualizo la variable
SQL> set sqlprompt "SESION2> "
SESION2>
y en esta actualizo sólo un registro
SESION2> update emp
2 set sal=1000
3 where empno=7369;
después de presionar ENTER no regresa de immediato al prompt, he aquí el bloqueo.
Identificando la sesión que está bloqueando
En una nueva sesión de SQL*Plus revisas el contenido de v$lock que muestra los bloqueos que existen actualmente en la base así como la relación entre quien bloquea a quien aunque no de manera explícita
SQL> select * from v$lock;
ADDR KADDR SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK
-------- -------- ------- -- ------- ------- ------- --------- ------- -------
........ ........ ....... .. ....... ....... ....... ......... ....... .......
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 137 TX 196639 3891 6 0 570 1
observa la última columna BLOCK, si una sesión mantiene un bloqueo que a su vez bloquea a otra verás que BLOCK=1. Llendo más allá, puedes ver cuál sesión es la que está siendo bloqueada comparando ID1 e ID2. La sesión bloqueada tendrá los mismos valores en ID1 e ID2 que la que bloquea, y, ya que está haciendo la petición de bloqueo que no puede obtener, tendrá REQUEST>0.
En el query anterior puedes ver que el SID 137 está bloqueando al SID 144. SID 137 corresponde a SESION1 y SID 144 es SESION2 que está bloqueada.
Para no hacer la comparación sólo viendo los datos de ese query puedes ejecutar lo siguiente
SQL> select l1.sid || ' ESTA BLOQUEANDO ' || l2.sid
2 from v$lock l1, v$lock l2
3 where l1.block = 1
4 and l2.request > 0
5 and l1.id1 = l2.id1
6 and l1.id2 = l2.id2;
L1.SID||'ESTABLOQUEANDO'||L2.SID
-----------------------------------
137 ESTA BLOQUEANDO 144
ó mejor aún, si se incluye la tabla v$session la información es aún más legible
SQL> select s1.username || '@' || s1.machine
2 || ' ( SID=' || s1.sid || ' ) esta bloqueando '
3 || s2.username || '@' || s2.machine || ' ( SID=' || s2.sid || ' ) ' as estado_bloqueos
4 from v$lock l1, v$session s1, v$lock l2, v$session s2
5 where s1.sid = l1.sid
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 ) esta bloqueando SCOTT@TESTSERVER ( SID=144 )
Hay aún más información en la tabla v$lock, pero para leerla, es neceario entender un poquito más sobre los tipos de bloqueos y los crípticas columnas ID1 e ID2
Tipo de bloqueo y las columnas ID1 / ID2
En este caso el tipo de bloqueo es DML exclusivo ya que fuí yo quien lanzó la instrucción que bloquea. Sin embargo, su puede obtener esta información de v$lock sin tanto esfuerzo.
Lo primero es ver la columna TYPE. Hay decenas de tipos de bloqueos, sin embargo, la gran mayoría son de sistema. Estos últimos por lo general son muy breves y no ayuda mucho ajustar el library cache, undo logs, etc. si te metes con v$lock.
Sólo hay tres tipos de bloqueos de usuario, TX, TM y UL. UL es un bloqueo definido por el usuario —aquel definido con el paquete DBMS_LOCK. TX es un bloqueo de transacción de registros; se obtiene cada vez que hay una transacción para alterar datos, sin importar la cantidad de objetos que intervendrán en esa transacción. Las columnas ID1 e ID2 apuntan a los segmentos de rollback y a la tabla de entradas de transacciones de esa transacción.
TM es un bloqueo DML. Se obtiene cada vez que un objeto es alterado. La columna ID1 identifica el objeto en cuestión.
Modos de bloqueo
Se puede ver más información observando los modos de bloqueo TM y TX. Las columnas LMODE y REQUEST usan ambas la misma numeración que los modos de bloqueo, en orden de exclusividad incremental: desde 0 para sin bloqueo, hasta 6 para bloqueo exclusivo. Una sesión debe hacerse de un bloqueo exclusivo TX para que pueda alterar los datos; LMODE será 6. Si no puede obtener el bloqueo exclusivo debido a que algunos de los registros que quiere alterar están bloqueados por otra sesión, entonces hará la petición TX en modo exclusivo; LMODE será 0 ya que no está bloqueando, y REQUEST será 6. Esto se puede observar en el query ejecutado anteriormente
ADDR KADDR SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK
-------- -------- ------- -- ------- ------- -------- -------- ------- -------
2887B03C 2887B068 144 TX 196639 3891 0 6 323 0
27CF5D14 27CF5D54 137 TX 196639 3891 6 0 570 1
Observa en SESION2 que ID1 e ID2, la cual está solicitando un bloqueo TX (LMODE=0, REQUEST=6), apunta hacia las entradas de transacciones y rollback de SESION1 que es lo que permite determinar la sesión que bloquea a SESION2.
Es posible que te encuentres también con bloqueos TX en modo 4 de modo compartido(Shared). Si un bloqueo no encuentra ninguna entrada disponible en la Lista de Transacciones Invocadas(ITL - Interest Transaction List) y tiene registros que va a alterar, entonces la sesión obtiene un bloqueo TX en modo 4 mientras espera por la entrada(ITL). Si notas que hay contención sobre un objeto por bloqueos TX-4, es probable que necesites incrementar su parámetro INITRANS.
Los bloqueos TM generalmente son requeridos y adquiridos en modo 3, también llamado registro compartido exclusivo(Shared-Row Exclusive) y modo 6. Las operaciones DDL requieren de un bloqueo TM exclusivo. (Nótese que la instrucción CREATE TABLE no requiere de un bloqueo TM —y no lo necesita ya que el objeto en cuestión ¡no existe todavía!) Las operaciones DML requieren de un bloqueo Shared-Row Exclusive. De los registros seleccionados arriba sobre v$lock, puedes ver por lo niveles de bloqueo TM que son de tipo DML
ADDR KADDR SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK
-------- -------- ------- -- -------- -------- ------- -------- ------- -------
0E3CBE90 0E3CBEC0 144 TM 69948 0 3 0 323 0
0E3CBE90 0E3CBEC0 137 TM 69948 0 3 0 570 0
Encontrando el objeto bloqueado
Ahora que sabemos que cada registro TM apunta a un objeto bloqueado, se puede usar el campo ID1 para identificarlo.
SQL> select object_name from dba_objects where object_id=69948;
OBJECT_NAME
---------------------------------------------------------------
EMP
A veces es suficiente con sólo saber de cuál objeto se trata; sin embargo, su puede ahondar aún más, identificando no solo el objeto, sino también el bloque de datos e incluso el registro exacto por el que espera SESION2.
Encontrando el registro bloqueado
Esta información se obtiene de v$session mediante la entrada de la sesión bloqueada
SQL> select row_wait_obj#, row_wait_file#, row_wait_block#, row_wait_row#
2 from v$session
3 where sid=144;
ROW_WAIT_OBJ# ROW_WAIT_FILE# ROW_WAIT_BLOCK# ROW_WAIT_ROW#
------------- -------------- --------------- -------------
69948 4 147 0
esto regresa el ID del objeto, el número relativo de archivo, el bloque en el archivo de datos y el registro en el bloque que está esperando la sesión. Si estos datos te suenan conocidos, es porque estos son los cuatro componentes de un ROWID extendido. Se puede construir el ROWID extendido de dicho registro a partir de estos componentes usando el paquete DBMS_ROWID. La función ROWID_CREATE toma esos datos como argumentos para regresar el ROWID
SQL> select do.object_name, row_wait_obj#, row_wait_file#, 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
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
con el cual podemos inspeccionar directamente el registro
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
Con lo anterior puedes identificar una sesión que está bloqueando y cómo llegar hasta el registro que está esperando la sesión bloqueada.
