Espera por bloqueo de otra sesión

El tiempo esperando un lock de otra sesión no se resuelve en el SQL de quien espera: la causa está en la transacción que retiene el lock, y esa transacción no aparece en el trace. El trabajo es encontrar quién lo retiene y por qué.

Síntomas

En el Diagnóstico aparece el hallazgo Espera por bloqueo de otra sesión (BLOQUEIO). El texto dice cuánto del tiempo total quedó en la clase de espera Application, en porcentaje y en segundos, lista los eventos de la clase que pesaron y termina con Ver perfil por clase.

El hallazgo aparece cuando la clase Application pesa en el tiempo total. La gravedad es media y pasa a alta cuando la clase pesa más. Si un evento de la clase domina el perfil, el informe no repite el hallazgo de componente dominante: este hallazgo ya trata ese tiempo.

Entran en la clase Application estos eventos, según la wait_class de Oracle 19c:

• enq: TX - row lock contention: fila modificada por otra transacción que todavía no terminó.
• enq: TM - contention: lock de la tabla entera.
• enq: UL - contention: lock pedido por la propia aplicación con DBMS_LOCK.
• enq: KO - fast object checkpoint y enq: RO - fast object reuse: escritura o descarte de los bloques del objeto antes de una lectura directa, un TRUNCATE o un DROP.
• SQL*Net break/reset to client y SQL*Net break/reset to dblink: llamada interrumpida o error devuelto al cliente.

Un evento que el informe no clasifica aparece en el perfil como No clasificado y no cuenta para este hallazgo.

Causa

La sesión pidió un lock que otra sesión retenía y quedó detenida hasta que se liberó. El título del hallazgo habla de bloqueo, pero el evento que pesa dice qué pasó:

• enq: TX - row lock contention: transacción larga o commit tardío reteniendo filas; varias sesiones actualizando el mismo registro (contador, saldo, siguiente número de documento); dos sesiones insertando la misma clave única.
• enq: TM - contention: casi siempre una clave foránea sin índice. Al modificar la clave o borrar la fila de la tabla padre, Oracle bloquea la tabla hija entera. LOCK TABLE y DDL también causan esta espera.
• enq: UL - contention: el proceso que retiene el lock de la aplicación tarda en soltarlo.
• SQL*Net break/reset to client: no es bloqueo. La llamada se interrumpió o devolvió un error a la aplicación.

Solución

Empiece por el evento que el hallazgo lista primero. Los casos van del más común al menos común.

Encontrar la sesión que retiene el lock

Mientras la espera ocurre, la sesión bloqueada muestra quién la retiene. Ejecute en la base de datos del trace; en RAC, gv$session cubre todas las instancias:

select inst_id, sid, serial#, username, event, seconds_in_wait,
       blocking_instance, blocking_session,
       final_blocking_instance, final_blocking_session,
       sql_id, row_wait_obj#
  from gv$session
 where blocking_session is not null
 order by seconds_in_wait desc;

La sesión de final_blocking_session es la del comienzo de la cola. Vea qué ejecutó por última vez y desde cuándo su transacción está abierta:

select s.inst_id, s.sid, s.serial#, s.username, s.module, s.status,
       s.sql_id, s.prev_sql_id, t.start_time
  from gv$session s
  join gv$transaction t
    on t.inst_id = s.inst_id
   and t.addr = s.taddr
 where s.inst_id = &inst_id
   and s.sid = &sid;

Si la espera ya pasó, ASH guarda blocking_session por muestra en v$active_session_history y dba_hist_active_sess_history. ASH exige la licencia del Oracle Diagnostics Pack. Otra salida es activar el trace también en la sesión que retiene el lock, la próxima vez.

Acortar la transacción que retiene las filas

• Haga el commit al final de cada unidad de trabajo, sin dejar la transacción abierta esperando al usuario, a otro sistema o un archivo.
• En un registro que muchas sesiones actualizan (contador, saldo, control de numeración), cambie la fila única por una sequence o por filas separadas por sesión o por rango.
• Cuando la regla lo permita, bloquee antes de modificar con select ... for update wait y un tiempo límite, o skip locked en una cola de trabajo, en lugar de esperar sin límite.

Lock de tabla: indexar la clave foránea

Para enq: TM - contention, liste las claves foráneas del esquema sin un índice que empiece por sus columnas, en el mismo orden. Cambie EXEMPLO por el propietario de las tablas:

