INITRANS y el evento "enq: TX - row lock contention" | ExpoDBA

INITRANS y el evento "enq: TX - row lock contention"

INITRANS y el evento "enq: TX - row lock contention"

El evento de espera "enq: TX - row lock contention" corresponde a una transacción que está solicitando un lock sobre una row en un momento dado, y no puede acceder a ella.

Oracle aplica bloqueo de tablas a nivel de fila, y por lo tanto, 2 transacciones no pueden modificar una misma fila al mismo tiempo (Cosa que está muy bien).


Por lo tanto, si tengo una tabla con 10 filas y la sesion 1 realiza un update sobre la fila 9 (pero no da commit), y la sesion 2 intenta hacer un update sobre algún campo de la misma fila 9, la sesion 2 quedará esperando por "enq: TX - row lock contention", hasta que la sesion 1 haga commit o rollback sobre la transacción.

Lo mismo sucedería con cualquier otra transacción que quiera hacer una modificación con esa fila, todos quedarán esperando con el evento "enq: TX - row lock contention", y accederán de a una por vez a trabajar sobre la fila, siempre y cuando la sesion que realizó la modificación termine la misma con commit o rollback.
Una vez que esto suceda, la sesion 2 podrá hacer la modificación que le corresponda y continuar y así el resto de las sesiones encoladas.

Hasta acá va todo bien, este es el mecanismo normal de la base de datos para asegurar consistencia. Pero esta no es la única razón por la que puede aparecer este encolamiento, ya que existe una restricción de nivel de concurrencia a nivel de bloque, y esta restricción esta dada por la cantidad de ITL que tiene un bloque asignado.

¿Que son los ITL?
ITL es el acrónimo para "Interested Transaction List", Los ITL se almacenan en el header de cada bloque, y cada bloque tiene una cantidad de ITL's reservados desde el momento de creación del mismo (Creación de tabla o nuevo extent).
Los ITL son una estructura de 23 bytes, que almacena un ROWID, y un TRANSACTION ID.
Estos estan almacenados en forma de Array en el header de cada bloque.

Cada vez que una transacción intenta modificar una fila de un bloque, se ocupa uno de estos ITL indicando el ROWID a modificar y el ID de TRANSACCION que modifica.
Una vez finalizada la transacción este se libera y da lugar a que otra transacción ocupe su lugar.

¿Cuantas transacciones concurrentes pueden modificar filas en un mismo bloque simultaneamente?
Depende directamente de la cantidad de SLOTS ITL que existan en el bloque.

¿Cuantos ITL tiene un bloque?
Y aquí es donde entra en juego el INITRANS. Cada vez que se formatea un bloque, tanto sea por creación de tabla o nuevo extent, este bloque crea un header con metadata del bloque, donde estan incluidos los ITL. El parametro INITRANS indica la cantidad de ITL inicial que tendrá cada bloque.
Por lo tanto, una tabla con un INITRANS 5, tendrá 5 ITL creados por defecto.

Esta cantidad de ITL es la mínima y a medida que se necesiten más ITL se pueden ir reservando (esto se hace automático).


Este crecimiento tiene 2 limitaciones:

Limitación 1:Que exista espacio en el bloque para crear un nuevo ITL (En SEGMENT_SPACE_MANAGEMENT = MANUAL tener en cuenta el PCTFREE)
Limitación 2:
                9i y anteriores: Que se haya alcanzado cantidad de ITL = MAXTRANS
                10g y posteriores: Que haya alcanzado 255 ITL (MAXTRANS es OBSOLETO)

Entonces podemos decir lo siguiente:
1) Cuando se crea un bloque, este se crea con una cantidad de ITL = INITRANS.
2) Si el bloque tiene más concurrencia que lo que indica el initrans se genera un nuevo ITL dentro del header a menos que:
     a) No haya espacio disponible (Se haya llegado a ocupar todo el espacio disponible para la metadata del bloque y no hay 23 bytes disponibles para la creación)
     b) La cantidad de ITL generados es igual a MAXTRANS (o 255 en versiones 10g en adelante)

Ejemplo:

Supongamos que la tabla tiene 10 filas, está alojada en 2 bloques, donde tiene un solo ITL y nada de espacio para crear uno nuevo:

Bloque 1: filas del 1 al 5
Bloque 2: filas del 6 al 10

Ahora empezamos a trabajar con las sesiones:
Sesion 1: hace un update sobre una columna de la fila 9 (Es el bloque 2, y lo hace sin problemas)
Sesion 2: hace un update sobre una columna de la fila 10 (Es el bloque 2, y queda esperando por enq: TX - row lock contention)

