ORA-00060 Deadlock detected

    ¿Qué es un deadlock?

Un deadlock o también conocido como abrazo mortal, es una situación que se produce cuando una transacción tiene tomado un recurso, llamémoslo A y requiere un recurso que llamamos B, y a su vez ese recurso B está tomado por otra transacción que requiere el recurso A.

 

La única forma de resolver este problema es que exista algún mecanismo externo que detecte el conflicto y le informe a la transacción que está en esta situación de deadlock, logrando de esta forma que la transacción abandone el intento de obtener ese recurso. Oracle detecta automáticamente los deadlocks realizando un rollback de una de las transacciones, que es la que detectó la situación de deadlock.

En general los deadlocks ocurren por un problema de diseño de la aplicación, por eso es importante poder brindar a las áreas de desarrollo correspondientes, la información necesaria como para que puedan investigar y resolver el problema.

Cuando sucede un deadlock la base de datos registra un error ORA-00060 en el archivo de alertas y se genera un archivo de trace.

A continuación presento un script que utilizo para detectar y enviar por mail los errores de tipo ORA- al cual le incorporé una sección que para el caso de ocurrencia de deadlock, extrae del archivo de trace información útil para que los desarrolladores, puedan resolver el problema.

   Script

 

#

# Funcion: informa sobre errores en el alert de la base de datos a

#          dbas, y a desarrollo envia informacion de deadlocks

# Uso: chk_alert.sh <INSTANCIA> <DIR_ALERT/ALERT>

#

 

DIR=/home/oracle/scripts

 

hora=`date +%H`

if [ $hora -lt 20 ] && [ $hora -gt 09 ]

then

    # Administradores de dia

    USERS="dbdia@mail"

    USERS_DL=desa@mail,dbdia@mail

 

else

    # Administradores de noche

    USERS=dbnoche@mail

    USERS_DL=desa@mail,dbnoche@mail

fi

 

if [ $# -ne 2 ]

then

  echo

  echo " Parametro invalido "

  echo

  echo " Se debe ejecutar chk_alert.sh instancia archivo_alert "

  echo

  exit

else

  file=$2

  instancia=$1

fi

 

#

# Ejecuto el profile de Oracle

#

. /home/oracle/.bash_profile   1>/dev/null 2>/dev/null
export ORACLE_SID=$instancia

 

if ( [ -a $file ] && [ -a $file.chk ] )

then

   ant=`wc -l $file.chk | awk {'print $1'}`

   aho=`wc -l $file | awk {'print $1'}`

  

   if [ $aho -gt $ant ]

   then

      lineas=`expr $aho - $ant `

      tail -$lineas $file | grep ORA- >$DIR/chk_alert.mail

  

      if  [ -s $DIR/chk_alert.mail ]

      then

         mailx -s "Produccion PRD: ALERT ORACLE : $instancia " $USERS <$DIR/chk_alert.mail

  

         if [ `grep ORA-00060 chk_alert.mail | wc -l ` -gt 0 ]

         then

         #                                          

         # Envia mail a desarrollo pues hay deadlock

         #                                          

                nombre_trace="`cat $DIR/chk_alert.mail|grep "ORA-00060"|cut -f8 -d" "|cut -f1 -d.`".trc

 

                n=`cat $nombre_trace|grep -n "Deadlock graph"|tail -1|cut -f1 -d:`

                n=`expr $n + 35`

 

                head -$n $nombre_trace|tail -36>$DIR/deadlock.txt

 

                obj1=`grep "dictionary objn" deadlock.txt|head -1|cut -f2 -d"-"|cut -f1 -d","`

                obj2=`grep "dictionary objn" deadlock.txt|tail -1|cut -f2 -d"-"|cut -f1 -d","`

 

                echo "Objetos Tomados:" >$DIR/chk_deadlock.mail

                echo "================" >>$DIR/chk_deadlock.mail

                sqlplus -S '/ as sysdba' << eof

col object_name format a30

col object_type format a12

spool $DIR/chk_deadlock.lst

select object_name,object_id,object_type from dba_objects where object_id in '$obj1','$obj2');

exit