select c.owner, c.table_name, c.constraint_name
  from dba_constraints c
 where c.constraint_type = 'R'
   and c.owner = 'EXEMPLO'
   and not exists (
         select 1
           from dba_cons_columns cc
           join dba_ind_columns ic
             on ic.table_owner = cc.owner
            and ic.table_name = cc.table_name
            and ic.column_name = cc.column_name
            and ic.column_position = cc.position
          where cc.owner = c.owner
            and cc.constraint_name = c.constraint_name
          group by ic.index_owner, ic.index_name
         having count(*) = (select count(*)
                              from dba_cons_columns x
                             where x.owner = c.owner
                               and x.constraint_name = c.constraint_name))
 order by c.table_name, c.constraint_name;

Cree el índice en la tabla hija con las columnas de la clave, en el orden de la constraint:

create index exemplo.filha_pai_ix on exemplo.filha (pai_id);

En una tabla grande y en uso, create index ... online (Enterprise Edition) no bloquea el DML durante la creación. Para saber qué tabla se bloqueó, el campo object # de la línea WAIT del enq: TM - contention en el trace es su object_id:

grep "enq: TM - contention" arquivo.trc | head

La documentación de Oracle sobre concurrencia y consistencia de datos explica los locks de DML y el efecto de la clave foránea sin índice.

Misma clave insertada por dos sesiones

En la línea WAIT del enq: TX - row lock contention, el campo name|mode dice el modo del lock. 1415053318 es el modo 6: la fila fue modificada por otra transacción. 1415053316 es el modo 4: en general, dos sesiones insertando la misma clave única o primaria, o un índice bitmap bajo DML concurrente. Para contar las esperas en modo 4:

grep "enq: TX - row lock contention" arquivo.trc | grep -c "name|mode=1415053316"

En el modo 4, revise cómo genera la clave la aplicación y si la misma carga se ejecuta dos veces al mismo tiempo. En una tabla OLTP, cambie el índice bitmap por un índice B-tree.

Otros eventos de la clase

• enq: UL - contention: pida los locks de DBMS_LOCK siempre en el mismo orden y suéltelos pronto, con release_on_commit o DBMS_LOCK.RELEASE.
• SQL*Net break/reset to client: siga a los errores de Oracle en el trace. La espera desaparece cuando el error deja de ocurrir.
• enq: KO - fast object checkpoint: lectura directa de una tabla que cambia todo el tiempo. Revise el plan de la sentencia que esperó.
• enq: RO - fast object reuse: TRUNCATE o DROP repetido, muchas veces dentro de un bucle.

Cómo confirmar

1. Aplique una corrección a la vez: el índice, el commit más temprano o el orden de los locks.
2. Active el trace de la misma operación con esperas (activar el trace con DBMS_MONITOR).
3. Envíe el archivo nuevo y abra el informe.
4. Compare la fila Application en Por clase de espera y las filas de los eventos en el perfil.

Resultado: la clase Application deja de pesar en el tiempo total y el hallazgo desaparece del Diagnóstico. En el perfil, el evento que pesaba baja en Ocurrencias y en Duración (s).

En el informe

El informe de ejemplo no tiene esperas de la clase Application; los enlaces abren las mismas secciones en él.

• Diagnóstico: el hallazgo, con el enlace Ver perfil por clase, que lleva a la fila Application de Por clase de espera.
• Por clase de espera: la fila Application, con los eventos en Componentes principales. Haga clic en el nombre de la clase para abrir la tarjeta con lo que mide y dónde mirar (clases de espera).
• Perfil del tiempo de respuesta: cada evento con Ocurrencias, Media (s) y Máx (s). Pocas esperas largas apuntan a una transacción olvidada abierta; muchas cortas, a una fila disputada (perfil).
• Sentencias principales por componente y por clase: las sentencias que quedaron bloqueadas (sentencias por componente).
• Objetos con más tiempo de espera: el obj# de la fila o de la tabla disputada. En la consola, el número abre la página del objeto, con consultas listas de columnas e índices de la tabla (objetos).
• Distribución de latencia: los rangos de duración de cada evento (latencia).

Si las sesiones llegaron a esperarse unas a otras en ciclo, Oracle devuelve el ORA-00060: vea deadlock entre sesiones.