demonstrates pitfalls of sqlite write locking and how to fix it

lock.py

# A demonstration of the SQLite `database is locked` error, and its fix.
# For more detail see
#
# to see the error, run:
# `python lock.py`
#
# and to see the fix, run:
# `python lock.py correct`

import logging
import sqlite3
import sys
import threading
import time


def insert_and_sleep(name):
    logging.info("connection %s: thread started", name)

    with sqlite3.connect("locktest.db") as conn:
        cur = conn.cursor()

        # begin a read transaction
        cur.execute("begin;")
        logging.info("connection %s: begin", name)

        # still a read transaction
        cur.execute("select * from people")
        logging.info("connection %s: select", name)

        # this `insert` upgrades the transaction to a write transaction
        cur.execute("insert into people (id, name, address) values (1, 2, 3)")
        logging.info("connection %s: inserted, sleeping", name)

        # sleep with the transaction still open!
        time.sleep(5)
        logging.info("connection %s: sleeping over, committing", name)

        # committing ends the write transaction
        cur.execute("commit;")


def try_to_insert(name, should_begin_immediate):
    logging.info("connection %s: thread started", name)

    with sqlite3.connect("locktest.db") as conn:
        cur = conn.cursor()

        # same busy_timeout reset as above
        if should_begin_immediate:
            cur.execute("PRAGMA busy_timeout=5000;")
            logging.info("connection %s: busy_timeout=5000", name)
        else:
            cur.execute("PRAGMA busy_timeout=0;")
            logging.info("connection %s: busy_timeout=0;", name)

        # start read transaction.
        # IF you change this to `begin immediate`,
        # the following select call will fail,
        # as begin immediate causes the db to try
        # to get a write transaction right away
        if should_begin_immediate:
            logging.info("connection %s: attempting begin immediate", name)
            cur.execute("begin immediate;")
            logging.info("connection %s: begin immediate complete", name)
        else:
            logging.info("connection %s: attempting begin", name)
            cur.execute("begin;")
            logging.info("connection %s: begin complete", name)

        # still read transaction,
        # database allows this even if it is already locked
        cur.execute("select * from people")
        logging.info("connection %s: select", name)

        # this call will fail, as we are attempting to upgrade a
        # read transaction to a write transaction *while the
        # database is already in another write transaction*
        logging.info("connection %s: trying to insert", name)
        cur.execute("insert into people (id, name, address) values (1, 2, 3)")

        cur.execute("commit;")
        logging.info("connection %s: insert", name)


if __name__ == "__main__":
    try:
        if sys.argv[1] == "correct":
            should_begin_immediate = True
        else:
            should_begin_immediate = False
    except IndexError:
        should_begin_immediate = False

    format = "%(asctime)s: %(message)s"
    logging.basicConfig(format=format, level=logging.INFO, datefmt="%H:%M:%S")

    with sqlite3.connect("locktest.db") as conn:
        cur = conn.cursor()
        cur.execute("create table if not exists people (id, name, address)")

    t1 = threading.Thread(target=insert_and_sleep, args=(1,))
    t1.start()
    t2 = threading.Thread(
        target=try_to_insert,
        args=(
            2,
            should_begin_immediate,
        ),
    )
    # wait 2 seconds before running t2,
    # ensuring that t1 is holding its write transaction
    time.sleep(0.1)
    t2.start()

    t1.join()
    t2.join()