Troubleshoot: enq: TX – row lock contention

Toparnos con solicitudes de atención para resolver transacciones bloqueadas no es algo inusual.

Por lo común nos basta con identificar la sesión causante del bloqueo, la cancelamos (kill session) y con ello queda resuelto, pero en esta ocasión el problema que experimentaba uno de mis Clientes era masivo y recurrente, y por tanto requería de mayor análisis.

El problema

La aplicación con problemas cuenta con múltiples tareas, y todas ellas tienen como punto en común la necesidad de actualizar una fila que es exclusiva para cada tarea.

Si logramos identificar la fila que está siendo bloqueada masivamente, el Cliente podrá determinar de qué tarea se trata, y con ello iniciar el proceso de corrección del código, que viene a ser la solución final.

La solución

Si lo que deseamos fuese simplemente salir del bloqueo, un query como el siguiente nos ayuda a identificar la sesión causante y proceder a cancelarla.

SELECT inst_id, sid, 
       blocking_instance, blocking_session
SELECT gv$session
 WHERE event = 'enq: TX - row lock contention';

   INST_ID        SID BLOCKING_INSTANCE BLOCKING_SESSION
---------- ---------- ----------------- ----------------
         2         57                 1              285

Pero como deseamos identificar la fila que está siendo bloqueada, debemos recurrir al package DBMS_ROWID, y a unas columnas adicionales que afortunadamente están presentes en v$session.

El query a usar es el siguiente:

SELECT do.owner,
       do.object_name,
       dbms_rowid.rowid_create ( rowid_type=> 1,
                                 object_number=> do.data_object_id,
                                 relative_fno=> v.rfile#,
                                 block_number=> s.row_wait_block#,
                                 row_number=> s.row_wait_row# )
  FROM gv$session s,
       dba_objects do,
       v$datafile v
 WHERE s.event = 'enq: TX - row lock contention'
   AND s.row_wait_obj# = do.object_id
   AND s.row_wait_file# = v.file#;

OWNER	   OBJECT_NAME	   DBMS_ROWID.ROWID_C
---------- --------------- ------------------
SCOTT	   EMP             AAASYGAAOAAAACFAAA

Lo que hemos conseguido son los datos de la tabla (dueño y nombre) y el ROWID de la fila bloqueada.

Con ellos podemos finalmente obtener los datos requeridos:

SELECT * 
  FROM scott.emp 
 WHERE rowid = 'AAASYGAAOAAAACFAAA';
 
  EMPNO ENAME     SAL
 ------ ------ ------
   7782 CLARK    2450

¿Y si el bloqueo ocurrió en el pasado?

En este caso tendremos que apoyarnos en dba_hist_active_sess_history en reemplazo de v$session, y desde luego necesitaremos conocer el intervalo de tiempo en el que se presentó el bloqueo masivo.

El query a usar será el siguiente:

SELECT DISTINCT do.owner, do.object_name,
       dbms_rowid.rowid_create ( rowid_type=> 1,
                                 object_number=> do.data_object_id,
                                 relative_fno=> v.rfile#,
                                 block_number=> s.current_block#,
                                 row_number=> s.current_row# )
  FROM dba_hist_active_sess_history s,
       dba_objects do,
       v$datafile v
 WHERE sample_time >= TO_DATE('18/11/2024 10:01','dd/mm/yyyy hh24:mi')
   and sample_time <= TO_DATE('18/11/2024 10:02','dd/mm/yyyy hh24:mi')
   and event = 'enq: TX - row lock contention'
   AND s.current_obj# = do.object_id
   AND s.current_file# = v.file#;

OWNER	   OBJECT_NAME	   DBMS_ROWID.ROWID_C
---------- --------------- ------------------
SCOTT	   EMP             AAASYGAAOAAAACFAAA

Nuevamente hemos obtenido los datos de la tabla (dueño y nombre) y el ROWID de la fila bloqueada.

Con ellos podemos obtener los datos requeridos:

SELECT * 
  FROM scott.emp 
 WHERE rowid = 'AAASYGAAOAAAACFAAA';
 
  EMPNO ENAME     SAL
 ------ ------ ------
   7782 CLARK    2450

Listo, caso cerrado, ¿no estuvo tan complicado, verdad?

Si deseas complementar lo aquí expuesto, te recomiendo la lectura de las notas:

1524216.1How to Determine which Row is Locked by a Blocking Session
198150.1How To Indentify The Row Which is Locked By an Other User's Session

Posts Recientes

Aprenda a resolver el error CRS-2304 GPnP profile signature verification failed al iniciar una base de datos 11.2 en un cluster 19c.
Aprenda a corregir los permisos dañados de un Oracle Home, ya sea de Oracle Grid o de Oracle Database Server
Aprenda a mejorar la seguridad de sus aplicaciones usando Oracle Wallet / Secure External Password Store.
Que hacer cuando nos encontramos que la base de datos nos devuelve la hora adelantada o atrasada sin razón aparente.
Aprenda cómo cambiar la contraseña de un usuario Oracle, sin interrumpir el servicio de las aplicaciones.
Tercer post de una serie dedicada al arte de parchar Oracle. Continuamos con el parchado out-of-place, simplificado usando Gold Image.

¿Necesitas Ayuda?

Completa estos datos y estaré en contacto a la brevedad.