17.2.3.4 Shutdown Transaction Completion

Shutdown transaction completion addresses the specific risk of a database transaction being interrupted mid-way through a shutdown sequence, which is a narrower and more consequential concern than simply closing a connection cleanly, since an abruptly terminated transaction can leave related rows in an inconsistent, partially-applied state depending on exactly when the interruption occurred relative to the transaction's own commit point.

Why connection closure alone is not sufficient

Cleanly closing a database connection pool, as covered in general shutdown connection handling, does not by itself guarantee that an in-progress, multi-statement transaction completes correctly; if the pool is closed while a transaction is open but not yet committed, the database itself generally rolls back that transaction automatically, which is actually the safe, correct outcome, but only if the application's own logic does not assume the transaction succeeded when it did not:

async function transferFunds(fromAccount, toAccount, amount) {
  const client = await pool.connect();
  try {
    await client.query('BEGIN');
    await client.query('UPDATE accounts SET balance = balance - $1 WHERE id = $2', [amount, fromAccount]);
    await client.query('UPDATE accounts SET balance = balance + $1 WHERE id = $2', [amount, toAccount]);
    await client.query('COMMIT');
  } finally {
    client.release();
  }
}

If the process is interrupted between the two UPDATE statements and before COMMIT, the database's own transactional guarantee ensures neither update is actually persisted, the database rolls back automatically, which is correct; the actual risk is in the calling code's own assumption about whether this operation succeeded, not in the database's transactional integrity itself.

Waiting for in-flight transactions to genuinely complete

Rather than relying entirely on automatic rollback as an acceptable outcome, the shutdown sequence should actively wait for any transaction currently in progress to reach an actual commit or an explicit, deliberate rollback before the connection pool is closed at all, treating mid-transaction interruption as something to avoid rather than something to merely tolerate safely:

let activeTransactions = new Set();

async function withTransaction(fn) {
  const promise = fn();
  activeTransactions.add(promise);
  try {
    return await promise;
  } finally {
    activeTransactions.delete(promise);
  }
}

process.on('SIGTERM', async () => {
  server.close();
  await Promise.allSettled(activeTransactions);
  await pool.end();
});

Tracking active transactions explicitly and waiting for them to settle, one way or the other, before closing the pool ensures every transaction reaches a genuine, deliberate conclusion rather than depending on automatic rollback as the acceptable fallback outcome.

Idempotency for operations that might be retried after interruption

For operations triggered by an external client that might retry after observing a connection failure during shutdown, designing the operation to be safely repeatable, an idempotency key preventing the same logical operation from being applied twice, protects against a double-application if the client retries after a connection was interrupted mid-operation:

async function processPayment(idempotencyKey, amount) {
  const existing = await db.query('SELECT * FROM payments WHERE idempotency_key = $1', [idempotencyKey]);
  if (existing.rows.length > 0) return existing.rows[0];

  return await db.query(
    'INSERT INTO payments (idempotency_key, amount) VALUES ($1, $2) RETURNING *',
    [idempotencyKey, amount]
  );
}

This protects against the specific scenario where a client legitimately retries an operation after its original attempt's connection was interrupted during a shutdown, without knowing whether that original attempt actually completed or not before the interruption occurred.

Distributed transactions and saga-pattern considerations

For an operation spanning multiple services, each with its own database, a shutdown interrupting one step of a multi-step saga or distributed transaction can leave the overall operation in a partially completed state across services, which requires a compensating action mechanism, not just careful single-database transaction handling, to fully resolve:

async function bookTrip(reservation) {
  await reserveFlight(reservation); // step 1
  try {
    await reserveHotel(reservation); // step 2, may be interrupted by shutdown
  } catch (err) {
    await cancelFlightReservation(reservation); // compensating action
    throw err;
  }
}

A shutdown occurring specifically between two steps of a saga requires the same compensating-action handling that would apply to any other mid-saga failure, which is a meaningfully more involved consideration than single-database transaction handling alone and should be designed for explicitly rather than assumed to be covered by ordinary connection closure practices.

Logging transaction state at the moment of interruption

If a transaction is genuinely interrupted during shutdown despite best efforts to wait for it to complete, logging exactly what was in progress at that moment provides the information needed for a subsequent investigation or manual reconciliation, rather than leaving no record of what specifically was interrupted:

process.on('SIGTERM', async () => {
  if (activeTransactions.size > 0) {
    console.error(`Shutdown proceeding with ${activeTransactions.size} transactions still active`);
  }
  // ... proceed with shutdown after a bounded wait
});

This kind of explicit, observable record is valuable specifically because it should be a rare, exceptional event if the shutdown sequence's own waiting logic is working correctly; its occurrence is itself worth investigating rather than silently accepted as routine.

Testing transaction interruption deliberately

Deliberately triggering a shutdown while a long-running transaction is genuinely in progress, in a test environment rather than discovering this behavior for the first time in production, confirms the shutdown sequence actually waits correctly rather than assuming it does based on the code's apparent design:

curl http://localhost:3000/slow-transaction-endpoint &
sleep 1
docker stop --time=30 my-api

Confirming through this kind of test that the transaction either completes successfully or rolls back cleanly, with no partial, inconsistent state left behind, validates the actual, observed behavior rather than relying on assumption.

Common mistakes

• Assuming connection pool closure alone correctly handles in-progress transactions, without explicitly waiting for them to reach an actual conclusion first.
• Not designing client-retriable operations with idempotency protection, risking a double-applied operation if a client retries after a connection was interrupted during shutdown.
• Treating a multi-service saga or distributed transaction with the same single-database closure handling that is sufficient for a single, local transaction, missing the need for compensating actions specifically.
• Not logging or recording when a transaction was genuinely interrupted despite the shutdown sequence's best efforts, leaving no trail for later investigation.
• Never deliberately testing shutdown behavior under a genuinely active, long-running transaction, relying instead on assumption about how the shutdown sequence will behave.

Shutdown transaction completion goes beyond simply closing database connections cleanly, requiring the shutdown sequence to actively wait for in-progress transactions to reach a genuine conclusion, idempotency protection for operations a client might retry after an interruption, and explicit handling of multi-step sagas through compensating actions where a single database's own transactional guarantees do not extend across service boundaries.