Thread Safety and check_same_thread

The flag you'll bump into immediately

By default, a Python sqlite3.Connection can only be used from the thread that created it. Use it from a different thread and you get ProgrammingError: SQLite objects created in a thread can only be used in that same thread.

Three ways to handle multi-threaded apps:

One connection per thread — typically via threading.local(). Simple and safe.
Connection pool — if your app has many short-lived threads. Tools like SQLAlchemy do this for you.
check_same_thread=False + your own lock — disable the safety check and serialize access yourself. Easy to mess up; only do this if you know exactly what you're doing.

Self-reference: Pippa's backend uses aiosqlite (covered in track 7) instead of threads, which sidesteps this entirely — async tasks run on one thread by definition. For sync threaded code, the per-thread connection pattern is the boring, correct default.

Code

Per-thread connection pattern·python

import sqlite3, threading

_local = threading.local()

def conn() -> sqlite3.Connection:
    if not hasattr(_local, 'c'):
        _local.c = sqlite3.connect('demo.db', timeout=30.0)
        _local.c.execute('PRAGMA journal_mode = WAL')
        _local.c.execute('PRAGMA foreign_keys = ON')
    return _local.c

def worker():
    rows = conn().execute('SELECT count(*) FROM users').fetchone()
    print(threading.current_thread().name, rows)

threads = [threading.Thread(target=worker) for _ in range(8)]
for t in threads: t.start()
for t in threads: t.join()

Exercise

Write a small program that spawns 16 threads, each running a mix of reads and writes against a SQLite database. Implement the per-thread connection pattern. Confirm WAL mode lets the readers and writers proceed without blocking each other. Then deliberately break it by sharing one connection across threads and observe the ProgrammingError.

Thread Safety and check_same_thread

The flag you'll bump into immediately

Code

External links

Exercise

Progress

Comments 0