Code that unintuitively generates a "database is locked" error with the Python sqlite module

unintuitive_sqlite_behavior.py

#!/usr/bin/env python3

# This code demonstrates a code sequence that will cause the Python sqlite
# to immediately generate a "database is locked" exception, even with a large
# timeout

import sqlite3
import time
import threading

DB_FILE = "foo.db"

# There are multiple ways to fix this bug
# set any of the following constants to True to fix
FIX_0 = False
FIX_1 = False
FIX_2 = False

conn = sqlite3.connect(DB_FILE, timeout=60)
cursor = conn.cursor()

# Init table and add some sample values
cursor.execute("create table if not exists t (k int primary key, v int)")
with conn:
    cursor.execute("insert or ignore into t (k, v) values (1, 1)")

def fn():
    # NB: we use a large timeout but it does *nothing(
    conn = sqlite3.connect(DB_FILE, timeout=60)
    with conn:
        cursor = conn.cursor()
        cursor2 = conn.cursor()
        if FIX_0:
            # This line fixes the code because an DML (like update)
            # causes the sqlite module to start a transaction via "BEGIN"
            # thus causing the following select statement to be included in
            # this transaction
            cursor2.execute("update t set v = 3 where k = ?", (2,))
        # This statement will cause SQLite to acquire a SHARED lock
        cursor.execute("select k from t")
        if FIX_1:
            # If FIX_0 was not enabled (and we aren't currently in a transaction)
            # then this will release the SHARED lock on the database by completely
            # stepping the select statement, thus allowing the following write
            # transaction to acquire a lock
            cursor.fetchall()
        if FIX_2:
            # This is similar to FIX_1 except it explicitly ends the select statement
            cursor.close()
        # If none of the above fixes were enabled, this line will inevitably throw a
        # "database is locked" exception in one or more of the threads that executes
        # this code
        cursor2.execute("update t set v = 3 where k = ?", (2,))


threads = []
for _ in range(10):
    threads.append(threading.Thread(target=fn))
    threads[-1].start()

for thr in threads:
    thr.join()

Although this is old, i believe it is a good example. I would like to add that if using a global thread lock. this issue could be avoided. Here's a small rewrite with a thread lock demonstrating the solution.

import time
import threading

lock = threading.Lock()

DB_FILE = "foo.db"

conn = sqlite3.connect(DB_FILE, timeout=60)
cursor = conn.cursor()

cursor.execute("create table if not exists t (k int primary key, v int)")
with conn:
    cursor.execute("insert or ignore into t (k, v) values (1, 1)")

def im_a_thread_of_the_main_proccess():
    try:
        lock.acquire(True)
        conn = sqlite3.connect(DB_FILE, timeout=60)
        with conn:
            cursor = conn.cursor()
            cursor2 = conn.cursor()
            cursor.execute("select k from t")
            cursor2.execute("update t set v = 3 where k = ?", (2,))
    finally:
        lock.release()

threads = []
for _ in range(10):
    threads.append(threading.Thread(target=fn))
    threads[-1].start()

for thr in threads:
    thr.join()

Whats happening is that the first thread that was ran is literally stopping other threads from finishing their job until it is done. It acquires a thread lock. Other threads will try to acquire the lock too but they wont be able to. Those threads will block until the lock is released. So they are almost lining up in a queue waiting. When a thread is finished it will release the lock and next thread waiting will stop blocking and continue. I believe this solution is best for multiple writers as its practically impossible for multiple things to write at the same time in sqlite3. This is not necessary if there are readers only. WAL journaling which can be enabled with conn.execute("PRAGMA journal_mode = WAL") easily allows for multiple readers with a single line.