sqlite callback functions can deadlock in Python subinterpreters

After quick search can't see that this problem as it affects sqlite is noted as existing issue so creating one so at least got one to point people at when they hit this problem when using mod_wsgi.

Main related issue around problems with simplified GIL state API and sub interpreters is:

• https://github.com/python/cpython/issues/59956

Bug report

In the sqlite module there are various points these days where one can supply a callback function, or handler class, which a callback is made to at some point from sqlite C code, where the GIL is not already held. In order to acquire the GIL the simplified GIL state API functions are used. These functions though are well known to fail when used in the context of a Python sub interpreter as the TLS state related to them only works for the main interpreter context. The result is that use of sqlite in a Python sub interpreter can result in deadlocks when these callbacks are invoked. As mod_wsgi is a big user of Python sub interpreters, it can easily be affected by the problem.

Examples of some (possibly not all) of these callback function points in sqlite are:

• https://docs.python.org/3/library/sqlite3.html#sqlite3.Connection.create_function
• https://docs.python.org/3/library/sqlite3.html#sqlite3.Connection.create_aggregate
• https://docs.python.org/3/library/sqlite3.html#sqlite3.Connection.create_window_function
• https://docs.python.org/3/library/sqlite3.html#sqlite3.Connection.set_authorizer

Example of problematic code is found in https://github.com/python/cpython/blob/main/Modules/_sqlite/connection.c#L824-L851.

static void
func_callback(sqlite3_context *context, int argc, sqlite3_value **argv)
{
    PyGILState_STATE threadstate = PyGILState_Ensure();

    PyObject* args;
    PyObject* py_retval = NULL;
    int ok;

    args = _pysqlite_build_py_params(context, argc, argv);
    if (args) {
        callback_context *ctx = (callback_context *)sqlite3_user_data(context);
        assert(ctx != NULL);
        py_retval = PyObject_CallObject(ctx->callable, args);
        Py_DECREF(args);
    }

    ok = 0;
    if (py_retval) {
        ok = _pysqlite_set_result(context, py_retval) == 0;
        Py_DECREF(py_retval);
    }
    if (!ok) {
        set_sqlite_error(context, "user-defined function raised exception");
    }

    PyGILState_Release(threadstate);
}

The PyGILState_Ensure() function can deadlock when the current thread was operating against a Python sub interpreter and not the main interpreter context.

Basically any use of PyGILState_Ensure() in connection.c of sqlite module could be problematic.

Workaround for users of mod_wsgi is to force the use of the main Python interpreter context using:

WSGIApplicationGroup %{GLOBAL}

If mod_wsgi users are hosting multiple WSGI applications, they should ensure that they are using a separate daemon process group for each so that when forcing WSGI application to use the main Python interpreter context they don't conflict as that will then only occur in their respective processes.

Tagging @ericsnowcurrently as last person still working on related issues GIL state issues.

Begin with Modules/_sqlite/connection.c, especially lines 824-851 and the callback entry points named in the report; read issue #59956 for the subinterpreter and GIL-state context. Determine how all callback paths behave in subinterpreters and verify that sqlite callbacks no longer deadlock, including create_function, create_aggregate, create_window_function, and set_authorizer.