skip to content

What does Python's sqlite3 do about transactions by default, and how does the autocommit attribute change it?

level: middleimportance: must knowfreq 60%

answer

  1. Nothing commits unless you say so
  2. Implicit BEGIN before writes only
  3. Closing throws away open work
  4. One attribute picks the mode
  5. with con: commits, does not close

basics

~20 s

By default sqlite3 opens a transaction implicitly before an INSERT, UPDATE, DELETE or REPLACE and leaves it open until you call Connection.commit; nothing else commits for you. Setting Connection.autocommit to True or False replaces that legacy behaviour with explicit modes.

solid answer

~40 s

The default is legacy transaction control, driven by `Connection.isolation_level` (default `''`): sqlite3 issues a `BEGIN` before data-modifying statements only - not before a SELECT and not before DDL - and never commits on its own, so `Connection.commit` is yours to call and `Connection.close` discards whatever is still open. `Connection.in_transaction` tells you whether one is pending. Setting `isolation_level=None` gives the old autocommit behaviour. Since Python 3.12 there is a cleaner switch: `Connection.autocommit`, whose default is `sqlite3.LEGACY_TRANSACTION_CONTROL`. Set it to `False` for PEP 249 semantics, where a transaction is always open and commit or rollback immediately starts the next one, or to `True` so every statement commits itself. Using the connection as a context manager commits on success and rolls back on an exception - it does not close the connection.

code

python · 10 lines
python
import sqlite3

con = sqlite3.connect(':memory:')
con.execute('CREATE TABLE job(id INTEGER PRIMARY KEY)')
print(con.in_transaction)   # False: DDL did not open one
con.execute('INSERT INTO job VALUES (1)')
print(con.in_transaction)   # True: implicit BEGIN before the INSERT
con.rollback()
print(con.execute('SELECT count(*) FROM job').fetchone())   # (0,)
con.close()

go deeper

for a junior

Remember that a write is not saved until commit is called, and that closing the connection throws away uncommitted work. Practise the shape: execute, commit, close.

for a middle

Explain when the implicit BEGIN is issued and when it is not, what isolation_level values do, and what the autocommit attribute added in 3.12 changes. Know that the connection context manager commits rather than closes.

for a senior

Demonstrate judgement about transaction scope: batching writes for throughput, keeping locks short, never holding a transaction across a network call, and choosing the mode explicitly at connect time rather than inheriting the legacy default.

for a principal

Own the convention: one documented transaction mode across the codebase, a data-access layer where commit boundaries are visible rather than incidental, and a rule for migrations that does not rely on implicit BEGIN behaviour.

