Anuncios en tutorial de programación PLSQL

lunes, 24 de julio de 2023

El paquete estándar DBMS_LOCK para sincronizar procesos

Hace 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.

Paquete estandar Oracle PL/SQL 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.

Procedimientos y funciones del paquete DBMS_LOCK

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.

3 comentarios:

drodriganez dijo...

Hola, es la primera vez que escribo en tu blog, pero lo utilizo a menudo, me es muy util.
Estoy intentando bloquear un procedimiento para que no se ejecute nunca de forma concurrente, pero el sistema me devuelve un error "PLS-00201: el identificador 'DBMS_LOCK' se debe declarar".

Cómo lo puedo solucionar???

JLPM dijo...

Hola Daniel,

Lo mas probable es que no tengas permisos de ejecución del paquete estándar PLSQL DBMS_LOCK.

Con una cuenta de DBA debes dar esos privilegios al esquema (usuario) bajo el que estás creando el procedimiento que incluye el identificador DBMS_LOCK:

GRANT EXECUTE ON dbms_lock TO [nombre de esquema];

Dimony dijo...

Hola buenas tardes.
Resulta que tengo un procedimiento que crea un JOB en tiempo de ejecución y este lanza un .sh de linux, se le pasan los parametros y para finalizar lo activo (enable).
Despues para ver si el job ha terminado o no hago un loop que esta haciendo consultas a la tabla dbs_scheduler_running_jobs hasta el momento en que no lo encuentra.
Entre medias de estos dos procedimientos le meto un dbms_lock_speep(1), por que antes de crear el el JOB ya esta ejecutando la siguiente linea de codigo.

¿Como podría hacer para que se esperara a activar el job y una vez terminado siguiera con la ejecucion del proceso?

Aqui muestro el ejemplo:

begin
v_id_job := ltrim(rtrim('SAFT_'|| g_user || '_' || v_fechahora));
dbms_scheduler.create_job(job_name => v_id_job,
job_type => 'EXECUTABLE',
number_of_arguments => 4,
job_action => '/u01/forms/erps/exe/saft_hash.sh',
enabled => FALSE,
auto_drop => TRUE);
exception
when others then
return 'Error';
end;

begin
-- Asignacion de parametros al JOB
for v_indice in 1..4 loop
v_valor := '';
case
when v_indice = 1 then
v_valor := '/home/oracle/DP770011314_private_key.pem';
when v_indice = 2 then
v_valor := ltrim(rtrim(g_ruta_directorio)) || '/' || ltrim(rtrim(v_id_hash)) || '.sha1';
when v_indice = 3 then
v_valor := ltrim(rtrim(g_ruta_directorio)) || '/' || ltrim(rtrim(v_id_hash)) || '.txt';
when v_indice = 4
then v_valor := ltrim(rtrim(g_ruta_directorio)) || '/' || ltrim(rtrim(v_id_hash)) || '.b64';
end case;

dbms_scheduler.set_job_argument_value(job_name => v_id_job,argument_position => v_indice,argument_value => v_valor);
end loop;

-- Activamos JOB
dbms_scheduler.enable(ltrim(rtrim(v_id_job)));
dbms_lock.sleep(1);

-- Compruebo cuando a terminado el job para poder continuar
v_flag := 1;
while 1 = v_flag loop
begin
select nvl(1,0)
into v_dummy
from dba_scheduler_running_jobs
where job_name = v_id_job;
exception
when no_data_found then
v_flag := 0;
when others then
v_flag := 1;
return 'Error';
end;
end loop;