Technology · PRACTICAL GUIDE
SQLite RETURNING still blocks commit after fetchone? Finish every write cursor
A returned ID is not always a finished statement. Compare single-row, multirow and two-cursor cases before replaying a write or discarding its results.
If COMMIT reports “SQL statements in progress” after INSERT ... RETURNING, inspect your own pending write results before treating it as another process holding the database. Receiving one ID and finishing the statement are separate events.
Eight disposable file-backed tests show when fetchone works, why that result does not generalize, and how closing a cursor can allow the write to commit while losing the returned IDs.
1. Record the driver and transaction owner
Record Python, sqlite3.sqlite_version, the exact SQL, every live result cursor and the transaction setting. Our tests used CPython3.12.14 with SQLite3.53.1 on Linux, explicitly autocommit=sqlite3.LEGACY_TRANSACTION_CONTROL and isolation_level="DEFERRED". These are bounded stdlib sqlite3 results, not APSW or ORM results.
Keep the full exception and failed stage. SQLite permits an explicit COMMIT with pending SELECT statements, but pending write operations can block it. Do not classify every SQLITE_BUSY as external contention or increase a timeout before checking unfinished RETURNING writes on this connection. Do not switch transaction modes as an accidental commit shortcut.
2. A single-row success is not the general rule
We created a fresh file and table t(x integer) for each case. An unread INSERT INTO t VALUES(1) RETURNING x blocked commit. Fetching its single row with fetchone() allowed commit in this driver. For INSERT INTO t VALUES(1),(2),(3) RETURNING x, fetching only one row still blocked commit.
On separate three-row fixtures, fetchall() or repeated fetchmany(1) through the empty batch allowed commit and all three values survived reopening. Preserve every required ID before continuing. Never pair returned rows with inputs by assumed order: SQLite does not guarantee RETURNING order. A batch-fetch loop limits Python list size, not the engine’s already buffered RETURNING output.
3. Finish all write cursors without losing required output
Our sixth case closed the unread three-row cursor. Commit succeeded and reopening found all three inserted values, although the application had collected no RETURNING rows. Cursor.close() is therefore not a substitute for rollback or for delivering required IDs. Use it only when abandoning that output is an intentional application decision.
Two further cases kept two INSERT ... RETURNING cursors active on one connection. Draining the first left the second pending and commit failed; draining both allowed commit and reopening found values 1 and99. Retain cursor references in the component that owns the transaction. Do not let a helper return one visible row while silently leaving another write result active.
4. Verify commit separately from returned rows
In every failed test, in_transaction remained true immediately after the attempt. Our test then closed its pending cursors and explicitly rolled back; reopening found no inserted rows. That empty result was caused by deliberate cleanup, not by the commit exception automatically undoing the write. Replaying the INSERT while its transaction remains active can perform additional work.
For required output, consume and validate the relevant results, close their cursors, then commit through the documented transaction owner. Check the durable values from a newly opened connection and retain the exact cardinality regression. On errors, follow the application’s rollback policy rather than reporting success from a returned ID. We tested no concurrent readers, triggers, large payloads or other autocommit modes; diagnose those separately.