### Three modes, one attribute apart sqlite3 has carried its own transaction behaviour since long before PEP 249 settled the question, and Python 3.12 added a way to opt out of it. On 3.14 all three modes are live. **Legacy mode (the default).** `Connection.autocommit` is `sqlite3.LEGACY_TRANSACTION_CONTROL`, and `Connection.isolation_level` is the empty string. In this mode sqlite3 inspects each statement you execute and issues an implicit `BEGIN` before an INSERT, UPDATE, DELETE or REPLACE if no transaction is open. It does **not** open one before a SELECT, and since Python 3.6 it does not open one before DDL either - so a `CREATE TABLE` runs in SQLite's own autocommit mode and is durable the moment it returns. Nothing ever commits implicitly: you call `Connection.commit`, or the changes sit in an open transaction until you roll back or close. `isolation_level` also chooses *which* BEGIN is issued: `''` and `'DEFERRED'` mean `BEGIN DEFERRED`, and `'IMMEDIATE'` or `'EXCLUSIVE'` take the write lock up front. Setting `isolation_level = None` disables the implicit BEGIN entirely, which is the historical way to get autocommit. **PEP 249 mode.** Set `autocommit = False`, or pass `autocommit=False` to `connect`. Now a transaction is always open - including around reads - and `commit()` or `rollback()` closes the current one and starts the next immediately. This is what a driver-agnostic layer expects, and it is the mode to pick when the same code has to behave the same way against another database driver. **Autocommit mode.** Set `autocommit = True` and every statement commits as it completes. There is no open transaction to lose, and no way to group statements atomically without issuing your own `BEGIN` and `COMMIT`. ### The failure everyone meets first A script inserts rows, prints a success message and exits, and the file is empty afterwards. In legacy mode the insert opened a transaction; the script never called `commit`; `Connection.close` - or interpreter shutdown finalising the connection - discarded it. The row was never in the database, only in an uncommitted transaction. `Connection.in_transaction` is the diagnostic: it is `True` exactly when there is an open transaction with work in it. ### The context manager commits, it does not close ```python with sqlite3.connect('geo.db') as con: con.execute('INSERT INTO hit VALUES (?)', (1,)) ``` On a clean exit this commits; on an exception it rolls back and re-raises. It is a *transaction* context manager, and the connection is still open afterwards - a genuine difference from `open()` that trips people who assume every `with` closes its resource. If you want both, nest: a `contextlib.closing` around the connection, and `with con:` around each unit of work. ### Why the mode matters beyond correctness Transaction scope is the single biggest performance lever in embedded SQLite. Each committed transaction is a durable write, so inserting ten thousand rows with a commit per row means ten thousand commits; the same rows inside one transaction commit once and can be orders of magnitude faster. Equally, an over-long transaction is the classic source of write contention, because SQLite allows only one writer at a time and your lock is held until you commit. The rule that follows is to make the transaction span the unit of work and nothing more - and in particular never to hold one open across a network call or user think-time, because the wait becomes lock hold time for every other writer. ### Rollback is real, but DDL is not always in it SQLite is transactional over DDL too when the DDL is inside an explicit transaction, but in legacy mode sqlite3 will not have opened one for you, so a `CREATE TABLE` executed on its own has already committed and a later `rollback()` will not remove it. If you need a migration to be all-or-nothing, take control of the boundary yourself - `autocommit = False`, or an explicit `BEGIN` - rather than relying on the implicit rule. ### Choosing For new code in a service, `autocommit = False` with explicit commits is the least surprising, because it matches what every other Python database driver does. For a batch loader, legacy or explicit control with one big transaction per chunk is the fastest. For a throwaway script or an interactive session, `autocommit = True` removes the forgotten-commit class of bug entirely. What you should not do is leave the mode implicit and hope: it is one attribute, and naming it at connect time documents the intent.

  • Your loader inserts a million rows and is far slower than expected. How does transaction scope explain it?
    A commit per row is a durable write per row, and the cost is dominated by that rather than by the inserts. Group rows into one transaction per chunk - a few thousand at a time - so the commit cost is amortised, and use executemany so the statement compiles once. Keep the chunk bounded so a failure does not roll back the entire run, and so no single write lock is held for very long.
  • What is the difference between BEGIN DEFERRED and BEGIN IMMEDIATE for a writing connection?
    Deferred takes no lock until the first statement needs one, so a transaction that reads then writes may fail to acquire the write lock partway through and cannot always be retried safely. Immediate takes the write lock at BEGIN, so contention is resolved before any work is done and a busy failure costs nothing. For read-then-write units of work, immediate is the safer choice.
  • Does using the connection in a with block close it at the end?
    No. The connection's context manager commits on a clean exit and rolls back on an exception, then leaves the connection open for reuse. Closing is a separate call to Connection.close, or contextlib.closing wrapped around the connection. Treating with as a close is a common source of leaked connections in long-running processes.

saying these in an interview costs you the question

  • Assumes execute commits the row by itself
  • Says a with block closes the connection
  • Thinks close commits pending changes
  • Believes SELECT statements open a transaction in legacy mode
  • Cannot name any way to turn autocommit on

context

resolving links…