SQLite3 threadsafety property should return 2 for serialized

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

Linked PRs

• gh-124316

レポートで説明されている sqlite3.threadsafety プロパティと sqlite3_threadsafe() のマッピングから始め、次に提供されたマルチスレッドの再現プログラムを実行します。リンクされている PR gh-124316 とそのテストを確認します。報告された threadsafety 値とカーソル共有の動作が合意された解決策と一致し、実証された失敗が発生しなければ完了です。