#!/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()