Deadlock silencioso en PostgreSQL: ¿culpa de SELECT FOR UPDATE o del pool de conexiones?

Puzzles

Llevo dos días peleando con un deadlock intermitente en producción. Tenemos un servicio en Java con Spring Boot, usando HikariCP y PostgreSQL 14. El flujo es simple: una transacción lee una fila con SELECT ... FOR UPDATE y luego actualiza otra tabla basada en el resultado. El deadlock aparece solo bajo carga alta, y no en todos los intentos. He revisado los logs de PostgreSQL y veo dos sesiones esperando entre sí, pero las consultas involucradas son exactamente las mismas en ambos hilos. Lo extraño es que el orden de los LOCK parece idéntico. ¿Alguien ha visto algo así? Sospecho del pool de conexiones, pero no sé cómo confirmarlo.

Beer

El clásico deadlock por orden de bloqueo no siempre es evidente. Aunque las consultas sean las mismas, el plan de ejecución puede variar según los índices y las estadísticas. Te recomiendo ejecutar EXPLAIN (ANALYZE, BUFFERS) en ambas consultas bajo carga. También verifica si hay índices parciales o foreign keys que tomen locks adicionales. Y sobre HikariCP: si usas setTransactionIsolation o setReadOnly mal configurados, puede haber locks implícitos. ¿Puedes compartir el esquema de las tablas implicadas?

Polyglot

Has probado a usar SELECT ... FOR UPDATE SKIP LOCKED? No es una solución general, pero a veces rompe el deadlock si el flujo permite reintentar. Otra opción es ordenar las operaciones por una clave única (por ejemplo, siempre actualizar la fila con menor ID primero). Si no puedes cambiar el orden, revisa si el pool de conexiones está reutilizando conexiones con autocommit=false y no se está haciendo rollback correctamente. Un savepoint perdido puede dejar locks sin liberar.

Puzzles

Gracias por las respuestas. Sobre el plan de ejecución, he capturado el deadlock en los logs y las dos sesiones tienen exactamente el mismo plan (seq scan en una tabla pequeña). No hay índices extraños. Lo de SKIP LOCKED no es viable porque necesito los datos para la lógica posterior. Pero lo de ordenar por ID me ha dado una idea: actualmente el código obtiene la fila y luego actualiza según una condición, pero no garantiza el orden de las filas entre transacciones. Voy a probar a forzar un ORDER BY id en el SELECT FOR UPDATE, aunque la tabla no tenga índice por id... ¿Alguien ha visto que PostgreSQL adquiera locks en el orden del plan y no en el de la cláusula ORDER BY?

OpenSource

Ese es un punto clave: PostgreSQL no garantiza el orden de los locks si el plan usa un seq scan, incluso con ORDER BY. De hecho, el FOR UPDATE se aplica a medida que se leen las filas, y el seq scan puede leer en orden físico del heap, que no coincide con el orden lógico. Si añades un índice en el id y haces que el plan use un Index Scan con ORDER BY, entonces los locks se adquirirán en orden de id. Eso debería eliminar el deadlock si todas las transacciones usan el mismo orden. Pruébalo y nos cuentas.

Politics

Ojo, que forzar un índice puede empeorar el rendimiento si la tabla es pequeña. Otra alternativa es usar advisory locks a nivel de aplicación para serializar el acceso a un conjunto de filas. Pero si el deadlock es raro, igual te interesa simplemente detectarlo y reintentar la transacción. En PostgreSQL, puedes configurar deadlock_timeout (por defecto 1s) y hacer que el driver reintente automáticamente. No es elegante, pero es práctico.

Polyglot

También revisa si estás usando FOR UPDATE en una vista o en una subconsulta. A veces el plan materializa y los locks se toman en un orden inesperado. Y otra cosa: en HikariCP, verifica que maximumPoolSize no sea demasiado alto para el número de conexiones de PostgreSQL. Si tienes más conexiones que el max_connections de la BD, puede haber esperas adicionales. Pero el deadlock en sí no es por pool, es por orden de locks.

Puzzles

Actualizo: he forzado el índice en id y el plan ahora usa Index Scan. He realizado una prueba de carga con 100 hilos concurrentes y los deadlocks han desaparecido. Supongo que el seq scan leía las filas en el orden del heap, que variaba entre sesiones. Añadiré el índice en producción y también un reintento por si acaso. Gracias a todos, especialmente a quien mencionó el orden físico vs lógico. Era justo eso.

HipHop

El orden físico del heap es un factor que mucha gente pasa por alto. El seq scan itera en orden físico, que puede variar entre sesiones si hay actualizaciones o eliminaciones recientes (HOT updates, dead tuples, etc.). Al forzar el índice, el orden lógico es estable, lo que reduce la probabilidad de deadlock por adquisición cíclica de locks. Pero cuidado: si la tabla crece y el índice se vuelve menos selectivo, el planner podría volver al seq scan. Monitorea el plan en producción.

Otra opción que no mencionaron es usar SELECT ... FOR UPDATE SKIP LOCKED si tu lógica de negocio lo permite. Evita el bloqueo por completo, pero cambia la semántica (te saltas filas que están bloqueadas). Si el deadlock es raro, el reintento es aceptable, pero asegúrate de que la transacción sea idempotente.

También revisa si estás usando REPEATABLE READ o SERIALIZABLE. Con SERIALIZABLE, PostgreSQL puede lanzar errores de serialización (40001) que son más fáciles de manejar que un deadlock, porque son detectables de inmediato. Puedes configurar el driver para reintentar en ese caso.