# Real-Time SQL Debugging: Why Your Query is Stuck and How to Kill It *SQLMarrow Case Studies · Advanced · 8 min read* --- It starts with an alert: **Database CPU has spiked to 99%**. Moments later, the application logs flood with `504 Gateway Timeout` and connection pool exhaustion errors. The site is grinding to a halt. When you check your active requests, they are all queued up, waiting on the database. Somewhere in your application, a query is stuck. In this case study, we will break down exactly why SQL queries get stuck in production, how to diagnose them in real-time, how to safely kill them without corrupting your data, and how to configure guardrails to prevent it from ever happening again. --- ## 🛑 Why Do Queries Get Stuck? A query doesn't just run slow because "there's a lot of data." In real-time production databases, queries typically hang or get stuck due to three primary reasons: ### 1. Lock Contention (Blocking Transactions) When a transaction performs a write operation (e.g., an `UPDATE` or `DELETE`), it acquires an **Exclusive Lock** on the affected rows or tables. If that transaction remains open (perhaps because the application code is waiting on a slow external API response before committing), any other query trying to read or write to those same rows must wait in a queue. ### 2. Cartesian Products (Cross Joins) If you accidently join two large tables without specifying matching keys (or write a query with highly inefficient join conditions), the database tries to multiply every row of the first table by every row of the second. A 10,000-row table joined improperly to another 10,000-row table creates a **100,000,000-row** in-memory dataset, freezing your CPU. ### 3. Missing Indexes on High-Frequency Filters If a query runs a full table scan on a multi-million row table, it consumes massive I/O bandwidth. Under high traffic, multiple copies of the same unindexed query run concurrently, starving the server of CPU and memory, causing all queries to bottleneck. --- ## 🔍 Step 1: Diagnose the Stuck Queries in Real-Time To fix the database, you first need to look inside its active memory. In PostgreSQL, this is done by querying the **`pg_stat_activity`** system view. ### The Diagnostic Monitoring Query Run this query to find active statements that have been running for longer than 1 second: ```sql SELECT pid, usename AS user_name, client_addr AS client_ip, state, age(clock_timestamp(), query_start) AS duration, wait_event_type, wait_event, query FROM pg_stat_activity WHERE state != 'idle' AND query NOT LIKE '%pg_stat_activity%' AND age(clock_timestamp(), query_start) > interval '1 second' ORDER BY duration DESC; ``` ### Analyzing the Output: * **`pid`**: The Process ID of the database worker running the query. You need this to kill it. * **`duration`**: How long the query has been running. If this is minutes or hours, it's definitely stuck. * **`state`**: If it is `active`, it is actively working. If it is `idle in transaction`, it means a query finished but the application hasn't closed the transaction—holding locks hostage. * **`wait_event_type` / `wait_event`**: If this shows `Lock` (e.g., `transactionid`, `relation`), your query is blocked waiting for another process to finish. --- ## ⛓️ Step 2: Trace the Blocking Locks If you see `wait_event_type = Lock`, you need to find out *who* holds the lock. Run this recursive query to list the blocked process alongside the blocking process: ```sql SELECT blocked_locks.pid AS blocked_pid, blocked_activity.query AS blocked_statement, blocking_locks.pid AS blocking_pid, blocking_activity.query AS blocking_statement FROM pg_catalog.pg_locks blocked_locks JOIN pg_catalog.pg_stat_activity blocked_activity ON blocked_activity.pid = blocked_locks.pid JOIN pg_catalog.pg_locks blocking_locks ON blocking_locks.locktype = blocked_locks.locktype AND blocking_locks.database IS NOT DISTINCT FROM blocked_locks.database AND blocking_locks.relation IS NOT DISTINCT FROM blocked_locks.relation AND blocking_locks.page IS NOT DISTINCT FROM blocked_locks.page AND blocking_locks.tuple IS NOT DISTINCT FROM blocked_locks.tuple AND blocking_locks.virtualxid IS NOT DISTINCT FROM blocked_locks.virtualxid AND blocking_locks.transactionid IS NOT DISTINCT FROM blocked_locks.transactionid AND blocking_locks.classid IS NOT DISTINCT FROM blocked_locks.classid AND blocking_locks.objid IS NOT DISTINCT FROM blocked_locks.objid AND blocking_locks.objsubid IS NOT DISTINCT FROM blocked_locks.objsubid AND blocking_locks.pid != blocked_locks.pid JOIN pg_catalog.pg_stat_activity blocking_activity ON blocking_activity.pid = blocking_locks.pid WHERE NOT blocked_locks.granted; ``` Now you have a direct map: **Process A is waiting on Process B**. --- ## ⚡ Step 3: Safely Terminate the Blocking Query Once you have identified the culprit's process ID (`pid`), you must stop it. PostgreSQL offers two functions for this. ### Option A: The Gentle Cancel (Try First) ```sql SELECT pg_cancel_backend(12345); -- Replace 12345 with the blocking pid ``` * **What it does**: Sends a `SIGINT` signal to the worker backend process. * **Best for**: Active SELECT statements or simple queries. * **Why**: It cancels the running query but keeps the database connection open, allowing the transaction to roll back gracefully. ### Option B: The Hard Kill (Use If Option A Fails) ```sql SELECT pg_terminate_backend(12345); -- Replace 12345 with the blocking pid ``` * **What it does**: Sends a `SIGTERM` signal to the backend process. * **Best for**: Queries that refuse to cancel or connection processes stuck in `idle in transaction`. * **Why**: It forcibly terminates the entire client connection. Postgres rolls back the transaction instantly, releasing all held locks. > [!CAUTION] > **Never run `kill -9` on the Postgres server process.** > Operating-system level hard killing of a postgres worker can leave the database in an inconsistent shared-memory state. To protect your data, the main Postgres daemon will trigger a **crash-recovery shutdown**, restarting the entire database server and disconnecting *every* user. --- ## 🛡️ Step 4: Add Production Guardrails (Prevention) The best way to handle stuck queries is to make sure they automatically time out before they can bring down the server. ### 1. Set a Statement Timeout Instruct your database to automatically cancel any query that runs for longer than a threshold (e.g., 30 seconds): ```sql -- Set globally for all sessions: ALTER DATABASE your_db_name SET statement_timeout = '30s'; -- Or set it inside a specific connection pool: SET statement_timeout = 30000; -- 30 seconds in milliseconds ``` ### 2. Set a Lock Timeout Ensure a query doesn't wait indefinitely to acquire a lock, blocking other connections: ```sql -- Cancel the query if it waits more than 5 seconds for a lock ALTER DATABASE your_db_name SET lock_timeout = '5s'; ``` ### 3. Idle in Transaction Timeout Kill connections that open a transaction, complete their SQL, but leave the transaction open: ```sql -- Terminate connections idle in transaction for >1 minute ALTER DATABASE your_db_name SET idle_in_transaction_session_timeout = '1min'; ``` --- ## 💡 Practice Diagnostics! On the right, we've connected the sandbox tool. Run the starter query to view the database activity. If you're building a system with concurrent traffic, keeping these queries in your back pocket will save you during your next server outage!