Objetos bloqueados en una base de datos (1)

Todo el mundo ha intentado hacer alguna llamada desde su móvil y dicha llamada no ha podido realizarse por problemas de congestión, es decir, otros usuarios han copado los canales disponibles y nosotros no hemos podido tomar posesión de ninguno de ellos. Algo parecido puede ocurrir con las sesiones de Oracle (o de cualquier otra base de datos), ya que una sesión puede bloquear (mantener un "lock") un objeto de la base de datos (tabla, columna, etc) evitando que otra sesión pueda utilizarlo.

En esta nota voy a contar como identificar que sesión es la causante del bloqueo y en posteriores mensajes iré más allá y contaré como identificar el objeto que está bloqueado.

Creación de un “lock”

Para empezar voy a simular una situación de bloqueo entre dos sesiones:

Sesión 1

SQL> create table test_bloqueo (test number, bloqueo varchar2(10));

Table created.

SQL> insert into test_bloqueo values (1, 'Hola');

1 row created.

SQL> insert into test_bloqueo values (2, 'Adios');

1 row created.

SQL> select * from test_bloqueo;

TEST BLOQUEO
---- ----------
1    Hola
2    Adios

2 rows selected.

SQL> commit;

A continuación creamos el "lock" sobre la tabla:

SQL> select * from test_bloqueo for update;

La claúsula “for update” es la encargada de creae el "lock" sobre la tabla test_bloqueo.

Sesión 2

Ahora intentaremos actualizar un registro de la tabla test_bloqueo:

SQL> update test_bloqueo set test=3 where bloqueo='Hola';

Este último comando se quedará colgado bloqueado por el "lock" establecido en la primera sesión.

¿Cómo identificar la sesión bloqueadora?

Oracle proporciona una vista (view), DBA_BLOCKERS, que muestra la lista de SIDs de todas las sesiones que están bloqueando algún objeto. El problema es que consultar esta “view” es, según mi experiencia, bastante más lento que consultar directamente la tabla V$LOCK, y además dicha “view” da mucha menos información que la mencionada tabla.

Esto es lo que podemos ver consultando la tabla:

SQL> select * from v$lock ;

ADDR     KADDR    SID TY ID1    ID2    LMODE REQUEST CTIME BLCK
-------- -------- --- -- ------ ------ ----- ------- ----- ----
B765B198 B765B1AC 779 TM 6442   0      3     0       11543 0
01AA2F00 01AA2FCC 611 TX 262160 653305 6     0       54537 1
B6047A58 B6047A68 699 TX 262160 653305 0     6       54535 0
...............................................................

Si una sesión mantiene un “lock” que está bloqueando a otra sesión entonces la columna BLOCK mostrará el valor 1. Además, se puede determinar que sesión está siendo bloqueada si comparamos las columnas ID1 e ID2, ambas columnas mostrarán los mismos valores tanto para la sesión bloqueadora como para la sesión bloqueada. Ésta última además, puesto que esta solicitando un “lock” que no puede conseguir, tendrá un valor en la columna REQUEST mayor que cero.

Los resultados del comando “select” nos muestran que la sesión con SID 611 esta bloqueando la sesión con SID 699. La sesión 611 se corresponde con la sesión 1 de nuestro ejemplo y la 699 se corresponde con la sesión 2.

Pero existe una solución todavía más eficaz para determinar que sesión bloquea a cual. Podemos construir el siguiente comando “select”:

SQL> select lock1.sid, ' BLOQUEA ', lock2.sid
2 from v$lock lock1, v$lock lock2
3 where lock1.block =1 and lock2.request > 0
4 and lock1.id1=lock2.id1
5 and lock1.id2=lock2.id2;

       SID 'BLOQUEA'        SID
---------- --------- ----------
       611 BLOQUEA          699

1 row selected.

Incluso podemos construir una “query” mucho mas legible:

SQL> select s1.username '@' s1.machine
2 ' ( SID=' s1.sid ' ) esta bloqueando '
3 s2.username '@' s2.machine ' ( SID=' s2.sid ' ) ' AS bloqueos
4 from v$lock l1, v$session s1, v$lock l2, v$session s2
5 where s1.sid=l1.sid and s2.sid=l2.sid
6 and l1.BLOCK=1 and l2.request > 0
7 and l1.id1 = l2.id1
8 and l2.id2 = l2.id2 ;

BLOQUEOS
---------------------------------------------------------------
TEST@chessy ( SID=611 ) esta bloqueando TEST@chessy ( SID=699 )

1 row selected.

Con esto doy por terminada esta primera nota acerca de los bloqueos en una base de datos. No obstante, todavía hay más informacion útil en la tabla V$LOCK pero esto será objeto de otra nota en mi blog.