eof

                cat $DIR/chk_deadlock.lst >>$DIR/chk_deadlock.mail

                echo >>$DIR/chk_deadlock.mail

                echo "Informacion del Deadlock:" >>$DIR/chk_deadlock.mail

                echo "=========================" >>$DIR/chk_deadlock.mail

                cat $DIR/deadlock.txt >>$DIR/chk_deadlock.mail

 

                echo >>$DIR/chk_deadlock.mail

                echo "Archivo Trace:" >>$DIR/chk_deadlock.mail

                echo "==============" >>$DIR/chk_deadlock.mail

                echo $nombre_trace >>$DIR/chk_deadlock.mail

 

                mailx -s "Deadlock en base: $instancia "  $USERS_DL <$DIR/chk_deadlock.mail

 

                cp /dev/null $DIR/deadlock.txt

                cp /dev/null $DIR/chk_deadlock.lst

                cp /dev/null $DIR/chk_deadlock.mail

         fi

         cp /dev/null $DIR/chk_alert.mail

      fi

   fi

fi

cp $file $file.chk

 

Un ejemplo de mail de deadlock detectado por el script de control es el siguiente:

Objetos Tomados:
================

OBJECT_NAME                    OBJECT_ID OBJECT_TYPE
---------------------------- ---------- ------------
SALDOS                         107326      TABLE
MOVIMIENTOS                    107329      TABLE


Informacion del Deadlock:
=========================
Deadlock graph:
---------Blocker(s)-------- ---------Waiter(s)---------
Resource Name process session holds waits process session holds waits
TX-000a0025-00045474 334 171 X 362 823 X
TX-002f0008-0001fbfe 362 823 X 334 171 X
session 171: DID 0001-014E-00003A3F session 823: DID 0001-016A-00004BCF
session 823: DID 0001-016A-00004BCF session 171: DID 0001-014E-00003A3F
Rows waited on:
Session 823: obj - rowid = 0001A341 - AAAaNBAACAABCFDAA9
(dictionary objn - 107329, file - 2, block - 270659, slot - 61)
Session 171: obj - rowid = 0001A33E - AAAaM+AAPAAAGekAAe
(dictionary objn - 107326, file - 15, block - 26532, slot - 30)
Information on the OTHER waiting sessions:
Session 823:
pid=362 serial=33398 audsid=45199005 user: 212/SLU
O/S info: user: aramirez, term: MHWAP006, ospid: 11204:4228, machine: DOMINIO\MHWAP006
program:
Current SQL Statement:
SELECT MAX(AA_EJER) FROM PRE_BEJERCICIO WHERE F_CARGA_ACTO IS NOT NULL AND F_CARGA_ACTO <> NVL(F_PROYECTO,F_CARGA_ACTO+1) AND F_CIERRE IS NULL
End of information on OTHER waiting sessions.
Current SQL statement for this session:
UPDATE CB_SALDOS SET I_SALDO_COMP = I_SALDO_COMP + DECODE(:B4 ,'S',:B2 ,0), I_SALDO_DEV = I_SALDO_DEV + DECODE(:B3 ,'S',:B2 ,0), I_SALDO_PAG = I_SALDO_PAG + DECODE(:B1 ,'S',:B2 ,0) WHERE C_CTAESC = :B6 AND AA_CTAESC = :B5
----- PL/SQL Call Stack -----
object line object
handle number name
0x495892ac0 52 SSS.TG_MOVES_BRIUD
===================================================
PROCESS STATE
-------------
Process global information:
process: 0x4fe689cb8, call: 0x498b0a058, xact: 0x4f623ade0, curses: 0x4fb7b46e8, usrses: 0x4fb7b46e8
----------------------------------------
SO: 0x4fe689cb8, type: 2, owner: (nil), flag: INIT/-/-/0x00
(process) Oracle pid=334, calls cur/top: 0x498b0a058/0x498b57880, flag: (0) -
int error: 0, call error: 0, sess error: 0, txn error 0
(post info) last post received: 0 0 8

Archivo Trace:
==============
/u01/oracle/admin/PRD/udump/prod_ora_1113.trc