Proceso multi-hilo en base de datos

Este artículo expone una problemática que en ocasiones surge en procesos críticos para una empresa. Si bien lo más normal suele ser implementar toda la lógica de los procesos en un único lenguaje en el lado servidor, a veces es necesario tener estos procesos centralizados, bien sea para evitar realizar varias transacciones o conexiones, o bien porque el proceso puede ser invocado por varias aplicaciones externas.

Una de las posibles soluciones, es implementar estos procesos en la propia base de datos Oracle o MySQL, desarrollando el módulo en PL/SQL.  PL/SQL es un lenguaje de programación propietario de las bases de datos de Oracle. Su sintaxis es una mezcla entre SQL y Pascal. En el caso de MySQL, tenemos los procedimientos almacenados, que si bien no son tan potentes como los de Oracle, nos deben permitir realizar procesos de cálculos como el que indicamos.

La ventaja que tiene implementar procesos de cálculo con grandes volúmenes de datos es que nos ahorramos todos los tiempos de transferir la información al servidor de aplicaciones para posteriormente volver a guardar la información con los resultados.

Supongamos un caso práctico en el que es necesario tratar los datos de una carga masiva de una encuesta, en la que el volumen de datos es de varios miles de registros. Con un volumen de esas dimensiones, la memoria utilizada y el volumen de datos entre el servidor Oracle y el servidor de aplicaciones sería bastante elevado, por lo que es una buena opción realizar todos los cálculos donde están ubicados los datos.

Supongamos que una vez desarrollado el proceso en PL/SQL se detecta que tarda varias horas en realizar el proceso de cálculo, por lo que se determina que una buena opción sería el poder dividir la información a procesar y que fuera procesada simultáneamente, es decir un sistema multitarea (multi-thread) o multiproceso.  Para poder modificar un proceso en multitarea, hemos de preparar el proceso para que reciba unos parámetros que nos indiquen que parte van a procesar.

 PROCEDURE funcion_procesa_datos 
 IS
 BEGIN

   …

 END;

Tenemos una función que al ser llamada, realiza una consulta (SELECT) a la base de datos y recorre registro a registro realizando varios cálculos con la información recuperada. Todas estas operaciones se ejecutarían en un hilo principal. Si queremos repartir el trabajo, tenemos que cambiar la función para que reciba un parámetro para filtrar el intervalo de información a procesar.

PROCEDURE funcion_procesa_datos( INI IN NUMBER, FIN IN NUMBER)
 IS
 BEGIN

   …

 END;

En la nueva función se hace también una consulta, pero se filtra por el campo nºorden. Con esto tenemos una función que sabe procesar sólo una parte de la función. El siguiente paso sería crear varios procesos que llamen a la función con parámetros distintos para que se procesen simultáneamente. En lenguajes de programación tipo Java o C se usan las clases Thread, mientras que en el caso de JavaScript es común utilizar setInterval() para hacer procesos en paralelo.

Pero ¿cómo crear estos hilos en Oracle o MySQL?

Oracle dispone de unos elementos denominados JOB, que permiten programar con qué frecuencia se ejecutan. La ventaja es que para cada job Oracle crea un nuevo hilo de proceso.

En el caso de MySQL un elemento de similares características son los EVENTS, muy similares a los jobs de Oracle, y que podemos crear para que se ejecuten una única vez.

El diagrama de funcionamiento sería:

Para crear un job en Oracle, utilizaremos el paquete DBMS_JOB con la función SUBMIT.

DBMS_JOB.SUBMIT( 
   job       OUT    BINARY_INTEGER,
   what      IN     VARCHAR2, NEXT_DATE IN DATE DEFAULTSYSDATE, 
   interval  IN     VARCHAR2 DEFAULT 'NULL',
   no_parse  IN     BOOLEAN DEFAULT FALSE,
   instance  IN     BINARY_INTEGER DEFAULT ANY_INSTANCE,
   force     IN     BOOLEAN DEFAULT FALSE);

El parámetro job nos devuelve el número de job creado. En what indicaremos el método que hayamos creado para realizar los cálculos. El resto de valores se pueden dejar con sus valores por defecto. Es necesario que después de crear cada job se realice un commit para que se active correctamente.

