A billing run holds one DB-API connection open for hours and silently writes only some of its rows — how do you diagnose and fix it?

## Start by making the truncation visible A run that writes "only some" rows and reports success has two failures, and the reporting one is the more serious. So the first fix is instrumentation, not architecture: log the number of rows the run *intended* to process, log a count and a commit marker at every transaction boundary, and assert the totals at the end. `cursor.rowcount` after an `executemany()` gives you the driver's own count of affected rows, and comparing it against the length of the batch you passed turns a silent truncation into a loud one. Until that exists, every theory below is unfalsifiable. ## Cause one: one transaction for the whole run A PEP 249 connection is transactional by default and opens its transaction implicitly. A job that loops for hours and calls `commit()` once at the end is holding one transaction the entire time. Any failure before that call — an exception, an out-of-memory kill, a deployment rolling the pod — discards everything, because closing a connection with an open transaction is a specified *implicit rollback*. The symptom is often not "nothing written" but "a prefix written": the run was restarted, the second attempt got further, and what you are looking at is the last successful partial attempt. A long-open transaction is also expensive on the server side in ways that are the database's story rather than Python's, and your operators will notice before you do. ## Cause two: the error was swallowed The second pattern is a run that *does* commit per batch, wrapped in a broad `except Exception:` that logs and continues — or worse, `pass`. Now a failure at batch nine of twenty leaves nine batches committed, eleven missing, and a success line in the log. This is the shape that most deserves the name silent truncation, and it is a code-review finding, not a debugging one: catch the driver's error class, add context, re-raise, and let the scheduler decide about retries. ## Cause three: the connection did not survive the idle time Long-running jobs frequently do a slow non-database step between database steps. Meanwhile a server-side idle timeout, a connection proxy, a load-balancer idle reaper or a failover can drop the socket. On the next `execute()` you get the driver's `OperationalError` or `InterfaceError` — if you are lucky. If a pooling layer or a reconnect helper is in play, it may hand you a *new* connection transparently, and any uncommitted work from before the drop is simply gone while the code carries on. That is the case that produces truncation with no exception anywhere. The diagnosis is correlation: line up the run's commit markers against the database's own log of terminated connections and against the idle windows in your job. The fix is not a longer timeout. It is to stop holding a connection across the idle stretch — acquire one per unit of work, from a pool that validates liveness before handing it over, and release it before the slow non-database phase. ## Cause four: the connection is being shared If the run was parallelised, check the driver module's `threadsafety` global before assuming a connection may cross threads: PEP 249 defines levels 0 to 3, and only the highest says connections and cursors may be shared freely. Sharing one connection below that level produces interleaved statements in one transaction and corrupted result sets — which, again, looks like missing rows. Give each worker its own connection. ## The design that makes it stop happening Structure the run as bounded units of work: read a batch, write it, commit, repeat. The batch size is a tradeoff — larger means fewer commits and less overhead, smaller means less to redo after a failure and shorter transactions — and a few hundred to a few thousand rows is the usual landing zone. Keep the reading cursor streaming with `fetchmany()` rather than materialising the whole set, and be aware that on some drivers committing invalidates an open result set on the same connection, so read the ids first or use a separate connection for the read. Then make the run **re-runnable**. Idempotent writes keyed on a natural key — an upsert on `(subscription_id, period)` rather than a blind insert — turn recovery into "run it again" instead of "reconcile by hand", and mean a partial run is a delay rather than an incident. Record progress in the same transaction as the work, so the checkpoint and the rows can never disagree. Finally, close deterministically: `contextlib.closing` around cursors and connections releases resources at a known point instead of at finalisation time. ## What a strong answer sounds like The candidate who has lived through this does not jump to a cause. They ask what the run's commit cadence is, whether the error path is swallowed, how long the connection sits idle, and whether the writes can be replayed — and they say plainly that a job which cannot be safely re-run is the real defect, whatever dropped the connection.