Hola a todos. 👋
Hoy vamos a hablar de un tema que, aunque no siempre recibe la atención que merece, puede causar más de un dolor de cabeza en nuestras bases de datos: los interbloqueos.
Seguramente alguna vez te has encontrado con sesiones que parecen no avanzar o con errores misteriosos relacionados con bloqueos. Pues bien, detrás de muchos de esos casos suele esconderse un interbloqueo. En esta entrada veremos qué son, por qué ocurren y, sobre todo, cómo identificar el código que los provoca.
¿Qué es un interbloqueo?
Un bloqueo ocurre cuando dos o más sesiones están esperando que los datos sean bloqueados entre sí, lo que provoca que todas las sesiones sean bloqueadas. Oracle detecta y resuelve automáticamente los bloqueos revertiendo la instrucción asociada a la transacción que detecta el bloqueo. Normalmente, los bloqueos se deben a un código de aplicación de bloqueo mal implementado.
Vamos a ver los pasos necesarios para identificar el código de aplicación problemático cuando se detecta un bloqueo.
Pasos al detectar un interbloqueo
Crea un usuario de prueba
Crea un esquema de prueba
Reproducir el interbloqueo
Inicia dos sesiones SQL*Plus, cada una iniciada en el usuario de prueba, y luego ejecuta los siguientes fragmentos de código, uno en cada sesión.
Sesión 1
Sesión 2
El primer fragmento de código se bloquea en una fila de la tabla, se pausa durante 30 segundos y luego intenta bloquear una fila de la tabla. El segundo fragmento de código hace lo mismo pero al revés, bloqueando una fila en la tabla y luego la tabla.
La llamada al procedimiento solo está presente para darte tiempo suficiente para cambiar de sesión. DEADLOCK_1DEADLOCK_2DEADLOCK_2DEADLOCK_1DBMS_LOCK.SLEEP
Finalmente, una de las sesiones detectará el bloqueo, revertirá su transacción y producirá un error de bloqueo, mientras que la otra transacción se completa con éxito. A continuación se muestra un error típico de bloqueo.
Además del error de bloqueo reportado a la sesión, se coloca un mensaje en el registro de alertas.
El mensaje de error contiene una referencia a un archivo trace, cuyo contenido indica las sentencias SQL bloqueadas tanto en la sesión que detectó el bloqueo como en las otras sesiones bloqueadas.
Las secciones en negrita son las que más interesan.
La primera sección muestra la sentencia SQL bloqueada en la sesión que detectó el bloqueo.
La segunda sección es un mensaje de Oracle que te dice que esto es un problema de aplicación, no un error de Oracle.
La tercera sección enumera las sentencias SQL bloqueadas en las otras sesiones de espera.
Las sentencias SQL que aparecen en el archivo trace deberían permitirte identificar el código de la aplicación que está causando el problema.
Para resolver el problema, asegúrate de que las filas de las tablas siempre estén bloqueadas en el mismo orden. Por ejemplo, en el caso de una relación maestro-detalle, podrías decidir bloquear siempre una fila en la tabla maestra antes de bloquear una fila en la tabla de detalles.
En resumen, los pasos necesarios para identificar y corregir el código que causa bloqueos son:
- Localiza los mensajes de error en el registro de alertas.
- Localiza el(los) archivo(s) de trazado relevante(s).
- Identifica las sentencias SQL tanto en la sesión actual como en la(s) sesión(es) en espera.
- Utiliza estas sentencias SQL para identificar el fragmento de código que está teniendo problemas.
- Modifica el código de la aplicación para evitar bloqueos bloqueando siempre las filas en las tablas en el mismo orden.
Conclusión
Los interbloqueos pueden parecer complicados al principio, pero con las herramientas y el enfoque correcto se pueden identificar y resolver fácilmente.
Con los pasos que hemos visto hoy podrás localizar el fragmento de código problemático y corregirlo, evitando que vuelva a suceder.
Si aún así tienes problemas y quieres que le echemos un vistazo, no dudes en contactar con nosotros.
¿Aún no conoces Query Performance? Descubre cómo puede ayudarte en tu entorno Oracle. Más información en su página de LinkedIn.
Sígue a GPS en LinkedIn