Database Transaction Leak

Source: Dev.to

Introduction

We often talk about memory leaks, but there is another silent performance killer in backend development: Database Transaction Leaks.

I recently spent time debugging a legacy codebase where a specific module’s tests passed perfectly in isolation but failed consistently when run as part of the full suite. The culprit? A “Database connection timeout” that I initially dismissed as a fluke. Here is how I discovered that our code was “leaking” database connections and how we fixed it.

The Symptom: The “Loner” Test

In isolation, my User module tests were green. However, when run alongside fifty other tests, they would suddenly time out.

The database wasn’t actually slow; it was exhausted. Previous tests were opening transactions and never closing them, holding onto connections from the pool until there were none left for the subsequent tests.

The Culprit #1: The Early Return Trap

In our legacy controllers, many functions relied on manual transaction management. Several instances had an early return where the developer forgot to close the transaction before exiting the function.

Buggy Code

const t = await startTransaction();
try {
  if (someCondition) {
    // Early return! The transaction 't' stays open forever 
    // until the database or server kills the process.
    return { status: 400, message: "Invalid Request" };
  }

  await t.commit();
} catch (e) {
  await t.rollback();
}

The Culprit #2: Shared Transaction Ownership

The second issue was more subtle: Nested Transaction Suicide. A parent function created a transaction and passed it to a child function, which then committed or rolled back the transaction itself. When control returned to the parent, the parent attempted to commit a transaction that was already closed.

Buggy Code

async function childFunction(t) {
  try {
    const data = await db.create({}, { transaction: t });
    await t.commit(); // Child closes the transaction
    return data;
  } catch (e) {
    await t.rollback();
    throw e;
  }
}

async function parentScope() {
  const t = await startTransaction();
  try {
    const data = await childFunction(t);
    await t.commit(); // Error! The transaction is already finished.
    return data;
  } catch (e) {
    await t.rollback();
  }
}

Why Didn’t This Break Production?

You might wonder: if we were leaking connections, why didn’t the production server crash every hour?

The answer was PM2. Our production environment used PM2 to manage the Node.js processes. When the connection pool became exhausted and the app began to hang or crash, PM2 automatically restarted the instance. This cleared the “leaked” connections, acting as a temporary (and dangerous) bandage. Users just perceived this as “the app is occasionally slow.”

The Solution: Proper Transaction Management

1. Explicit Lifecycle Management

Always ensure every possible code path (especially early returns) handles the transaction.

const t = await startTransaction();
try {
  if (someCondition) {
    await t.rollback(); // Always clean up before returning!
    return { status: 400 };
  }
  await t.commit();
} catch (e) {
  await t.rollback();
}

2. The “Single Owner” Principle

A good rule of thumb: the function that creates the transaction should be the one to close it. If you pass a transaction to a child function, the child should use it but never commit or rollback itself.

async function childFunction(t) {
  // Use the transaction 't', but don't commit/rollback here
  return await db.create({}, { transaction: t });
}

async function parentScope() {
  const t = await startTransaction();
  try {
    await childFunction(t);
    await t.commit(); // Only the creator manages the lifecycle
  } catch (e) {
    await t.rollback();
  }
}

Conclusion

By fixing these transaction leaks, our test suite went from flaky and slow to stable and fast. If your tests pass individually but fail in a group, don’t ignore those “Connection Timeout” errors—you might just have a leak in your database logic.