Desde inicios de los tiempos siempre hemos escuchado de los deadlocks, los abrazos mortales decían los puritanos… y siempre se nos dijo que el problema era del aplicativo y que los DBAs no se involucraban…pues bien, en parte es cierto, pero un administrador de bases debe inmiscuirse en ese tipo de problemas, acá les presento un caso práctico y como se resuelve con Deadlock Graphs
Un deadlocks es simplemente el bloqueo mutuo que se ocasiona entre 2 usuarios al momento de ejecutar sentencias DML, un deadlock implica que ninguno de los usuarios puede avanzar hasta que Oracle simplemente corta la sentencia del primero que ingreso a deadlock y muestra por pantalla el mensaje ORA-00060: deadlock detected while waiting for resource
Una ilustración de este error
Y un sencillo ejemplo práctico de resolución
(1) Detección del error en el alert
Cuando se produce un deadlock en Oracle, se genera un registro en nuestro archivo de alert en nuestra instancia, asociado además con la ubicación de un archivo de trace. eje:
178768:opiodr aborting process unknown ospid (20185466) as a result of ORA-609
183770:ORA-00060: Deadlock detected. More info in file /oracle/app/oracle/diag/rdbms/trade/trade1/trace/trade1_ora_19464964.trc.
(2) Validación del archivo de trace
El archivo de trace contiene muchísima información útil, pero lo que nosotros vamos a buscar es la información de las sesiones involucradas en el deadlock
Las filas y objeto involucrados en el deadlock
Además de la propia sentencia SQL que produce el deadlock
(3) Utilizar Deadlock Graph con el archivo de trace
Como ya se mencionó, nuestro archivo de trace posee un área que se llama Deadlock graph,con lo cual se puede dilucidar el origen del deadlock
Se debe tomar en cuenta los siguientes valores para buscarlos en la tabla de deadlock graph
Si unimos los valores, obtendremos esto
TM SX SSX SX SSX
Este dato es el que debemos en la tabla de deadlock graph para orientarnos en la solución de nuestro deadlock
Para los valores TM SX SSX SX SSX , la tabla nos indica que puede ser un problema de Indices no existentes en una Foreign Key Constraint, lo cual produciría nuestro deadlock
(4) Explicación del código obtenido
El código que se obtiene desde el archivo de trace del deadlock indica de que tipo es el bloqueo que se produce
TM : Está relacionado con el tipo de recurso involucrado, para este caso TM es llamado DML Enqueue y se relaciona siempre a tablas de la base de datos, otros recursos existentes son TX (Transaction Queue) y UL (User Lock, es un bloqueo que se produce mediante el package DBMS_LOCK), dato que se puede encontrar en V$LOCK.TYPE
SX : Row Exclusive Table Lock (lock 3)
SSX : Shared Row Exclusive (lock 5)
El tipo de bloqueo puede ir desde 1 (menos restrictivo, null) hasta 6 (más restrictivo tipo de bloqueo, exclusive)
(5) Validación de las Foreign Key
Recordar que un deadlock se produce cuando dos o más sesiones están esperando por un recurso que se encuentra bloqueado, Oracle detecta estos bloqueos y elimina a la segunda sesión que provoca este boqueo, en donde una sesión no puede terminar su trabajo mientras la otra sesión no lo finalice y viceversa.
Entonces la idea principal es encontrar cuales son los índices faltantes para nuestra Foreing Key, esto se puede llevar a cabo con el siguiente script de Oracle
Script to check for Missing Indexes for Foreign Keys (Doc ID 16428.1)
Con lo anterior simplemente nos queda creamos el índice que el mismo scripts nos indicará y no se producirían más ORA-0060 (deadlock)
Comandos que aparecen de la siguiente forma
Constraint FK_CIUDAD_SITIO(ENABLED) : Changing data in table ESTADIA.PAISES will lock table TRAMITES_COMANDOS.CIUDAD_SITE
Create index for table TRAMITES_COMANDOS.CIUDAD_SITE on columns SITE
Espero les sirva 😉
Documentación
How to Identify ORA-00060 Deadlock Types Using Deadlock Graphs in Trace (Doc ID 1507093.1)
