Python sqlite3 Transaction Isolation Levels

Python's standard sqlite3 module controls transaction isolation and locking behavior primarily through the isolation_level parameter and, starting in Python 3.12, the autocommit parameter. While traditional database engines provide isolation levels such as Read Committed or Repeatable Read, SQLite operates predominantly under serializable isolation, using locking mechanisms to control concurrency. This article details the specific parameters controlling these behaviors in sqlite3, how they map to SQLite transaction types, and how to configure them effectively.

The isolation_level Parameter

The primary mechanism for managing transaction boundaries and locking behavior in sqlite3 is the isolation_level parameter. It can be passed directly to sqlite3.connect() or configured dynamically on an active Connection instance.

import sqlite3

# Set during connection
conn = sqlite3.connect("database.db", isolation_level="IMMEDIATE")

# Or modify on an existing connection
conn.isolation_level = "EXCLUSIVE"

The parameter accepts the following values:

• None (Autocommit Mode): Disables Python's automatic transaction management. In this mode, sqlite3 does not automatically issue BEGIN or COMMIT statements. Every SQL statement runs in its own individual transaction unless you manually open one using conn.execute("BEGIN ...").
• "DEFERRED": Automatically opens a transaction with BEGIN DEFERRED before Data Modification Language (DML) statements such as INSERT, UPDATE, or DELETE. SQLite does not acquire a lock on the database until the first write operation occurs.
• "" (Empty String): The default behavior in Python 3.11 and earlier. It functions identically to "DEFERRED", automatically managing transactions and issuing BEGIN DEFERRED prior to DML operations.
• "IMMEDIATE": Automatically opens a transaction using BEGIN IMMEDIATE. SQLite immediately reserves a write lock. Other connections can continue to read from the database, but no other connection can begin a write transaction.
• "EXCLUSIVE": Automatically opens a transaction using BEGIN EXCLUSIVE. SQLite obtains an exclusive lock, preventing all other connections from reading or writing to the database until the transaction completes.

The autocommit Parameter (Python 3.12+)

Python 3.12 introduced the autocommit parameter to sqlite3.connect() to resolve long-standing quirks with PEP 249 transaction handling and legacy implicit transaction starts.

conn = sqlite3.connect("database.db", autocommit=False)

The autocommit parameter supports three states:

• False (PEP 249 Compliance): The driver manages transactions according to DB-API 2.0 standards. It automatically issues a BEGIN statement before any DML or DDL statements. When autocommit=False, changing isolation_level is not permitted.
• True: Completely turns off driver-managed transactions. All statements are automatically committed immediately upon execution unless explicit BEGIN statements are executed manually.
• sqlite3.LEGACY_TRANSACTION_CONTROL: The default setting for backward compatibility. It defers transaction handling to the legacy isolation_level logic described above.

SQLite-Level Isolation via PRAGMA

Because Python’s isolation_level maps to SQLite's lock-acquisition modes rather than standard ANSI SQL isolation levels, SQLite itself defaults to fully SERIALIZABLE transactions.

The only other ANSI isolation level supported by SQLite is READ UNCOMMITTED. This cannot be set through a Python connection parameter; it must be enabled using a SQLite PRAGMA statement in shared-cache or WAL (Write-Ahead Logging) mode:

conn = sqlite3.connect("database.db", isolation_level=None)
conn.execute("PRAGMA read_uncommitted = 1;")

Setting PRAGMA read_uncommitted = 1 permits dirty reads on database connections operating on the same shared cache, downgrading isolation from Serializable to Read Uncommitted for SELECT operations.