SQLite3 threadsafety property should return 2 for serialized

[rghe]

Bug report

Bug description:

If the underlying SQLite library sqlite3_threadsafe() function returns 1 (i.e. SERIALIZED) the sqlite3.threadsafety property returns 3, meaning that cursor can be shared between threads. The sqlite3 module cannot really share cursors between threads, though. Sometimes it ends up in check_cursor_locked(), more often it segfaults. Given the dubious value of sharing cursors between threads, introducing the locks needed to support level 3 seems unnecessary. Until #118172 is fixed, sqlite3.threadsafety should return 1 (share module only).

Reproducible with a test program similar to the one in #118172

import sqlite3
import threading

numthreads=10
KB = None
threadcursor = None
print(f"sqlite3.threadsafety: {sqlite3.threadsafety}")

def execute_query():
    threadcursor.execute("SELECT * FROM test_table")
    result = threadcursor.fetchall()
    assert result == [(1, 'test1'), (2, 'test2'), (3, 'test3')], str(result)
    return result

def run_threads():
    global threadcursor
    threadcursor = KB.cursor()
    threads = []
    for i in range(numthreads + 1):
        thread = threading.Thread(target=execute_query)
        threads.append(thread)
        thread.start()

    for thread in threads:
        thread.join()

def test_multithreading():
    global KB

    KB = sqlite3.connect(
        "example.db", check_same_thread=False
    )
    cursor = KB.cursor()

    cursor.execute(
        """CREATE TABLE IF NOT EXISTS test_table (id INTEGER PRIMARY KEY, value TEXT)"""
    )
    KB.commit()

    cursor.execute("""DELETE FROM test_table""")
    KB.commit()

    cursor.execute("""INSERT INTO test_table (value) VALUES ('test1')""")
    cursor.execute("""INSERT INTO test_table (value) VALUES ('test2')""")
    cursor.execute("""INSERT INTO test_table (value) VALUES ('test3')""")
    KB.commit()

    run_threads()
    KB.close()

if __name__ == "__main__":
    test_multithreading()

CPython versions tested on:

CPython main branch

Operating systems tested on:

Linux

[erlend-aasland]

Thanks for the report. I'll try to take a look at this within the coming week. (Ping me if not.)