Bug report

Bug description:

A sqlite3 connection shared between threads (check_same_thread=False, SQLite in serialized mode, sqlite3.threadsafety == 3) deadlocks the whole process if it has a Python callback registered (function, aggregate, collation, authorizer, progress handler or trace callback). SQLite invokes the callback from sqlite3_step() with the connection mutex held, and the callback then waits for the GIL in PyGILState_Ensure(); meanwhile another thread holds the GIL and blocks on the same connection mutex in an SQLite call the module makes without releasing the GIL, for example sqlite3_column_int64() in _pysqlite_fetch_one_row(), sqlite3_bind_*(), sqlite3_errmsg() or sqlite3_set_authorizer().

import sqlite3, threading

cx = sqlite3.connect(":memory:", check_same_thread=False)
cx.create_function("f", 1, lambda x: x)
cx.execute("create table t(x)")
cx.executemany("insert into t values(?)", [(i,) for i in range(1000)])

def work(n):
    # different SQL in each thread, so no cached statement is shared
    for i in range(100):
        cx.execute(f"select f(x) from t limit {n + i}").fetchall()

threads = [threading.Thread(target=work, args=(n,)) for n in (10000, 20000)]
for t in threads:
    t.start()
for t in threads:
    t.join()  # never returns

The script should finish; instead it hangs within a second and cannot be interrupted with Ctrl-C. gh-86864 was the same lock inversion in Connection.close(), fixed by releasing the GIL around sqlite3_close_v2().

CPython versions tested on:

CPython main branch

Operating systems tested on:

Linux

Linked PRs

• gh-161065: Fix sqlite3 deadlock on connections shared between threads #161066