LISTENing but Connection Reset by Peer: A SQLite Connection Leak That Exhausted 255 of 256 File Descriptors

A long-running local web UI kept its port in LISTEN but reset every HTTP connection: it had leaked 255 of its 256 allowed file descriptors, because with conn: on a sqlite3 connection commits but never closes.

A self-built local web UI with a job queue, kept resident by a macOS LaunchAgent, stopped opening one morning.

The awkward part was that it had not crashed. The process was running, the port was still LISTEN. Only HTTP would not go through. The last successful access had been about 36 hours earlier.

This article records how I chased that state down to file descriptor (FD) exhaustion, and from there to the behavior of sqlite3's context manager.

Symptoms

$ curl -sS http://127.0.0.1:8000/
curl: (56) Recv failure: Connection reset by peer

The same thing happened over a different route (access over VPN). It was route-independent, meaning the problem was on the process side, not the network side.

Meanwhile every naive health check was fine:

• LaunchAgent job: running
• Python process: alive
• Target port: still LISTEN
• Data volume: mounted

That trio — "the process is alive", "the port is open", "but connections are instantly reset" — is a sign to suspect not an application exception but a process that cannot acquire any more resources. If a TCP connection request cannot be accept()ed, the connection is reset without anything appearing in the application log.

Counting the culprit

So I counted the FDs the process was holding.

# total FD count for the process
lsof -p 780 | wc -l

# what is it holding, by kind
lsof -p 780 | awk '{print $NF}' | sort | uniq -c | sort -rn | head

The result was unambiguous.

Held object	Count
queue.db	122
queue.db-wal	121
Other (sockets etc.)	remainder
Total	255

And the limit for this process:

$ launchctl limit maxfiles
maxfiles    256            unlimited

255 / 256. Not one to spare. A socket for a new HTTP connection cannot be created, so the connection is reset before it is ever accept()ed. From curl's point of view, Connection reset by peer; from the application's point of view, nothing happened at all. That also explains the empty log.

SQLite in WAL mode opens both the main file and the -wal file per connection, which is why you get 122 against 121 — an almost 1:1 pair. That symmetry was a very readable fingerprint for "N connections are leaking".

Cause: with conn: does not close

Here is what was leaking.

# Before
def db():
    conn = sqlite3.connect(DB_PATH)
    conn.row_factory = sqlite3.Row
    return conn

# caller
with db() as conn:
    conn.execute("UPDATE jobs SET state = ? WHERE id = ?", (state, job_id))

with db() as conn: looks exactly like with open() for a file, so it feels like it closes. But the context manager on sqlite3.Connection only takes care of the transaction: commit() on normal exit, rollback() on exception. It does not close().

So the code above adds one connection per request. Sometimes GC reclaims them, but you cannot count on that in code that keeps references or in a long-lived process. Hitting it a few times locally shows no problem at all; it only surfaces when the process has been resident for days. That is why it took about 36 hours from the last good access to the failure being noticed.

The fix

Wrap it with contextlib.contextmanager and always close in finally.

import sqlite3
import contextlib

@contextlib.contextmanager
def db():
    conn = sqlite3.connect(DB_PATH)
    conn.row_factory = sqlite3.Row
    try:
        yield conn
        conn.commit()
    except Exception:
        conn.rollback()
        raise
    finally:
        conn.close()

Callers keep writing with db() as conn: unchanged. Only the behavior changes.

There was one more instance of the same oversight. In the code that opens a log file at worker startup and hands it to a child process, the parent's FD was never closed. I changed that to with open(...) as f: so it closes immediately. The child already holds a duplicated FD from fork/exec, so writes continue after the parent closes.

For testing, I also made the DB path overridable by environment variable, so I could verify the lifecycle without disturbing the real service queue.

DB_PATH = os.environ.get("APP_DB_PATH", DEFAULT_DB_PATH)

Confirming "it is fixed" with numbers

FD leaks are easy to believe you have fixed when you have not. I confirmed by delta, not by eye.

1. 300 iterations in a test environment

With the DB path swapped out, I wrote a test that calls the state API 300 times. Result: FD delta of 3 or less (within the noise of running the test itself). Before the fix, this would have added close to 300.

2. 120 iterations against the real service

At a moment with zero in-flight jobs, I restarted the service and did the same thing against the real thing.

BEFORE=$(lsof -p "$PID" | wc -l)
for i in $(seq 1 120); do curl -s -o /dev/null "http://127.0.0.1:8000/api/state"; done
AFTER=$(lsof -p "$PID" | wc -l)
echo "$BEFORE -> $AFTER"

The result was 23 -> 23. Across 120 requests the FD total did not move by one. After the restart both routes returned HTTP 200, and I confirmed no loss of stored data (2 registered profiles, 4 history entries, 4 outputs).

Lessons

• When the process is alive and only connections die, suspect resource limits first. Chasing the application log turns up nothing. A failure to accept() happens at a layer below your application
• A resident process's limit is not your interactive shell's limit. Looking at ulimit -n in your own shell is meaningless. Here, the LaunchAgent's soft maxfiles was 256, and that was the real ceiling. Check the value applied to the process itself
• with conn: is a transaction boundary, not a resource boundary. Worth checking for connection-like objects with context managers generally, not just sqlite3
• Verifying a leak fix comes down to "call it N times and measure the delta". Once you can show 23 -> 23 as a measurement, there is no room left for opinion

Raising the limit (increasing maxfiles) is also an option, but it only extends the time to exhaustion; as long as the leak exists you end up back in the same place. Count, close, then measure a delta of zero — that order was the reliable one.

---

I publish verification records and related tools on ACS Developer.

Originally published in Japanese on Zenn: https://zenn.dev/acs_developer/articles/sqlite-connection-leak-launchd-maxfiles

Comments

[Raknaos]

The trio "process alive / port LISTEN / connections instantly reset" is a good fingerprint, and I like that you named the mechanism: a socket that can't be accept()ed gets reset below the application, so there is nothing to log and the whole app log chase is wasted time.

with conn: committing without closing is exactly the shape I'd expect to survive review, because it reads like a correctness wrapper. The before/after lsof count across 120 requests is the cheapest regression guard I've seen for it — did you consider asserting on the FD delta in the health check itself, so the resident process alarms on drift instead of on failure?