Why Long Database Transactions Crash Services and How to Prevent Them

Why Use Long Transactions?

In complex backend systems, transactions ensure data consistency and allow rollback on errors. However, when a transaction runs for a long time, it holds the DB connection until it finishes, leading to “long transactions”. If many requests arrive, the connection pool can become exhausted, causing service failures.

Strange Symptoms

When investigating, you may see most threads blocked in Tomcat’s thread pool, even on fast endpoints like /status . The jstack output can be misleading; the real blockage often comes from a few threads holding DB connections.

How to Improve

Keep transactions short: avoid slow queries, do not include RPC or IO calls, exclude low‑priority services like message queues, and avoid mixing different resources (e.g., Redis) in the same transaction.

Resolution Strategies

Beyond scaling, the effective approach is prevention. Review code to move non‑DB operations out of @Transactional methods, add timeout alerts, and strengthen monitoring. Use connection‑pool metrics and jstack to detect blocked threads, and analyze slow SQL via tools like Druid.

Monitoring screenshots show connection pool saturation and rising waiting threads, indicating long‑transaction problems.

Conclusion

Long transactions are high‑risk and can cause severe outages. The best fix is redesigning the business model, but practical mitigation includes code review, timeout alerts, and comprehensive monitoring.