¿Qué es idle in transaction?
«idle in transaction» se puede traducir como «ociosa/inactiva en la transacción».
Es uno de los estados en que puede encontrarse un backend.
Otros estados posibles son:
active: El backend está ejecutando una query.
idle: El backend está esperando un nuevo comando del cliente.
idle in transaction (aborted): Este estado es similar a idle in transaction excepto que una de las sentencias de la transacción provocó un error.
fastpath function call: El backend está ejecutando una función fast-path, una función simple que se envía al servidor a través de una interfaz de ruta rápida.
disabled: Es el estado reportado cuando track_activities (un parámetro de configuración del servidor) está deshabilitado en este backend.
Un backend es el proceso que se encarga de ejecutar una sentencia SQL y comunicarse con el frontend (el proceso que se crea cuando el cliente consigue conexión con la base de datos).
Entonces, idle in transaction significa, en otras palabras, que el backend ejecutó la sentencia SQL y se ha quedado inconclusa.
¿Cómo se detectan las transacciones idle in transaction?
pg_stat_activity es una vista del recolector de estadísticas del servidor PostgreSQL que muestra una fila por proceso en el servidor, e información relacionada con la actividad actual de ese proceso, como su estado (de entre los vistos en el punto anterior) y la sentencia SQL que ejecuta.
Así que, podríamos lanzar una consulta sobre la vista tan sencilla como esta:
from pg_stat_activity
where state like ‘%idle in transaction%’;
Si quieres monitorizar automáticamente esta consulta, y que te avise cuando encuentre casos para tomar acción, te recomiendo usar Nagios.
Otra herramienta útil es pg_activity porque, además de mostrar la información de la vista pg_stat_activity en tiempo real (con un refresco de entre 1 y 3 segundos) permite finalizar estos backend como veremos en el último punto de este artículo.
Si no conoces esta herramienta y quieres saber todo acerca de ella échale un vistazo al curso Monitoriza la actividad de PostgreSQL con pg_activity.
¿Por qué ocurren?
PostgreSQL lo ejecuta todo dentro de una transacción.
Si ejecutamos código sin crear la transacción, PostgreSQL la iniciará por tí pero al no finalizarse la transacción en el código, ésta quedará «idle in transaction» (consulta finalizada, backend ocioso a la espera del fin de la transacción).
Esto es habitual cuando los clientes ejecutan aplicaciones desarrolladas en Java, con el framework Hibernate, pues pueden desencadenar transacciones ‘idle in transaction’ que no se liberarán hasta que el cliente que las provocó sea reiniciado, o hasta que el DBA cancele o termine el backend.
Un caso particular es «idle in transaction (aborted)» que ocurre cuando la sentencia no se pudo ejecutar por un error (como una inserción que viola una restricción de integridad) y el código no tiene implementado la gestión de errores correspondiente.
En este caso podemos ir al log, o a la herramienta que utilices para analizar el log de PostgreSQL, para obtener información acerca de ese error.
Si quieres saber todo sobre el log de PostgreSQL aquí tienes información sobre el curso Administración PostgreSQL: El Log del servidor y si quieres conocer la herramienta más potente hoy día para analizar el log aquí tienes un curso muy completo sobre pgBadger, Administración PostgreSQL: pgBadger, el analizador del log.
¿Cómo solucionarlo?
Como todo en informática, y en la vida, lo primero es prevenir. Y si aún así ocurre, entonces solucionar.
Actuaciones preventivas para evitarlos.
Prevenirlos pasa porque todas las sentencias SQL que generemos en nuestros códigos, incluidos los SELECT, estén encapsulados en transacciones con sus respectivos bloques try, catch y finally, para garantizar el manejo de excepciones y el cierre de la transacción.
Actuaciones correctivas para eliminarlos.
Una vez que aparecen los idle in transaction sólo nos queda obtener información sobre el backend que lo ha causado (cliente, aplicación, usuario, base de datos y query) y, si se trata de un idle in transaction (aborted) podemos además averiguar el mensaje de error que se ha producido y facilitarlo al equipo de desarrollo que corresponda para que aplique las actuaciones preventivas comentadas en el apartado anterior.
Una vez hecho esto no nos queda más remedio que cancelar o terminar el backend, utilizando las funciones que proporciona PostgreSQL para ello o, mucho más fácil, con la herramienta pg_activity.
Las funciones pg_cancel_backend (pid) y pg_terminate_backend (pid) se pueden ejecutar desde el intérprete de comandos de PostgreSQL, psql.
Antes debemos averiguar el PID del backend y esto lo obtenemos del campo con el mismo nombre en la vista pg_stat_activity.
Con la herramienta pg_activity es más rápido y sencillo.
Sólo tenemos que colocar el cursor encima del backend que queremos finalizar y pulsar la tecla ‘c’ para cancelar o ‘k’ para terminar.
La diferencia entre cancelar o terminar un backend es que cancelar emite una señal SIGINT que cancela la query actual mientras que terminar emite una señal SIGTERM que desconecta el backend de la base de datos.