¿Quien bloqueó mi sesión?

Recientemente me encontré con el siguiente artículo de Natalka Roshak publicado en orafaq.com que espero les sea de utilidad.

Cuando un usuario o desarrollador te dice que tiene su sesión bloqueada, —entendiendo por bloqueo la ejecución de una instrucción DML 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.

Creando el bloqueo

Usaré la tabla emp del esquema SCOTT. Abro una sesión de SQL*Plus y configuro los siguiente parámetros

SQL> set sqlprompt "SESION1> "
SESION1> set linesize 200

actualizo todos los registros

SESION1> update emp
  2  set sal=1000*1.05;

14 rows updated.

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.