Podemos ver que se trata de 2 transacciones aplicando sobre diferentes filas del mismo bloque.
La sesion 1 tomó el ITL disponible.
La sesion 2 No encuentra ningún ITL disponible, al intentar crearlo, se encuentra con que no hay espacio, por lo tanto queda esperando.

¿Que pasaría si tenemos INITRANS 2?
Ambas transacciones operan  sin problemas concurrentemente sobre el mismo bloque, ocupando cada una uno de los ITL.
Si una tercera sesion intenta modificar una fila distinta en el mismo bloque, quedaría esperando por "enq: TX - row lock contention".

Ahora veamos que pasa si hay espacio disponible para nuevos ITL:
En este caso la sesion intenta crear un nuevo ITL (No la sesion, para eso utiliza una función del kernel de Oracle), y si hay espacio disponible, lo crea y ejecuta concurrentemente.
Si no hay espacio disponible, por más que no se haya llegado al MAXTRANS (o a 255 ITL en 10g), el bloque queda saturado de transacciones concurrentes y el resto de las sesiones que ingresen en el bloque quedarán esperando por nuestro evento "enq: TX - row lock contention"

Hemos visto todos los casos posibles, y si falta alguno tenemos toda la información necesaria para poder deducirlo.


 


 IMPORTANTE: El valor de INITRANS se tiene en cuenta solo al momento de formatear los bloques, o sea, cada vez que se pide un nuevo extent, cuando se crea la tabla o cuando se hace un move del segmento, así que por más que se cambie el parametro a una tabla existente, los bloques ya creados seguirán con la misma cantidad de ITL hasta que se haga un mantenimiento con un move o alguna otra técnica para reubicar la tabla.
Los nuevos bloques utilizaran los nuevos parametros.


 

 

Caso de aplicaión Real:

1) Reclaman demoras en una aplicación
2) Verificamos esperas de las sesiones en la base y vemos los "enq: TX - row lock contention"

select w.event,count(9)
from gv$session s, gv$session_wait w
where s.sid=w.sid
group by w.event
order by 2;
EVENT                                      COUNT(9)
---------------------------------------- ----------
....
....
rdbms ipc message                                12
buffer busy waits                                29
cursor: pin S                                    53
latch: redo allocation                           79
log file sync                                    92
db file sequential read                         103
enq: TX - row lock contention                   140
SQL*Net message from client                    1174

 

Script: Cantidad de esperas por tipo
 

 

3) Identificamos al usuario, program y a que tabla intentan acceeder estas sesiones

SELECT object_name,   b.username, b.program, count(9)
from v$session b, dba_objects o
where o.object_id=ROW_WAIT_OBJ#
and event = 'enq: TX - row lock contention'
group by object_name,  b.username, b.program, ROW_WAIT_FILE#,ROW_WAIT_BLOCK#,ROW_WAIT_ROW#
order by 4;



OBJECT_NAME                    USERNAME             PROGRAM                                    COUNT(9)
------------------------------ -------------------- ---------------------------------------- ----------
TABLA_1                        USUER1               cargador@hadoopds (TNS V1-V3)                    60

Script:  Bloques que generan enq: TX - row lock contention modificada.

Identificamos que la tabla a la que intentan acceder es la tabla TABLA_1 y el que está mostrando estas esperas es la aplicación "cargador"

4) Hablando con el grupo de desarrollo de la aplicacion "cargador" nos comentan que aumentaron las ejecuciones en paralelo de la aplicación para ganar tiempo (no tuvieron en cuenta nuestras restricciones).
También verificamos con el grupo de desarrollo que las llamadas paralelas de la aplicación no intenten modificar la misma ROW, porque de ser así aqui termina el estudio y es responsabilidad de la aplicación tener en cuenta esta restricción.

5) Nos fijamos a que OBJECT, FILE, BLOCK están intentando acceder las sesiones que están en espera.

SELECT object_name,    ROW_WAIT_FILE#,ROW_WAIT_BLOCK#,ROW_WAIT_ROW#, count(9) cant, min(seconds_in_wait) min_time, max(seconds_in_wait) max_time
from v$session b, dba_objects o
where o.object_id=ROW_WAIT_OBJ#
and event = 'enq: TX - row lock contention'
group by object_name,   ROW_WAIT_FILE#,ROW_WAIT_BLOCK#,ROW_WAIT_ROW#
order by 1,5;



OBJECT_NAME                    ROW_WAIT_FILE# ROW_WAIT_BLOCK# ROW_WAIT_ROW#       CANT   MIN_TIME   MAX_TIME
------------------------------ -------------- --------------- ------------- ---------- ---------- ----------
TABLA_1                                  2828         1724614             0          9         12         18
TABLA_1                                  2959          984830             0         61         17        574


