Anuncios en tutorial de programación PLSQL

martes, 17 de octubre de 2017

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.

Bloqueos en una base de datos Oracle

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.

8 comentarios:

Orlando Echeverria dijo...

Gracias por el articulo, me parecio muy bueno he detectado los usuarios que me bloqueaban la base de datos y he podido kill dichos procesos, una ultima pregunta podria automatizar este proceso si se origina un bloqueo automaticamente kill.

Pepelu dijo...

Todo se puede automatizar pero yo no te aconsejaría hacerlo, podrías hacer un kill de alguna sesión que esté bloqueando a otra y al hacerlo montar un buen estropicio.

Matar sesiones requiere estar muy seguro de que la sesión se puede verdaderamente eliminar.

Diegoge dijo...

Buen artículo, claro y preciso!
Gracias por el aporte.

Saludos

Diego.

julius dijo...

Hola, muy interesante el articulo me sirvio de mucho pero quisiera saber si un objeto ya bloqueado puedo desbloquearlo?, asi como una tabla, un paquete, un procedimiento almacenado, una funcion.

Pepelu dijo...

Hola Julius, cuando una sesión deja bloqueado un objeto de la base de datos Oracle para desbloquearlo lo único que hay que hacer es matar la sesión.

intercom dijo...

Hola que tal, buen articulo y excelente pagina. disculpa tu sabrás como puedo bloquear un inicio de session desde sql developer... sucede que se conectan a mi base con usuario SYSTEM desde ese programa y quiero retringirlo. eh creado un trigger after logon y dentro hecho un kill a la session, pero no bota la session.

Tom dijo...

Muy buen artículo!! espero los siguientes :)

Gil Armando Viesca de Lira dijo...

Hola buen dia,
tengo una duda que quiza salga un poco fuera del tema,
pero si tengo un trigger que apunta a una tabla la cual desde Forms tiene un RECORD_SCOPE este registro marcaria error debido a que se bloquea desde el record_scope? o no deberia de tener ningun problema?