Stop Wasting Queries: How to Cancel MySQL Requests When the Client Disconnects

Stop Wasting Queries: How to Cancel MySQL Requests When the Client Disconnects

Introduction

In a high-traffic web application, many users may be clicking around at once. Imagine a user who requests a long list of items, then quickly navigates away before the server has finished fetching them from the database. By default, that SQL query will keep running until it completes — consuming CPU, memory, and I/O on your DB server — even though the client no longer needs the response.

By wiring up a cancellation signal from the HTTP layer down to your database client, you can abort in-flight queries. When the request is canceled (e.g. the XHR is .abort()ed), your API can tear down the SQL query early, allowing MySQL to stop working on results you’ll never send. This not only conserves resources, but also reduces lock contention and improves overall scalability.

In this article you’ll learn:

How to detect “request canceled” in Node.js
How to cancel MySQL queries mid-flight
How to wrap everything in RxJS Observables for clean, composable cancellation handling

The Problem with Long-Running Queries

High concurrency
With hundreds or thousands of simultaneous users, even occasional long queries can pile up.
User impatience
Modern UIs often cancel slow XHRs (e.g. debounced type-ahead, switching pages) — but your backend, by default, keeps churning away.
Resource waste
CPU cycles, disk reads, and memory buffers on the DB server for queries whose results will never be consumed.
Cascading delays
Unneeded queries can slow down other users’ requests and potentially exhaust your connection pool.

The solution is to hook into the XHR’s cancellation and propagate that signal all the way down to MySQL so the query is aborted.

Detecting Cancellations in Node.js

In a typical Express handler:

app.get('/items', (req, res) => {
  // `req` is an instance of http.IncomingMessage
  req.on('aborted', () => {
    // called if client disconnects or XHR is aborted
  });

  // ... perform DB query ...
});

The 'aborted' event fires if the client closes the connection or if your frontend calls xhr.abort().
You can also use the modern AbortController pattern:

const controller = new AbortController();
req.on('aborted', () => controller.abort());

Cancelling MySQL Queries

1. Using the `mysql` (callback) module

const mysql = require('mysql');
const pool  = mysql.createPool({ /* config */ });

app.get('/items', (req, res) => {
  // Create an AbortController for this request
  const controller = new AbortController();
  req.on('aborted', () => {
    controller.abort();
  });

  // Acquire a connection
  pool.getConnection((err, conn) => {
    if (err) return res.status(500).end();

    const query = conn.query('SELECT * FROM items WHERE ...');

    // If cancellation happens, destroy the query & release connection
    controller.signal.addEventListener('abort', () => {
      query.destroy();    // aborts the underlying socket/query
      conn.release();
    });

    query
      .on('error', err => {
        conn.release();
        if (!controller.signal.aborted) {
          res.status(500).json({ error: err.message });
        }
      })
      .on('result', row => {
        // stream rows to client...
      })
      .on('end', () => {
        conn.release();
        if (!controller.signal.aborted) {
          res.end();
        }
      });
  });
});

Calling query.destroy() sends a COM_QUERY kill to MySQL
You must conn.release() to return the connection to the pool

2. Using `mysql2/promise`

The promise-based client doesn’t expose a direct .destroy(), but you can still:

const mysql = require('mysql2/promise');
const pool  = mysql.createPool({ /* config */ });

app.get('/items', async (req, res) => {
  const controller = new AbortController();
  req.on('aborted', () => controller.abort());

  const conn = await pool.getConnection();
  let query;

  try {
    // Initiate the query, saving the Query object
    query = conn.query('SELECT * FROM items WHERE ...');

    // Tie cancellation to the query
    controller.signal.addEventListener('abort', () => {
      // Under the hood, the first element of the promise result
      // is a Query object you can destroy:
      query[0].destroy();
      conn.release();
    });

    const [rows] = await query;
    if (!controller.signal.aborted) {
      res.json(rows);
    }
  } catch (err) {
    if (!controller.signal.aborted) {
      res.status(500).json({ error: err.message });
    }
  } finally {
    if (!controller.signal.aborted) {
      conn.release();
    }
  }
});

Wrapping in RxJS for Clean Cancellation

RxJS shines at managing streams and cleanup. Here’s how you can wrap your SQL query in an Observable that automatically tears down when the HTTP request is aborted:

import { Observable, fromEvent } from 'rxjs';
import { takeUntil } from 'rxjs/operators';
import mysql from 'mysql2';

function queryObservable(conn: mysql.Connection, sql: string, params: any[] = []) {
  return new Observable<mysql.RowDataPacket>(subscriber => {
    const query = conn.query(sql, params);

    query
      .on('error', err => subscriber.error(err))
      .on('result', row => subscriber.next(row))
      .on('end', () => subscriber.complete());

    // When the subscriber unsubscribes, destroy the query
    return () => {
      query.destroy();
    };
  });
}

app.get('/items', (req, res) => {
  const conn = pool.getConnectionSync();
  const cancel$ = fromEvent(req, 'aborted');

  queryObservable(conn, 'SELECT * FROM items WHERE ...')
    .pipe(takeUntil(cancel$))
    .subscribe({
      next: row  => res.write(JSON.stringify(row) + '\n'),
      error: err => {
        conn.release();
        if (!req.aborted) res.status(500).json({ error: err.message });
      },
      complete: () => {
        conn.release();
        if (!req.aborted) res.end();
      }
    });
});

Why RxJS?

Automatic cleanup: unsubscribing triggers your teardown (destroying the query).
Declarative pipelines: you can map(), filter(), take(), or even retry logic around your DB calls.
Consistency: use the same patterns for HTTP streams, WebSocket streams, database cursors, and more.

Putting It All Together

Detect client abort with req.on('aborted') or AbortController.
Wrap your database cursor in an Observable (or manual callback) that supports a teardown hook.
Link the HTTP abort signal to that teardown so the SQL query is destroyed mid-flight.
Release your connection back to the pool immediately upon cancellation.
Optionally layer RxJS operators to handle retries, backpressure, or result-stream transformations.

Conclusion

By propagating XHR cancellations all the way down to MySQL, you:

Avoid wasting database resources on results you’ll never send.
Reduce average query load, improving performance for all users.
Give yourself a unified, clean way to handle streaming data with RxJS.

As modern APIs increasingly embrace reactive patterns, wiring up cancellation signals is not just a “nice-to-have” — it’s essential for scaling efficiently under load.