Deadlock entre sesiones (ORA-00060)

En un deadlock, dos o más sesiones se esperan entre sí en ciclo, y ninguna avanza. Oracle rompe el ciclo devolviendo el ORA-00060 a una de ellas y graba el grafo de espera en el trace de esa sesión. El tipo de lock del grafo señala la causa.

Síntomas

En el Diagnóstico aparece un hallazgo DEADLOCK por cada deadlock del trace. El título es el diagnóstico seguido de las sesiones del ciclo, como Deadlock de fila (TX en modo X) entre las sesiones <a> y <b>. Cuando el trace tiene más de un deadlock y se conoce la hora de cada uno, aparece al final del título, entre paréntesis. La gravedad es siempre alta, sin porcentaje del tiempo total.

El texto explica la causa probable y la corrección y recuerda que Oracle deshizo solo la sentencia de la sesión que recibió el ORA-00060: su transacción sigue abierta hasta el commit o el rollback. Cuando el trace lo informa, trae el sql_id de la sentencia que recibió el error. Termina con Ver deadlock.

El hallazgo sale del bloque DEADLOCK DETECTED, en dos tipos de archivo:

• el trace solo de deadlock, que Oracle graba para la sesión que recibió el error (cómo obtener el trace de deadlock). El informe no tiene perfil, y el Resumen muestra Deadlocks (ORA-00060), Detectado el, Sesiones involucradas y Diagnóstico;
• el trace 10046 de la sesión que recibió el error. El informe es el completo, y en general trae también el hallazgo de errores de Oracle en el trace, por la línea ERROR del ORA-00060, y la espera enq: TX - row lock contention o enq: TM - contention justo antes del deadlock.

El Diagnóstico muestra un hallazgo por cada uno de los primeros deadlocks del trace, y la sección Deadlocks trae su detalle.

Causa

El diagnóstico sale del tipo de lock en el grafo de espera y del modo solicitado:

La página del ORA-00060 en la documentación de Oracle resume el error.

Solución

Leer el deadlock en el informe

1. Haga clic en Ver deadlock.

   Resultado: se abre la tarjeta Deadlock n, con el diagnóstico, la hora y la línea del trace en la que empieza el bloque.

2. Siga el ciclo: cada sesión espera a la siguiente, y la última vuelve a la primera.
3. En el Grafo de espera, revise para cada recurso el Tipo, la Sesión en espera con el Modo solicitado y la Sesión que retiene con el Modo retenido.
4. En Sesiones involucradas, revise Usuario, Máquina, Programa, Módulo, Acción y el SQL de cada sesión. La marcada con recibió el ORA-00060 es la dueña del trace.
5. En Fila esperada, revise el object_id, el archivo, el bloque, el slot y el rowid de la fila que esperaba cada sesión. Sin Fila esperada, la espera no era por una fila: es el caso de TM, UL, ITL y clave única.
6. En Pila PL/SQL de la sesión que recibió el error, encuentre la unidad PL/SQL y la línea del código que ejecutó la sentencia.

Con el propietario y el nombre de la tabla del object_id, el rowid muestra la fila disputada, si todavía existe:

select owner, object_name, object_type
  from dba_objects
 where object_id = &object_id;

select *
  from exemplo.pedido
 where rowid = '&rowid';

Deadlock de fila (TX en modo X)

• Modifique las filas siempre en el mismo orden en todas las rutinas, por la clave, por ejemplo.
• Bloquee las filas antes de modificarlas, con select ... for update, en el mismo orden.
• Acorte la transacción: menos filas retenidas por menos tiempo dejan menos posibilidad de ciclo.

Transacción con espera en modo S

• Clave única: compruebe si dos rutinas insertan las mismas claves en distinto orden, o si la misma carga se ejecuta dos veces al mismo tiempo.
• ITL: aumente el INITRANS de la tabla o del índice del grafo, como en espacio de transacción en el bloque.
• Índice bitmap en una tabla con DML concurrente: cámbielo por un índice B-tree.

Lock de tabla (TM)

Cree un índice en las columnas de la clave foránea de la tabla hija. El object_id aparece en el texto del hallazgo y en la columna Tipo del grafo; en la consola, abre la página del objeto, con el bloque Tabla: índices y las columnas de cada uno. Para encontrar todas las claves foráneas sin índice del esquema, use la consulta de indexar la clave foránea.

Lock de usuario (UL)

Solicite los locks de DBMS_LOCK siempre en el mismo orden en todas las rutinas y libérelos pronto, con release_on_commit o DBMS_LOCK.RELEASE.

Tratar el ORA-00060 en la aplicación

Oracle deshizo solo la sentencia que recibió el error. La transacción sigue abierta, con las filas que ya había bloqueado: la aplicación tiene que hacer su rollback y, si la operación se puede repetir, intentarlo de nuevo. Sin eso, la sesión sigue reteniendo las filas y bloquea a las demás.

Cómo confirmar

El deadlock es intermitente: un trace sin él no prueba la corrección. Observe, en el período en que solía ocurrir, si el ORA-00060 vuelve. Cada ocurrencia graba un trace nuevo de la sesión que recibió el error y una línea en el alert log. En 19c, la vista v$diag_alert_ext lee el alert log desde la base de datos:

select originating_timestamp, message_text
  from v$diag_alert_ext
 where message_text like '%ORA-00060%'
 order by originating_timestamp desc;

Si el error vuelve, envíe el trace nuevo y compare el diagnóstico, las sesiones y las sentencias con los del deadlock anterior.

En el informe

El informe de ejemplo no tiene deadlock, y la sección Deadlocks solo aparece cuando el trace tiene alguno.

• Deadlocks: una tarjeta por deadlock, con el diagnóstico, el ciclo, el Grafo de espera, las Sesiones involucradas y la pila PL/SQL (deadlocks).
• Diagnóstico: un hallazgo por deadlock, con el enlace Ver deadlock (Diagnóstico).
• Resumen: en el trace solo de deadlock, los cuadros Deadlocks (ORA-00060), Detectado el, Sesiones involucradas y Diagnóstico (resumen).
• Errores: en el trace 10046, la tarjeta del ORA-00060 tiene el enlace Ver el deadlock (errores).
• Sentencias SQL por contribución: en el trace 10046, la sentencia que recibió el error, por la columna SQL ID (sentencias SQL).

Antes del deadlock, las sesiones suelen esperarse entre sí sin cerrar el ciclo; ese tiempo aparece en espera por bloqueo de otra sesión.