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.

La importancia de ordenar adecuadamente las columnas cuando se define una tabla

Dependiendo del caso, hay que prestar suficiente atención en el orden en el que se definen las columnas en la etapa de diseño fisico de las tablas. Para poder entender la situación que planteo, primero seria bueno que les muestre como almacena Oracle las filas en los bloques.

Una fila se almacena en un bloque de la siguiente forma:

Primero se define el Encabezado (H) que guarda propiedades acerca de la fila en si misma, tales como la cantidad de columnas que tiene y el flag que determina si esta lockeada. Luego vienen los datos en formato de duplas (largo de la columna,contenido de la columna). Como cada columna puede tener diferentes largos, cada una de ellas consta de dos partes: el largo Lx y los datos en si mismo Dx. Dado que el motor de base de datos no conoce el offset de las columnas en la fila, tiene que comenzar desde la primera columna, ver el largo, desplazarse hasta donde se encuentra el dato del largo del segunda columna y asi siguiendo hasta encontrar la columna buscada. Abajo, les muestro como se guarda la fila:



Como se habrán dado cuenta, si se necesita buscar una columna que esta al final, Oracle tardará mucho mas que para buscar una columna del principio, este overhead no es despreciable y podría afectar la performance, sobre todo para aplicaciones con requerimientos de tiempos de respuesta muy bajos, del orden de los milisegundos. Es por eso que en ciertos casos es recomendable definir al principio las columnas con mayor tasa de referencia y al final las que sean menos frecuentemente consultadas.
Para que puedan observar el grado de impacto de un orden de columnas no optimo, voy a armar un ejemplo sencillo que se pueda entender mejor:

Voy a crear una tabla T con 200 columnas de tipo INT, y luego las voy a insertar 5000 filas:

Creo la tabla T con la primera columna X1

create table t (x1 int);

Para no escribir la ddl con las 200 columnas lo voy a hacer dinamicamente, agregando las 199 columnas restantes:

begin
for i in 2..200
loop
  execute immediate 'alter table t add x'||i||' int';
end loop;
end;
/

Ahora voy a insertar las 5000 filas, de forma tal de llenar todas las columnas con el mismo valor por fila.

begin
for i in 1..5000
loop
  insert into t(x1) values (i);
  for j in 2..200
  loop
     execute immediate 'update t set x'||j||' = '||i||' where x1 = '||i;
  end loop;
  commit;
end loop;
end;
/

Una vez creada y populada la tabla T, voy a ejecutar un bloque anonimo que realiza 1000 veces la suma de todas las filas para cada columna Xn:

declare
l_cnt int;
l_foo int;
l_stime int;
begin
for i in 1..200
loop
   l_stime := dbms_utility.get_time();
   for j in 1..1000
   loop
      execute immediate 'select sum(x'||i||') from t' into l_foo;
   end loop;
   insert into t2 values (i,dbms_utility.get_time()-l_stime));
   commit;
end loop;
end;
/

Curva de Comparación

En la curva de arriba el eje X mide la posición de la columna en la fila y el eje Y el tiempo de procesamiento en segundos (usando el bloque pl de arriba) para operar con la columna. Como se puede apreciar el tiempo de procesamiento es directamente proporcional a la ubicación de la columna en la fila.