SQLite transaction rollback optimization guide as expected in a multi-threaded environment
I'm working with an scenario where my SQLite transactions are not rolling back as I would expect when using multiple threads. I'm using SQLite version 3.36.0 and the `sqlite3` library for Python. My application is structured such that I have a worker thread that performs database operations while the main thread handles user interactions. Here's a simplified version of my code: ```python import sqlite3 import threading def db_worker(): conn = sqlite3.connect('my_database.db') cursor = conn.cursor() try: cursor.execute("BEGIN TRANSACTION;") cursor.execute("INSERT INTO my_table (data) VALUES ('test data')") raise Exception('Simulated behavior') # Simulating an behavior cursor.execute("COMMIT;") except Exception as e: print(f'behavior: {e}') conn.rollback() # Attempting to rollback finally: conn.close() thread = threading.Thread(target=db_worker) thread.start() thread.join() ``` When the simulated behavior occurs, I expect that the transaction should be rolled back, but I notice that the insert still seems to continue in the database. I confirmed this by querying `my_table` after the thread has completed execution. I have also tried using `PRAGMA journal_mode=WAL;`, but that doesn't seem to resolve the scenario. My current understanding is that SQLite allows concurrent reads but not concurrent writes, so Iām unsure how the rollback is failing in this context. Is there a specific way to manage transactions in a multi-threaded environment with SQLite? Any insights would be appreciated.