Cursores con actualización óptima de filas sin bloqueos

En la entrada de hoy hablaré sobre un truco mediante el cual podremos actualizar los campos obtenidos de un cursor, de manera óptima, sin necesidad de bloquear las filas de las tablas referenciadas por el cursor.

Sabemos que es posible definir un cursor de manera que se puedan actualizar los campos que se estén referenciando en él. Por ejemplo:


CURSOR c_accepted_orders IS
    SELECT *
    FROM orders
    WHERE state = 'ACCEPTED'
    FOR UPDATE OF state;



Si recorremos el cursor y deseamos actualizar el campo postal_code, sólo habrá que codificar algo como esto:


FOR crec IN c_accepted_orders LOOP
    -- Procesar orden
    (...)
    IF procesed = 'OK' THEN
        UPDATE orders
        SET state = 'FINISHED'
        WHERE CURRENT OF c_accepted_orders;
    END IF;
    (...)
END LOOP;



La ventaja de este método está en su sencillez y rapidez, porque la sentencia UPDATE no necesita localizar la fila para actualizarla. Sin embargo, tiene un inconveniente: mientras el cursor permanezca abierto, las filas del cursor permanecerán bloqueadas. Esto en principio no parece demasiado problemático, pero lo cierto es que es posible que el procesamiento de cada fila del cursor sea muy complejo, o que el número de filas obtenidas sea muy elevado. Esto supone que es posible que mantengamos bloqueadas las filas durante un buen rato, cosa que no es nada recomendable. Es más, si por cualquier motivo el proceso se corta, tendremos un bonito bloqueo del que el DBMS dará cuenta, pero no todo lo rápido que desearíamos.

Está claro que en ocasiones no interesa utilizar la cláusula FOR UPDATE en cursores, así que no queda otra que actualizar la fila mediante una consulta. Para optimizarla propongo la utilización de la pseudocolumna ROWID, que identifica de manera unívoca cada fila de una tabla. Eso sí, será necesario renombrar la pseudocolumna para que no se confunda con el ROWID de la fila de la tabla a actualizar. El resultado podría ser algo como esto:


CURSOR c_accepted_orders IS
    SELECT o.*, o.rowid rowid_order
    FROM orders o
    WHERE o.state = 'ACCEPTED';

(...)

FOR crec IN c_accepted_orders LOOP
    -- Procesar orden
    (...)

    IF procesed = 'OK' THEN
        UPDATE orders
        SET state = 'FINISHED'
        WHERE ROWID = crec.rowid_order;
    END IF;

    (...)
END LOOP;