Compare BEGIN modes for a local SQLite worker design

By Batu · English technical notes

Also published in our Blogger archive.

Compare write-intent timing for BEGIN modes

The script opens DEFERRED and IMMEDIATE transactions and checks in_transaction. Deferred waits for the first write to seek write access; IMMEDIATE seeks it at BEGIN, which can expose contention before a queue worker selects work. Rollback keeps the fixture about transaction state.

One connection cannot demonstrate a competing writer or a busy timeout, and in_transaction does not identify a lock level. Test those outcomes separately before selecting a mode.

Example

import sqlite3
from pathlib import Path
from tempfile import TemporaryDirectory

with TemporaryDirectory() as directory:
    connection = sqlite3.connect(Path(directory) / "queue.db")
    connection.execute("CREATE TABLE job(id INTEGER)")
    connection.commit()
    connection.execute("BEGIN DEFERRED")
    assert connection.in_transaction
    connection.rollback()
    assert not connection.in_transaction
    connection.execute("BEGIN IMMEDIATE")
    assert connection.in_transaction
    connection.rollback()
    assert not connection.in_transaction
    print("begin=immediate rolled_back=yes")

Expected stdout:

begin=immediate rolled_back=yes

Sources

- SQLite Transaction Control

- sqlite3.Connection.in_transaction

Prepared with AI assistance. The example uses synthetic data; its stated limits apply.