AI-Generated SQL Locks Database, Exhausts Tomcat Connection Pool: Production Post-Mortem

1. Bizarre Failure Phenomenon: Restart Works, Then Service Freezes Again

The backend runs two parallel services: a .NET service and a Tomcat Java service, sharing a single SQL Server database. The same query logic runs stably on .NET, but the Tomcat side slowly gets "suffocated". Symptoms:

Front-end requests time out; APIs stop responding.

Port 8080 still listens, Tomcat process is alive, no crash.

Restarting Tomcat restores everything; after tens of minutes to hours the issue recurs.

The team ruled out network jitter, memory leaks, and thread-pool misconfiguration. The trail finally led to database connections.

2. Layer-by-Layer Localization: Who Consumed All Database Connections?

Tomcat's JNDI pool was configured with maxActive="300". During the outage all 300 connections were active with zero idle. New requests queued for a connection, exhausting Tomcat worker threads and freezing the service.

Querying SQL Server sessions revealed long-blocked sessions: a single SELECT statement stuck on a lock, never completing, never releasing its connection. That SQL had been written with AI assistance. It worked functionally and ran fast in the test environment with small data, but AI did not evaluate lock granularity, transaction scope, or blocking risk under concurrency.

Root-Cause Chain

SQL starts a transaction and acquires database locks.

It waits on a data lock and blocks indefinitely.

Tomcat's old pool had no SQL execution timeout , so the connection stayed borrowed forever and was never returned.

As traffic continued, available connections dwindled to zero.

New API requests could not obtain a connection, all blocked, and Tomcat "hung".

Why .NET survived: .NET code set a strict CommandTimeout ; when a query stalled it aborted automatically and released the connection. Tomcat only had pool-level settings, lacking statement-level execution timeout, so a blocked query leaked the connection permanently.

3. Post-Mortem: Three Engineering Pits AI Misses When Writing SQL

✅ Implements the query, ❌ ignores locks, lock escalation, blocking, deadlock risks.

✅ Single-statement test passes, ❌ does not add SQL execution timeout; a stuck query holds the connection forever.

✅ Works in low-data test env, ❌ under concurrency and large data volumes latency triggers a delayed failure — a time bomb.

These are not syntax errors; unit tests and simple functional tests cannot catch them. They surface only under real production traffic.

4. Root-Cause Fix: Dual-Layer Optimization (Connection Pool + SQL)

1. SQL-Level Optimization

Reduce transaction scope; keep queries in short transactions. Optimize indexes to cut lock hold time and avoid large scans that cause lock contention.

2. Tomcat JNDI Pool: Add Timeout Protection (Critical)

Enable statement timeout and idle-connection validation so leaked connections are reclaimed automatically.

<Resource name="jdbc/jws" auth="Container" type="javax.sql.DataSource"
         maxActive="300" maxIdle="30" maxWait="10000"
         removeAbandoned="true" removeAbandonedTimeout="60" logAbandoned="true"
/>

removeAbandoned

: enable leaked-connection reclamation. removeAbandonedTimeout: after 60 seconds unreturned, treat as leaked and reclaim.

Additionally, add SQL execution timeout in application code to mirror .NET's protection.

3. Monitoring Hardening (Early Warning, Not Post-Crash)

Add two alert categories:

Database: long-blocking sessions, lock-wait alerts.

Tomcat: connection-pool water-level monitoring; alert when usage exceeds 80%. Catch the symptom before the service fully paralyzes.

5. Closing Thoughts

AI accelerates business code, SQL, and API docs, boosting development speed. But AI cannot see live concurrency contention, cannot see database lock waits, and does not understand the cascade collapse after resource exhaustion.

AI writes functionality; humans must assess risk, set safety nets, and validate concurrency stability. Blindly trusting AI output and deploying directly easily buries delayed production failures.

All of the above comes from real online operations experience — no empty theory.