Technology · PRACTICAL GUIDE
Python sqlite3 says “cannot start a transaction within a transaction”: find the owner before BEGIN
The error is not a lock timeout. It means the same connection already has an active transaction. Record the Python mode, preserve the intended atomic boundary, and use one outer transaction or a SAVEPOINT.
SQLite rejects BEGIN inside an active transaction. In Python, the surprising part is often who opened the first one: autocommit=False keeps a transaction open, legacy isolation_level can start one before DML, a surrounding helper may own it, or a script may contain its own BEGIN.
Do not fix the exception by inserting commit everywhere. First capture connection.autocommit, connection.isolation_level and connection.in_transaction immediately before the failing line. Then name the owner of the unit of work and choose one transaction model.
Freeze the evidence at the failing BEGIN
Log the exact failing SQL and, on the same connection, repr(autocommit), repr(isolation_level), and in_transaction. Also identify the last INSERT, UPDATE, DELETE, REPLACE, BEGIN, SAVEPOINT, executescript, commit, and rollback on that connection.
Do not infer ownership from call-stack proximity. A helper several frames earlier, a context manager, or the connection policy can open the transaction before the line that fails.
Choose one outer transaction owner
Application-owned mode means the caller opens and closes the unit of work; lower helpers execute statements but do not issue BEGIN or COMMIT. Driver-owned mode means the connection policy opens transactions and callers use commit and rollback rather than injecting BEGIN.
For a dump or script that already contains BEGIN and COMMIT, run it under an explicitly chosen compatible script policy instead of wrapping the script in another transaction. Test rollback behavior with a deliberate mid-script failure.
Use SAVEPOINT for intentional nesting
If a helper must provide a reversible inner scope while an outer transaction may already exist, use a uniquely named SAVEPOINT, RELEASE on success, and ROLLBACK TO followed by RELEASE on failure. A second BEGIN is not a nested transaction in SQLite.
Document whether the helper is allowed to commit the outer transaction. A reusable library normally must not commit work it does not own.
Treat BEGIN IMMEDIATE as a lock-timing choice, not a nesting tool
BEGIN IMMEDIATE acquires the write transaction up front and may return SQLITE_BUSY when another connection writes. It still cannot run inside a transaction on the same connection.
If legacy DML already opened DEFERRED, decide whether that earlier work belongs to the same unit. Keep it and remove the inner BEGIN, commit it as a completed separate unit, or refactor the boundary; do not guess from the exception text.
Prove commit and rollback boundaries
Write a test that records rows before the operation, fails after the first mutation, and verifies the entire intended unit rolled back. Add a success test and a nested-helper test. Run the same tests under every supported Python connection policy.
Assert the expected in_transaction state at API boundaries. That turns a hidden mode change or future default change into an early test failure instead of a production partial commit.
