Nueva funcionalidad para el SELECT FOR UPDATE (SKIP LOCKED)

En muchas ocasiones tuve la oportunidad de revisar código pl/sql en donde se programa, entre otras cosas, la "marcación" de registros por medio de un flag. Estas marcas, en general se implementan modificando una columna (ej: estado) donde se denota en que etapa del procesamiento se encuentra la sesión y así evitar solapamientos con otras sesiones paralelas que esten haciendo lo mismo.

La forma mas común que yo he visto para realizar la operatoria descripta es usando "SELECT ... FOR UPDATE NOWAIT" del registro de la tabla maestra para asegurar que las demás sesiones no puedan procesar dicho registro. Una vez que el registro se procesó, las otras sesiones podrán tomar el siguiente registro disponible para procesar. Si no se requiere un orden para procesar, este enfoque atenta contra el paralelismo real. Esto se da porque mientras una sesión este procesando un registro las otras deberan esperar a que se commitee para poder procesar el siguiente registro. Esto sucede porque Oracle no lockea los selects, entonces aunque el proceso este procesando un registro dado, no se puede "saltear" y tomar el siguiente, sino que se devuelve un error de que el recurso esta siendo usado (ORA-00054 recurso ocupado y obtenido con NOWAIT).

A partir de 11g se documentó una opción (por lo que pude probar existe desde 9i pero no estaba documentada) muy interesante para lidiar con este tipo de procesamiento, que en realidad es una extensión de la sintaxis del for update para permitir, justamente, para poder procesar con mucho mayor grado de paralelismo y asi posibilitar que cada sesion "saltee" el registro en procesamiento y tome el próximo disponible para procesar. Esto mejora sensiblemente los tiempos de procesamiento general, ya que se podrán levantar n sesiones en paralelo minimizando la interdependencia entre ellas.

Abajo les muestro un ejemplo:

Creo una tabla T y le inserto 100 registros:

create table t (id int,
                estado char(1),
                fecha date,
                importe number(8,2))


insert into t
select rownum,
      'C',
      sysdate+dbms_random.value(-50,50),
      dbms_random.value(1,1000000)
from dual
connect by rownum <= 100  

Cambio el estado de 10 filas, elegidas al aleatoriamente. Dichas filas quedarán en estado 'P', suponiendo que el estado 'P' es disponibles para procesar.

create view t_v as 
select id
from t 
order by dbms_random.value  

update t
set estado = 'P'
where id in (select id from t_v) 
and rownum <= 10  


select * from t where estado = 'P';         

       ID E FECHA        IMPORTE
---------- - --------- ----------
        1 P 20-DIC-10  888292.36
       27 P 09-MAR-11  845864.47
       39 P 19-ENE-11  583901.49
       52 P 23-FEB-11  157817.12
       62 P 05-ENE-11   680744.2
       63 P 19-ENE-11  679375.69
       73 P 20-ENE-11   750069.3
       87 P 26-FEB-11  783555.02
       96 P 13-DIC-10  973668.87
      100 P 28-FEB-11  756671.07

En una consola ejecutamos el siguiente bloque pl, para tomar el siguiente registro a procesar (Sesion 1)

declare
   cursor l_cur is
   select  *
   from  t
   where estado = 'P'
   for update nowait skip locked;
   l_rec l_cur%rowtype;
begin
      open l_cur;
      fetch l_cur into l_rec;   
      -- 
      dbms_output.put_line (l_rec.id);
end;

Resultado: 1

En otra sesion (Sesion 2) ejecutamos el mismo bloque pl:

declare
   cursor l_cur is
   select  *
   from  t
   where estado = 'P'
   for update nowait skip locked;
   l_rec l_cur%rowtype;
begin
      open l_cur;
      fetch l_cur into l_rec;   
      -- 
      dbms_output.put_line (l_rec.id);
end;

Resultado: 27

La sesión 1 tomó el registro con id=1 y la sesión 2 tomó el registro con id=27, que son el primero y segundo respectivamente en el listado de mas arriba. Claramente no se commiteo nada y sin embargo la sesion 2 pudo tomar un nuevo registro para procesar mientras la sesión 1 estaba procesando. Con el select for update convencional la sesión 2 hubiese fallado y por código se deberia volver a intentar hasta que la sesión 1 libere el registro (commit/rollback) con id=1 y asi permitir pasar al siguiente.