Una vez creados los diferentes hilos se realiza un control de espera hasta que todos los hilos hayan acabado, para retornar el control a la aplicación que lo ha ejecutado.

Para realizar este control se hará una consulta a la vista user_jobs filtrando los que contienen el nombre de nuestro proceso. Por ejemplo:

PROCEDURE procesar_datos
  IS
   vjob           NUMBER;
   num_procesos   NUMBER;
  BEGIN
    -- Iniciamos el primer hilo con valores de 0 a 2500
    DBMS_JOB.submit(vjob,'mi_proceso(0, 2500);');
    COMMIT;

    -- Iniciamos el segundo hilo con valores de 2500 a 5000
    DBMS_JOB.submit(vjob,'mi_proceso(2500, 5000);');
    COMMIT;

    -- Iniciamos el tercer hilo con valores de 5000 a 7500
    DBMS_JOB.submit(vjob,'mi_proceso(5000, 7500);');
    COMMIT;

    -- Iniciamos el cuarto hilo con valores de 7500 a 10000
    DBMS_JOB.submit(vjob,'mi_proceso(7500, 10000);');
    COMMIT;

    num_procesos := 4;

    WHILE num_procesos > 0 LOOP
      select count(*) into num_procesos 
      from user_jobs where what like ‘%mi_proceso%’;
      DBMS_LOCK.sleep(10);
    END LOOP;

  END;

Para el caso de MySQL usaríamos los eventos. Por defecto viene desactivado, así que necesitamos habilitarlo con el siguiente comando:

  SET GLOBAL event_scheduler = ON;

La sintaxis a utilizar para crear el evento sería:

CREATE
[DEFINER = { user | CURRENT_USER }]
EVENT
[IF NOT EXISTS]
event_name
ON SCHEDULE schedule
[ON COMPLETION [NOT] PRESERVE]
[ENABLE | DISABLE | DISABLE ON SLAVE]
[COMMENT 'comment']
DO sql_statement;

schedule:
AT timestamp [+ INTERVAL interval] ...
| EVERY interval
[STARTS timestamp [+ INTERVAL interval] ...]
[ENDS timestamp [+ INTERVAL interval] ...]

interval:
quantity {YEAR | QUARTER | MONTH | DAY | HOUR | MINUTE |
WEEK | SECOND | YEAR_MONTH | DAY_HOUR | DAY_MINUTE |
DAY_SECOND | HOUR_MINUTE | HOUR_SECOND | MINUTE_SECOND}

Aunque para crear un evento se puede prescindir de la mayoría de valores. El evento se autodestruirá al finalizar la ejecución, salvo que indiquemos ON COMPLETION PRESERVE.

  
 CREATE EVENT mi_proceso_1
 ON SCHEDULE AT now() + INTERVAL ‘5’ SECOND
 DO mi_proceso(0,2500);

Para realizar un control similar al que realizábamos con Oracle utilizaremos también una consulta.

  
  SELECT count(*) into num_procesos 
  FROM INFORMATION_SCHEMA.EVENTS
     WHERE EVENT_NAME like ‘mi_proceso%’;

En conclusión, en el momento de planificar un desarrollo hay que sopesar si es conveniente realizar procesos en la propia base de datos y, en el caso de que sea necesario, utilizar técnicas multi-hilo para mejorar el rendimiento.

Webgrafía

• http://docs.oracle.com/cd/B19306_01/appdev.102/b14258/d_job.htm#i1000390
• https://docs.oracle.com/cd/A97630_01/server.920/a96521/jobq.htm
• https://dev.mysql.com/doc/refman/5.7/en/create-event.html
• https://dev.mysql.com/doc/refman/5.7/en/events-table.html
• http://www.desarrolloweb.com/manuales/tutorial-oracle.html
• http://manuales.guebs.com/mysql-5.0/stored-procedures.html
• http://www.codejobs.biz/es/blog/2014/07/09/como-hacer-un-procedimiento-almacenado-en-mysql-sin-morir-en-el-intento
• https://msdn.microsoft.com/en-us/hh549259.aspx

Cita recomendada: PUERTAS ESTIVILL, Pedro. Proceso multi-hilo en base de datos. Mosaic [en línea], julio 2015, no. 129. ISSN: 1696-3296. DOI: https://doi.org/10.7238/m.n129.1519.