Open adam444555 opened 5 months ago
cc @erlend-aasland
cc @erlend-aasland
Thanks Donghee! (BTW, I'm already subscribed to the topic-sqlite3 label, so no need to ping me manually 🙂)
is this correct use of the api though, setting check_same_thread=False
and then accessing the same connection from multiple threads simultaneously? The issue goes away with the following change:
LK = threading.Lock()
def execute_query():
cursor = KB.cursor()
with LK:
cursor.execute("SELECT * FROM test_table")
result = cursor.fetchall()
assert result == [(1, 'test1'), (2, 'test2'), (3, 'test3')], str(result)
return result
is this correct use of the api though, setting
check_same_thread=False
and then accessing the same connection from multiple threads simultaneously? The issue goes away with the following change:LK = threading.Lock() def execute_query(): cursor = KB.cursor() with LK: cursor.execute("SELECT * FROM test_table") result = cursor.fetchall() assert result == [(1, 'test1'), (2, 'test2'), (3, 'test3')], str(result) return result
I am not sure. But based on the description from the document, for serialized threading mode, "Threads may share the module, connections and cursors", and no lock needed for the old version (3.11 and older), I assume lock is not necessary.
I can confirm that the issue appears in Python 3.12 and Python 3.13, but not in earlier versions (I tested back to Python 3.8).
I also tried to revert the sqlite3 extension code back to its 3.11 state (making only a few needed C API changes), just to make sure the issue exists in the sqlite3 extension and not in the runtime. I can confirm the issue is in the sqlite3 extension. I'll do a bisect as soon as possible.
I bisected this back to f5c85aa3eea1adf0c61089583e2251282a316ec1, which seems plausible. I'll also try to come up with a smaller repro so we can add a regression test for this.
However, I'm not completely convinced this is the actual offending commit; If I check out f5c85aa3eea1adf0c61089583e2251282a316ec1~
, I get intermittent segfaults[^1] for about 5% of the runs.
[^1]: bad memory access in _pysqlite_query_execute
Bug report
Bug description:
When using SQLite3 in multi-threading application, the fetch reuslts are not consistent. After some testing, this seems to be caused by the caching. The SQLite threading mode is serialized. This issue only exists in python 3.12 and 3.13. No issue in 3.11. Currently solution is to set
cached_statements=0
when connect to the database.A simple demo to reproduce the error:
Test output: (Different in each run because of inconsistent fetch results)
CPython versions tested on:
3.12, 3.13
Operating systems tested on:
Linux