lunes, 21 de marzo de 2011

cambio en locking en 11gR2

Hola,

Hay un proceso en un cliente que no me gusta nada por la cantidad de eventos de espera :
"enq: TX - row lock contention"
que generan las sesiones. Pero así se ha decido hacer desde hace mucho tiempo.
Si tienen el placer de leer el libro "Expert Oracle Database Architecture" de Tom Kyte, él describe un método para bloquear y no perder información de "updates", en el capítulo 6. El señor Kyte llama a este tipo de bloqueos "Pessimistic Locking", en este caso no se agrega el "nowait" en la sentencia por lo que realmente queda a la espera. También existe en su blog una nota sobre "write consistency"que hace referencia a todo esto.
Por increíble que parezca este comportamiento de Locking ha cambiado (espero que temporalmente, y solo sea un BUG) en la versión 11gR2.
Después de comprobar que realmente cambiaba intenté contactar con la persona más importante en el mundo de Oracle Jonathan Lewis, para ver que parecía. No es nada común que conteste, porque no intenta ser como AskTom, además existe http://forums.oracle.com y a buenas preguntas suele responder.
Expongo el caso si están pensado en pasar a 11.2 y alguno de sus procesos actúa de esta forma, pueden probarlo para las versiones 10gR1, 10gR2, 11gR1 y 11gR2, en Windows, Linux, HP-UX, Solaris y AIX. El resultado es el mismo: la versión 11gR2 cambia.

Se tiene una tabla en el que tienes una columna con valores distintos, la que puede comportarse como una queue table, es decir, obtienes el valor mínimo de esa columna, trabajas con el resto de la información y borras esa filas. Puedes o no tener un índice y el comportamiento es el mimo.

La tabla es la siguiente:

SQL> create table tab1

2 (id number,

3 info varchar2(10),

4 constraint tab1_pk primary key (id)

5 using index

6* (create index idx_tab1_pk on tab1(id)))


La llenamos con unos pocos datos:

SQL> insert into tab1

2 select rownum, 'aaaaaa'||rownum

3 from dual

4 connect by rownum <=5;

5 rows created.


Y la consulta para obtener el bloqueo del mínimo es:

SQL> select id

2 from tab1

3 where id = (select min(id) from tab1)

4 for update;


El caso es que cuando se ejecutan varias sesiones a la vez, digamos 10, la primera consigue el bloqueo y se inicia la transacción, y el resto se quedan esperando a que termine la transacción. En el caso de 2 sesiones tenemos en 10g, 11gR1 y 11gR2, lo siguiente en V$LOCK:

V$lock

ADDR KADDR SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK

-------- -------- ---------- -- ---------- ---------- ---------- ---------- ---------- ----------

385247F0 3852481C 71 AE 99 0 4 0 533 0

3852494C 38524978 41 AE 99 0 4 0 291 0

385249C0 385249EC 41 TX 65561 166 0 6 144 0

00DE464C 00DE467C 41 TM 12467 0 3 0 144 0

00DE464C 00DE467C 71 TM 12467 0 3 0 219 0

37FD9C20 37FD9C60 71 TX 65561 166 6 0 219 1

Los tipos de bloqueos AE, solo aparecen en las versiones 11gR1 y 11gR2. Pero se aprecia claramente que la sesión 71 obtuvo el bloqueo exclusivo sobre la fila, y la sesión 41 está a pidiendo un bloqueo exclusivo sobre la misma.


Ahora se borra la fila con la que se ha bloqueado y se "confirma" la transacción:

SQL> delete tab1 where id=1;

1 row deleted.

SQL> COMMIT;

Commit complete.


En las versiones 10g y 11gR1, la sesión que estaba a la espera vuelve con:

SQL> select id

2 from tab1

3 where id = (select min(id) from tab1)

4 for update;

ID

----------

2


¿Qué ha pasado? En teoría la explicación que me han dado, es que cuando existe el cambio en el datos, Oracle relanza la sub-consulta y obtiene de nuevo un datos, para el que después realiza el bloqueo. Y ¿Cual es el problema? que en la versión 11gR2 el resultado es el siguiente:

No row selected.

Al ver este comportamiento y contactar con Mr. Lewis, intentamos buscar el motivo, y ver que se podía hacer para cambiar el comportamiento. Primero ver si el comportamiento proviene del optimizador, y lo más rápido era ver lo planes de ejecución, para la versión 10g y 11gR1:

--------------------------------------------------------------------------------------------

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

--------------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | | | 3 (100)| |

| 1 | FOR UPDATE | | | | | |

|* 2 | INDEX RANGE SCAN | IDX_TAB1_PK | 1 | 13 | 1 (0)| 00:00:01 |

| 3 | SORT AGGREGATE | | 1 | 13 | | |

| 4 | INDEX FULL SCAN (MIN/MAX)| IDX_TAB1_PK | 4 | 52 | 2 (0)| 00:00:01 |

--------------------------------------------------------------------------------------------


Y para la versión 11gR2:

----------------------------------------------------------------------------------------------

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

----------------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | | | 3 (100)| |

| 1 | FOR UPDATE | | | | | |

| 2 | BUFFER SORT | | | | | |

|* 3 | INDEX RANGE SCAN | IDX_TAB1_PK | 1 | 13 | 1 (0)| 00:00:01 |

| 4 | SORT AGGREGATE | | 1 | 13 | | |

| 5 | INDEX FULL SCAN (MIN/MAX)| IDX_TAB1_PK | 4 | 52 | 2 (0)| 00:00:01 |

----------------------------------------------------------------------------------------------


Al parecer había un cambio y esto era algo razonable por lo que la primera prueba consistía en usar el HINT

/*+ optimizer_feature_enable('10.2.0.4') */

Pero el comportamiento seguía igual. También existe el HINT /*+ precompute_subquery */ pero no cambia nada.
Después (un poco tedioso) era buscar en algún BUG solucionado y desactivar el parche con el parámetro "_fix_control" (mirar lock_horror) y encontré un que parecía que iba a cambiarlo:

v$system_fix_control

7306637 --> inject SORT BUFFER under FOR UPDATE

SQL> alter system set "_fix_control"='7306637:OFF' scope=spfile;
System altered.

Y efectivamente cambió el plan de ejecución. Pero el comportamiento no. Al final terminé desactivando todos los BUGNO que estaban en la versión 11.2.0.1 y sigue el comportamiento igual.
Oracle ya sabe de este cambio, y está reportado, por lo ahora estoy a la espera de un parche o algo para que regrese al comportamiento anterior.
Esto no será muy bueno para algunas aplicaciones, aunque seguro que en breve tendrán corregido este problema. Esto era solo una demostración que si tienes algo parecido, no pierdas más tiempo o no cambies tu aplicación.

También se he probado las sentencias :

delete tab2 where id = (select min(id) from tab2);

update tab1 set id=id+10

where id = (select min(id) from tab1);


Y sufre el mismo comportamiento, hay un cambio entre 10g|11gR1 y la 11gR2. La segunda sesión queda a la espera y al final en la versión 11gR2 no hay filas a borrar o a actualizar, mientras que en la anteriores re-ejecuta la consulta, obtiene el mínimo y borra o actualiza la fila.

John Ospino Rivas

No hay comentarios:

Publicar un comentario