# 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

1. **High concurrency**  
    With hundreds or thousands of simultaneous users, even occasional long queries can pile up.
    
2. **User impatience**  
    Modern UIs often cancel slow XHRs (e.g. debounced type-ahead, switching pages) — but your backend, by default, keeps churning away.
    
3. **Resource waste**  
    CPU cycles, disk reads, and memory buffers on the DB server for queries whose results will never be consumed.
    
4. **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:

```js
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`](https://nodejs.org/api/events.html#class-abortcontroller) pattern:
    

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

---

## Cancelling MySQL Queries

### 1\. Using the `mysql` (callback) module

```js
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:

```js
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:

```ts
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

1. **Detect** client abort with `req.on('aborted')` or `AbortController`.
    
2. **Wrap** your database cursor in an Observable (or manual callback) that supports a teardown hook.
    
3. **Link** the HTTP abort signal to that teardown so the SQL query is destroyed mid-flight.
    
4. **Release** your connection back to the pool immediately upon cancellation.
    
5. **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.