Avoiding Event-Loop Blocking with aiosqlite

Every sqlite3 call is synchronous and blocking. When a coroutine calls conn.execute(...) directly on an asyncio event loop, that C call does not yield — it holds the single loop thread until the query returns, the fsync() completes, or a PRAGMA wal_checkpoint finishes copying frames back into the main database. During those milliseconds nothing else runs: no timers fire, no sockets are read, no other coroutine makes progress. A single slow query or a checkpoint stall therefore does not slow one request, it freezes the whole process. This is the exact failure that aiosqlite is built to remove, and it belongs to the Async Execution Patterns topic within the wider WAL Optimization & Concurrency Tuning guide. aiosqlite does not make SQLite asynchronous — nothing can, because the engine is synchronous by design. It runs an ordinary blocking sqlite3 connection on one dedicated worker thread and lets your coroutines await the results, so the loop thread stays free while the query runs elsewhere.

Figure — A direct sqlite3 call parks the event loop thread for the full query and fsync; routing the same call through aiosqlite hands it to a worker thread and returns an awaitable, so other coroutines keep running.
Direct sqlite3 call versus aiosqlite worker offload Two paths from the asyncio event loop. On the blocking path a coroutine calls sqlite3 directly, and the loop thread stalls for the whole query and fsync while every other coroutine waits. On the aiosqlite path the coroutine awaits a call that is handed to a single dedicated worker thread which owns the SQLite connection in WAL mode; the loop thread stays free to run other coroutines and receives the result when the worker finishes. Event loop one thread direct call sqlite3.execute() loop STALLS · query + fsync no coroutine runs await worker thread owns the connection loop stays free result SQLite WAL mode

Diagnosis

The symptom is a process that looks idle yet misses deadlines: heartbeat pings arrive late, WebSocket frames bunch up, timers that should fire every 100ms drift to 400ms — and it correlates with database activity, not CPU load. Confirm it is loop blocking rather than genuine overload with three measurements.

First, turn on asyncio’s own detector. The loop already times each callback and warns when one runs too long; lower the threshold so a blocking query cannot hide:

import asyncio, logging
logging.basicConfig(level=logging.WARNING)

loop = asyncio.get_event_loop()
loop.slow_callback_duration = 0.05   # warn on any callback holding the loop >50ms
loop.set_debug(True)                 # required for the slow-callback warning to fire

With debug mode on, every synchronous conn.execute that runs longer than 50ms prints Executing <Handle ...> took 0.183 seconds. A direct sqlite3 call on a slow query or a checkpoint lands here immediately; a properly offloaded await db.execute(...) never does, because the loop callback only schedules the work and returns.

Second, measure the loop lag directly — the gap between when a timer was due and when it actually ran. Any positive lag is time the loop spent blocked:

import asyncio, time

async def loop_lag_probe(interval=0.1):
    while True:
        t0 = time.monotonic()
        await asyncio.sleep(interval)            # should wake after exactly `interval`
        drift = (time.monotonic() - t0) - interval
        if drift > 0.02:                         # >20ms late == the loop was blocked
            print(f"event loop blocked for {drift*1000:.0f}ms")

Run that probe as a background task while the application serves traffic. If the drift spikes line up with query or checkpoint times, the loop is being held by synchronous SQLite work.

