There is a race condition in the sqlite3 module that is misleadingly raised as sqlite3.InterfaceError('Error binding parameter 0 - probably unsupported type.')

There are two issues here, one is the incorrectly raise error (https://bugs.python.org/issue16379), the other is the underlying SQLITE_MISUSE caused by the race condition. I believe this is a race between sqlite3 vs python garbage collection due to the releasing of the GIL. If the GIL releasing is removed from sqlite3, I cannot reproduce the race.

Here is a script to reproduce the error, as the number of runners or queries_per_executemany is increased the error happens more frequently. The minimal test case only needs two executemanys where each has two queries to run.





import asyncio
import sqlite3
from concurrent.futures.thread import ThreadPoolExecutor


async def misuse_sqlite(runners=2, queries_per_executemany=2):
    loop = asyncio.get_event_loop()

    expected_error = 'Error binding parameter 0 - probably unsupported type.'
    exceptions = []
    executor = ThreadPoolExecutor(1)
    db = executor.submit(sqlite3.connect, ':memory:', isolation_level=None).result()
    executor.submit(db.executescript, "create table test (id text primary key);")

    def query_in_executor():
        return asyncio.wrap_future(executor.submit(executemany))

    def executemany():
        try:
            return db.executemany("select * from test where id=?", ((str(i),) for i in range(queries_per_executemany)))
        except Exception as err:
            exceptions.append(err)

    for _ in range(runners):
        loop.call_soon(query_in_executor)
    await asyncio.sleep(0.01)

    assert all(str(err) == expected_error and isinstance(err, sqlite3.InterfaceError) for err in exceptions)

    executor.submit(db.close).result()
    executor.shutdown()

    return len(exceptions) > 0


attempts = 0

while True:
    attempts += 1
    if asyncio.run(misuse_sqlite()):
        print('error hit on attempt', attempts)
        break

I've been trying to provoke this on my Mac (macOS 11.4) with Python 3.8 though 3.11 (dev) without success. Are you still able to reproduce this, Jack?

If you can attach the output of python3.10 -m test.pythoninfo, I could try to set up a similar environment to reproduce the error.