SQLite3 multithreading cache inconsistency · Issue #118172 · python/cpython · GitHub
Skip to content

SQLite3 multithreading cache inconsistency #118172

Open
Open
SQLite3 multithreading cache inconsistency#118172
Labels
extension-modulesC modules in the Modules dirtopic-sqlite3type-bugAn unexpected behavior, bug, or error

Description

@adam444555
adam444555
opened on Apr 23, 2024

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:

import sqlite3
import threading

KB = sqlite3.connect(
    "example.db", check_same_thread=False
)

def execute_query():
    cursor = KB.cursor()
    cursor.execute("SELECT * FROM test_table")
    result = cursor.fetchall()
    assert result == [(1, 'test1'), (2, 'test2'), (3, 'test3')], str(result)
    return result

def run_threads():
    threads = []
    for i in range(50):
        thread = threading.Thread(target=execute_query)
        threads.append(thread)
        thread.start()

    for thread in threads:
        thread.join()

def test_multithreading():
    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()

Test output: (Different in each run because of inconsistent fetch results)

Exception in thread Thread-3 (execute_query):
Traceback (most recent call last):
  File "/usr/lib/python3.12/threading.py", line 1073, in _bootstrap_inner
    self.run()
  File "/usr/lib/python3.12/threading.py", line 1010, in run
Exception in thread Thread-7 (execute_query):
Traceback (most recent call last):
  File "/usr/lib/python3.12/threading.py", line 1073, in _bootstrap_inner
    self._target(*self._args, **self._kwargs)
  File "/home/user/tmp/kb_test.py", line 13, in execute_query
    self.run()
  File "/usr/lib/python3.12/threading.py", line 1010, in run
Exception in thread Thread-12 (execute_query):
Traceback (most recent call last):
  File "/usr/lib/python3.12/threading.py", line 1073, in _bootstrap_inner
    assert result == [(1, 'test1'), (2, 'test2'), (3, 'test3')], str(result)
    self._target(*self._args, **self._kwargs)
AssertionError: [(1, 'test2'), (3, 'test3')]
  File "/home/user/tmp/kb_test.py", line 13, in execute_query
Exception in thread Thread-1 (execute_query):
Traceback (most recent call last):
  File "/usr/lib/python3.12/threading.py", line 1073, in _bootstrap_inner
    self.run()
    assert result == [(1, 'test1'), (2, 'test2'), (3, 'test3')], str(result)
    self.run()
AssertionError: [(3, 'test3')]
  File "/usr/lib/python3.12/threading.py", line 1010, in run
  File "/usr/lib/python3.12/threading.py", line 1010, in run
    self._target(*self._args, **self._kwargs)
  File "/home/user/tmp/kb_test.py", line 13, in execute_query
    self._target(*self._args, **self._kwargs)
  File "/home/user/tmp/kb_test.py", line 13, in execute_query
    assert result == [(1, 'test1'), (2, 'test2'), (3, 'test3')], str(result)
    assert result == [(1, 'test1'), (2, 'test2'), (3, 'test3')], str(result)
AssertionError: [(None, None), (), (2, 'test2'), (), (), (2, 'test2'), (0, None), (1, 'test1'), (2, 'test2'), (3, 'test3')]
AssertionError: [(3, 'test3')]

CPython versions tested on:

3.12, 3.13

Operating systems tested on:

Linux

Activity

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Metadata

Metadata

Assignees

No one assigned

    Labels

    extension-modulesC modules in the Modules dirtopic-sqlite3type-bugAn unexpected behavior, bug, or error

    Projects

    Milestone

    No milestone

    Relationships

    None yet

    Development

    No branches or pull requests

    Issue actions