Script:  Bloques que generan enq: TX - row lock contention.


6) De acá podemos ver que todos quieren acceder a la tabla TABLA_1 pero a diferentes bloques.
En el ejemplo el bloque 984830 del datafile 2959 tiene 61 sesiones en espera, de las cuales la que hace menos tiempo que está en esta espera lleva 17 segundos y la que más tiempo espera lleva 574 segundos.

7) Identificado el bloque de contencion, vamos a ver que cantidad de ITL tiene generados.
Para hacer esto existe una utilidad para hacer un dump de un datafile

alter system dump datafile <file_id> block <Block_id>;

 

Script: Dump de un bloque

 

La llamamos con los parametros del bloque que queremos investigar

alter system dump datafile 2959 block 984830;


Esto nos genera un dump file que quedará en el directorio de udump (ver según versión su ubicación) con el nombre:

<bdSID>_ora_<OSPID>.trc, donde dbSID es el SID de la base de datos y el OSPID es el PID de la sesion actual.

 


En nuestro caso el file se llama oracle_ora_3339.trc

Buscamos dentro del trace el valor "itc", encontraremos algo como esto:

Block header dump:  0xe44f06fe
 Object id on Block? Y
 seg/obj: 0xeb9bb  csc: 0x6cc.c66f8d49  itc: 10  flg: E  typ: 2 - INDEX
     brn: 0  bdba: 0xe44f068a ver: 0x01 opc: 0
     inc: 0  exflg: 0

 Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
0x01   0x021a.012.0002e47a  0x3642f70c.f48c.01  CB--    0  scn 0x06cc.c61d62b8
0x02   0x032d.025.000041d0  0x42c21955.9b94.3d  ----    1  fsc 0x0001.00000000
0x03   0x01a6.01f.0004b8d1  0xfa818848.d413.15  C---    0  scn 0x06cc.c66f8d37
0x04   0x0179.006.00069802  0xfa839921.b87b.06  ----    1  fsc 0x0000.00000000
0x05   0x02e9.01f.00005ff3  0xe085d1fc.d11d.2e  ----    1  fsc 0x0000.00000000
0x06   0x0307.00e.00004c1d  0xe086b38e.b2f1.14  ----    3  fsc 0x0000.00000000
0x07   0x014f.02c.00089c62  0x3647d47a.6ef8.14  ----    3  fsc 0x0000.00000000
0x08   0x000c.012.0011aed0  0xf90600aa.7d0e.3f  ----    1  fsc 0x0000.00000000
0x09   0x02de.02b.00006d0c  0xfa078e5b.e503.04  ----    3  fsc 0x0000.00000000
0x0a   0x014c.004.0008f8c3  0x36479461.004f.11  ----    3  fsc 0x0000.00000000


El itc indica la cantidad de ITL reservados, y la lista de abajo indica que transacción tiene en ese momento cada uno de los ITL.
De acá podemos sacar que el bloque que tiene 61 sesiones en espera, solo tiene 10 ITL disponibles, y están utilizados.
Por lo tanto el nivel de transacciones simultaneas en un bloque necesitado por esta aplicacion, al menos en este momento es de
10 utilizados + 61 esperando: 71 sesiones concurrentes en un bloque.

9) Verificamos el INITRANS  de la tabla  y vemos que INITRANS=5  (es 10g por lo tanto MAXTRANS=255)

10) De lo observado, podemos decir que el bloque tuvo inicialmente 5 ITL, y a medida que fue demandado, se fueron incrementando los ITL hasta que se quedó sin espacio, por lo tanto no generó más de 10 ITL, y vemos que en un momento dado, el bloque que más concurrencia tiene requiere 71 ITL para que las sesiones no esperen.
Luego de chequear en varias oportunidades una concurrencia de 70 transacciones aproximadamente, podemos decir que si el bloque tuviese 80 ITL reduciríamos casi a 0 el evento "enq: TX - row lock contention" (pagando esto con espacio 23 bytes*80=1840 b, más de 1k por cada bloque), salvo picos de concurrencia que de todos modos la mejora sería más que notable.

11) Solución:
    Paso 1: Cambiar el parametro INITRANS de la tabla a 50 (40 de concurrencia promedio)
    Paso 2: Realizar un move de la tabla para que los bloques sean reformateados.(Recomiendo evaluar el uso de DBMS_REDEFINITION)
    Paso 3: Chequear estado de objetos dependientes.

Espero que les sirva y sea clara la explicación.
Cualquier duda, inquietud, estoy a su disposición en los comentarios.

PD: El Tucu tenía razón.