skip to content

Why do a DB-API connection's uncommitted writes vanish when you close it?

level: middleimportance: must knowfreq 55%

answer

  1. There is no begin() in the API
  2. The connection, not the cursor, holds it
  3. Closing is not the same as finishing
  4. The spec calls it an implicit rollback
  5. with connection commits but never closes

basics

~20 s

PEP 249 connections are transactional by default and open a transaction implicitly, with no BEGIN call in the API. Only commit() makes the work durable; close() without it performs an implicit rollback, so the writes are discarded.

solid answer

~50 s

The DB-API has no `begin()`. A conforming connection is transactional out of the box: the first statement that needs a transaction starts one, and it stays open until you call `connection.commit()` or `connection.rollback()`. PEP 249 specifies that closing a connection with an open transaction performs an **implicit rollback**, so an `INSERT` followed by `close()` leaves nothing behind — and it does so silently, with no exception to notice. The transaction is a property of the *connection*, not of a cursor, so several cursors on one connection share it and one `commit()` commits all of their work. Most drivers also make the connection object a context manager whose `__exit__` commits on success and rolls back on an exception — but note that it commits the transaction and does **not** close the connection, which is the classic misreading of `with connection:`.

code

python · 16 lines
python
import os
import sqlite3
import tempfile

path = os.path.join(tempfile.mkdtemp(), "billing.db")

con = sqlite3.connect(path)
cur = con.cursor()
cur.execute("CREATE TABLE charge(id INTEGER PRIMARY KEY, cents INTEGER)")
cur.execute("INSERT INTO charge(cents) VALUES (?)", (1999,))
print(cur.rowcount)  # 1 -- the statement did run
con.close()          # no commit(): implicit rollback

again = sqlite3.connect(path)
print(again.cursor().execute("SELECT count(*) FROM charge").fetchone())  # (0,)
again.close()

go deeper

for a junior

Remember that writes are invisible and impermanent until commit() is called on the connection, and that closing without it throws them away silently. Get into the habit of committing at the end of each unit of work.

for a middle

Explain the implicit transaction, the specified rollback-on-close, and that the transaction is per connection so cursors share it. Know that the connection's with block is a transaction boundary, not a close.

for a senior

Demonstrate judgement about where commit boundaries go in a real job — units of work that are consistent and re-runnable, errors that propagate so the rollback happens, and no broad except swallowing driver failures.

for a principal

Own the policy: whether services run with implicit transactions and an enforced unit-of-work scope, when autocommit is allowed, and how you keep 'someone forgot to commit' out of the codebase by construction rather than by review.

## No BEGIN, but a transaction anyway Look at the PEP 249 connection surface and you will notice something missing: there is `commit()`, there is `rollback()`, there is `close()`, and there is no `begin()`. That is deliberate. The specification says a conforming connection is transactional by default, and the transaction is opened *implicitly* — the driver issues whatever the engine needs before the first statement that requires one. Your side of the contract is only to say how it ends. The two endings are explicit. `commit()` makes everything since the last transaction boundary durable and starts the next transaction. `rollback()` throws that work away. The specification also nails down the third case, the one that catches people: **closing a connection with an open transaction performs an implicit rollback.** Not a commit, not an error — a silent discard. A script that inserts rows and then calls `close()` (or simply exits, letting the connection be finalised) writes nothing, and nothing in the output says so. This is the right default. The alternative — commit-on-close — would mean a process killed halfway through a multi-statement update could persist a half-finished state, which is exactly what transactions exist to prevent. But it does mean that in Python, forgetting `commit()` is a silent data-loss bug rather than a loud one, and it is the single most common defect in hand-written database code. ## The transaction belongs to the connection A cursor is a handle for executing statements and reading results; it has no transaction of its own. If you take three cursors from one connection and write through all three, they are all inside the same transaction, and one `connection.commit()` commits all of it. Conversely, two *connections* are two transactions, and neither can see the other's uncommitted work — which is why a test that writes on one connection and reads on another sees nothing until the writer commits, and why a job that opens a second connection "just for the reads" can be genuinely confusing to debug. A related trap: on many drivers, committing invalidates result sets that are still open on that connection's cursors. If you are looping with `fetchmany()` on one cursor and committing writes from another, read the driver's rules before assuming the loop survives. ## The `with` block does not close anything Most drivers implement the connection as a context manager, and the convention is that `__exit__` **commits on clean exit and rolls back if the block raised** — a transaction boundary, not a resource boundary. The connection stays open and usable afterwards. So `with connection: ...` is the right way to scope a unit of work and the wrong way to manage a connection's life. If you want deterministic closing, wrap the connection in `contextlib.closing`, or nest the two: `closing` for the resource on the outside, the connection's own `with` for the transaction inside. Cursors are the same story in reverse — they hold driver-side resources, and `contextlib.closing` around `connection.cursor()` releases them at a known point rather than at whatever moment the object is finalised. ## Autocommit, and why the default matters Drivers usually offer a way to turn implicit transactions off, so that each statement commits on its own. That mode is genuinely useful for DDL on engines that will not run schema changes inside a transaction, and for read-only work where a transaction only pins server resources. It is also how you *lose* atomicity: with autocommit on, a partially-completed batch of related writes is a partially-committed batch, and there is no rollback to undo it. The concrete switch is driver-specific — the stdlib sqlite3 module, for example, gained an explicit `autocommit` attribute in Python 3.12 alongside its older control mode. Whether DDL is transactional at all is an engine question rather than a Python one; some engines roll back a `CREATE TABLE`, some quietly commit around it. Do not assume either. ## What good code looks like Scope a transaction to a unit of work, not to a process. Commit at the boundary where the state is consistent and re-runnable, and let errors propagate so the rollback actually happens — a bare `except Exception: pass` around database work converts a rollback into a silently incomplete write, which is far worse than a crash. Never rely on interpreter shutdown to finish anything: if the process is killed between the last write and the `commit()`, the work is gone, and that is the design working as intended.

  • Does `with connection:` close the connection when the block ends?
    No — on the drivers that implement it, the connection's context manager is a *transaction* boundary: `__exit__` commits if the block finished cleanly and rolls back if it raised, then leaves the connection open and reusable. To close deterministically, wrap it in `contextlib.closing`, or nest the two: `closing` on the outside for the resource, the connection's own `with` on the inside for each unit of work. Assuming the `with` closes it is a common way to leak connections.
  • Two cursors on the same connection each insert a row and only one of them commits. What is persisted?
    Both rows. The transaction belongs to the connection, not the cursor, so every cursor taken from that connection writes into the same transaction and a single `commit()` on the connection makes all of it durable. Cursors are only statement-and-result-set handles. If you genuinely need two independent transactions you need two connections — and then neither sees the other's uncommitted work.
  • Your job wraps its database block in `except Exception: pass` and logs success. What actually happens?
    The rollback still occurs when the connection is later closed, but nobody is told: the job reports success having written nothing, or having written only what it had committed before the failure. Swallowing the driver's exception converts a loud, recoverable failure into silent data loss, which is strictly worse than crashing. Catch the driver's error class only to add context, then re-raise, and let the caller decide whether to retry.

The connection is an unsent draft: everything you type is real to you and invisible to everyone else, and closing the window without sending discards it rather than delivering it.

saying these in an interview costs you the question

  • Thinks close() commits any pending work
  • Believes each execute() is committed on its own by default
  • Says the transaction belongs to the cursor, not the connection
  • Assumes `with connection:` closes the connection
  • Expects a second connection to see uncommitted rows
  • Relies on interpreter shutdown to flush the writes

context

resolving links…