Third, find the offending call. A blocking sqlite3 call in a coroutine has a synchronous stack — no await between the coroutine frame and the C call. Grep for sqlite3.connect and bare .execute( inside async def functions; every one of them is a loop-blocking call that should go through an awaited connection instead.

Solution

Open the connection through aiosqlite so the underlying sqlite3 handle lives on a dedicated worker thread, apply the same PRAGMA baseline used everywhere else in this codebase during connection setup, and await every statement. The loop thread only ever schedules and collects; the blocking work happens off-loop.

import asyncio
import aiosqlite

# PRAGMAs that must be applied on the worker thread, once per connection.
_BASELINE = (
    ("journal_mode", "WAL",    "readers never block the single writer; enables snapshots"),
    ("synchronous",  "NORMAL", "fsync at checkpoint not per-commit; crash-safe under WAL"),
    ("busy_timeout", "5000",   "worker sleeps up to 5s on a lock instead of raising SQLITE_BUSY"),
    ("foreign_keys", "ON",     "per-connection, off by default — enforce referential integrity"),
)

async def open_db(path: str) -> aiosqlite.Connection:
    db = await aiosqlite.connect(path)                    # starts the worker thread that owns sqlite3
    for name, value, _reason in _BASELINE:
        await db.execute(f"PRAGMA {name}={value};")       # runs on the worker, not the loop thread
    await db.commit()                                     # persist journal_mode change to the header
    return db

async def latest_reading(db: aiosqlite.Connection, sensor_id: int):
    async with db.execute(                                # await never parks the loop thread
        "SELECT value, ts FROM readings WHERE sensor=? ORDER BY ts DESC LIMIT 1;",
        (sensor_id,),
    ) as cur:
        return await cur.fetchone()

aiosqlite.connect spins up one thread and constructs an ordinary sqlite3.Connection on it; every await db.execute(...) marshals the call onto that thread and resumes your coroutine with the result. Because the PRAGMAs are issued through the same awaited path, they execute on the worker thread that actually owns the connection — applying them anywhere else would set them on the wrong handle. Note that busy_timeout=5000 is still required: aiosqlite removes loop blocking, not lock contention, so the worker can still meet a competing writer and must be allowed to back off.

Verification

Prove the PRAGMAs took effect inside the async connection, not on some transient handle, by reading each one back through the same awaited interface and asserting the value:

import asyncio, sqlite3, aiosqlite

async def verify(path: str):
    db = await open_db(path)
    try:
        async with db.execute("PRAGMA journal_mode;") as cur:
            mode = (await cur.fetchone())[0]
        assert mode.lower() == "wal", f"journal_mode is {mode!r}, expected wal"

        async with db.execute("PRAGMA busy_timeout;") as cur:
            assert (await cur.fetchone())[0] == 5000, "busy_timeout did not apply"

        async with db.execute("PRAGMA foreign_keys;") as cur:
            assert (await cur.fetchone())[0] == 1, "foreign_keys off — write bypassed setup"
    except sqlite3.Error as exc:                          # aiosqlite re-raises sqlite3 errors on await
        raise AssertionError(f"verification query failed: {exc}") from exc
    finally:
        await db.close()

asyncio.run(verify("app.db"))

The read-backs run on the worker thread and confirm the live connection carries WAL, the 5-second timeout, and foreign-key enforcement. Pair this with the loop-lag probe from the diagnosis section: with queries offloaded, the drift warnings should disappear entirely under the same workload that previously froze the loop.

Failure Modes & Gotchas

One aiosqlite connection is one thread, so it serializes — sharing it across tasks does not add concurrency. Each aiosqlite.Connection owns exactly one worker thread, and that thread executes awaited calls one at a time in submission order. Fanning many coroutines at a single shared connection does not parallelize their queries; it queues them behind each other on that one thread, and an interleaved transaction from a second coroutine can land between another coroutine’s BEGIN and COMMIT, corrupting transaction boundaries. Keep a write connection owned by one logical writer and open separate connections for independent readers — WAL lets those reader connections run concurrent snapshot reads. For CPU-bound or bulk write fan-out where you want an explicit dedicated writer with a queue, offloading writes to a thread executor gives you finer control than a bare aiosqlite handle.

A checkpoint still blocks the worker thread — it just no longer blocks the loop. aiosqlite moves the stall off the event loop, but the stall still exists: a PRAGMA wal_checkpoint(TRUNCATE) that copies a large WAL back into the main file holds the worker thread for its full duration, and every coroutine awaiting that same connection waits behind it. If your ingestion connection also runs checkpoints, a checkpoint burst will stall reads on that handle. Run checkpoints on a separate connection driven by a maintenance task, and configure the threshold deliberately rather than letting wal_autocheckpoint fire mid-burst.

aiosqlite removes loop blocking, not SQLITE_BUSY. Because the engine is unchanged, a second writer — another process, another connection, a migration script — can still take the write lock while your worker wants it, and the worker will raise SQLITE_BUSY once busy_timeout expires. Set busy_timeout on every connection as shown, and route all writes through a single writer so your own coroutines never contend. Dropping the timeout because “aiosqlite handles concurrency” reintroduces instant lock errors under the first concurrent writer.