El paquete estándar DBMS_LOCK para sincronizar procesos

Paquete estandar Oracle PL/SQL DBMS_LOCKHace unas semanas alguien me preguntó como se podía determinar si un procedimiento PL/SQL (procedure PLSQL) estaba siendo ejecutado para evitar tener dos instancias del mismo proceso corriendo simultáneamente. Para manejar este tipo de situaciones, así como situaciones en las que queramos ejecutar procedimientos y funciones de forma secuencial y sincronizar diferentes procesos, Oracle dispone de el paquete de funciones y procedimientos DBMS_LOCK.
Con el paquete DBMS_LOCK podemos establecer bloqueos de usuario (PL/SQL User Locks (UL)) con los que podremos parar la ejecución de un procedure si al ejecutarlo nos encontramos con que un determinado bloqueo o lock está en proceso.

A continuación os describo los tres procedimientos y funciones del paquete DBMS_LOCK más importantes:

DBMS_LOCK.allocate_unique (nombre_lock, id_lock, expira_seg): Este procedimiento lo que hace es asignar un identificador único (id_lock) para el nombre de bloqueo (nombre_lock) que hemos utilizado como parámetro de entrada. Por lo tanto, id_lock se trata de un parámetro de salida, y los valores que puede tomar están comprendidos en el rango de 1073741824 a 1999999999. Por otro lado, el parámetro expira_seg es opcional e indica el número de segundos que se mantendrá el bloqueo una vez que haya sido solicitado, si no se especifica, el valor por defecto es de 864000 segundos (10 días).

DBMS_LOCK.request (id_lock, tipo_lock, timeout, unlock_on): Con esta función lo que hacemos es solicitar el establecimiento de un bloqueo de usuario.

Veamos el uso de los distintos parámetros:

  • id_lock: Valor del identificador único generado anteriormente mediante la ejecución del procedimiento DBMS_LOCK.allocate_unique.
  • tipo_lock: Permite definir el tipo de bloqueo que queremos establecer. Puede tomar los siguientes valores: 2 – Row Share (SS), 3 – Row Exclusive (SX), 4 – Share (S), 5 – Share Row Exclusive (SSX) y 6 – Exclusive (X). En la mayoría de los casos utilizaremos el valor de 6 para establecer un bloqueo exclusivo.
  • timeout: Número de segundos que queremos permanecer esperando a que se libere el bloqueo si nos encontramos con que otro idéntico ha sido solicitado anteriormente y está todavía en marcha. Si no especificamos ningún valor, la función permanecerá en espera hasta que el bloqueo se libere (el valor por defecto que toma la variable timeout es muy alto).
  • unlock_on: Indica si el bloqueo se debe liberar al ejecutar los comandos COMMIT o ROLLBACK (valor TRUE) o si debe mantenerse aunque ejecutemos alguno de los comandos mencionados (valor FALSE). El valor por defecto es FALSE.

Los valores que puede devolver esta función son los siguientes:

  • 0: Bloqueo establecido satisfactoriamente.
  • 1: El bloqueo no se pudo establecer por superarse el tiempo de espera (timeout).
  • 2: Bloqueo multiple (deadlock). Aparece cuando se ha excedido el límite de peticiones de bloqueo permitidas sobre el mismo identificador.
  • 3: Parámetro erróneo o inválido.
  • 4: Aparece cuando en una misma sesión se solicita el mismo bloqueo por segunda vez sin haber liberado la primera petición.
  • 5: Identificador de lock ilegal.

DBMS_LOCK.release (id_lock): Esta función se utiliza para liberar un bloqueo previamente establecido con la función DBMS_LOCK.request. La función puede devolver los valores 0, 3, 4 ó 5. El significado de dichos valores es exactamente el mismo que en la función DBMS_LOCK.request salvo el valor 4, que en este caso indica que no somos los dueños del lock y no podemos liberarlo.

Ejemplo práctico del uso del paquete DBMS_LOCK

En una primera sesión ejecutamos:


DECLARE
  v_lockhandle VARCHAR2(200);
BEGIN
  DBMS_LOCK.allocate_unique
    ('Mi_Procedure', v_lockhandle);
  DBMS_OUTPUT.put_line
    ('id_lock = '||v_lockhandle);
  DBMS_OUTPUT.put_line
    ('request status = ' ||
     DBMS_LOCK.request(v_lockhandle, 6));
END;

La salida por pantalla tras la ejecución del processo sería:

id_lock = 1074484814107448481486
request status = 0

A continuación en la tabla V$LOCK podemos identificar el bloqueo en curso de la siguiente manera (ojo, sólo hay que utilizar los diez primeros caracteres del id_lock):

SELECT sid, type, id1, lmode,
       request, ctime, block
FROM   v$lock
WHERE  id1 = '1074484814'

SID TYPE ID1        LMODE REQUEST CTIME BLOCK
117 UL   1074484814     6       0   201     0

Si ejecutamos el mismo proceso sobre una segunda sesión, observaremos que dicho proceso se queda esperando a que el bloqueo establecido en la primera sesión se libere. En la tabla V$LOCK veremos que aparece una nueva línea en espera de establecer un bloqueo exclusivo (lmode = 0, request = 6):

SID TYPE ID1        LMODE REQUEST CTIME BLOCK
117 UL   1074484814     6       0   790     1
 97 UL   1074484814     0       6     9     0

Ahora si abrimos una tercera sesión y ejecutamos:

DECLARE
  v_lockhandle VARCHAR2(200);
BEGIN
  DBMS_LOCK.allocate_unique
    ('Mi_Procedure', v_lockhandle);
  DBMS_OUTPUT.put_line
    ('id_lock = '||v_lockhandle);
  DBMS_OUTPUT.put_line
    ('request status = ' ||
    DBMS_LOCK.request(v_lockhandle, 6, 20));
END;

El proceso se quedará en espera durante 20 segundos (observad que he utilizado el parámetro de timeout con el valor de 20). Transcurrido este tiempo el proceso terminará y obtendremos la siguiente salida:

id_lock = 1074484814107448481486
request status = 1

Es decir, el proceso DBMS_LOCK.request devuelve el valor “1” indicando que el establecimiento del bloqueo falló por timeout. Por otro lado, durante esos 20 segundos de espera, aparecerá una tercera línea en la tabla V$LOCK (lmode = 0, request = 6), que no considero necesario mostrar.

Ahora, si en la primera sesión liberamos el bloqueo ejecutando:

DECLARE
  v_lockhandle VARCHAR2(200) := '1074484814';
BEGIN
  DBMS_LOCK.release(v_lockhandle);
END;

Entonces observaremos que, al liberarse el bloqueo, la ejecución del proceso que estaba en espera en la segunda sesión termina. En la tabla V$LOCK nos encontraremos con lo siguiente:

SID TYPE ID1        LMODE REQUEST CTIME BLOCK
 97 UL   1074484814     6       0     3     0

Es decir, ahora el bloqueo exclusivo (lmode = 6) está establecido por la segunda sesión.

Artículos relacionados:
El paquete estándar PLSQL DBMS_SQL.

El paquete estándar PLSQL DBMS_OUTPUT.