SQLite transaction not rolling back on constraint violation in a multi-threaded environment
After trying multiple solutions online, I still can't figure this out... I'm working on a personal project and I'm working with an scenario with SQLite transactions where a violation of a UNIQUE constraint is not rolling back the entire transaction in a multi-threaded environment. I've set up a simple application using Python's `sqlite3` module, and I'm trying to insert records into a table within a transaction. When two threads attempt to insert a record with the same key at the same time, I expect the entire transaction to roll back if one of them violates the constraint. However, it seems that only the thread that gets the constraint violation raises an exception, while the other thread's changes are committed. Here’s the code snippet I'm using: ```python import sqlite3 import threading def insert_record(conn, value): try: conn.execute("BEGIN TRANSACTION;") conn.execute("INSERT INTO my_table (key_column, value_column) VALUES (?, ?);", (value, 'Some Data')) conn.commit() except sqlite3.IntegrityError as e: print(f'IntegrityError: {e}') conn.rollback() finally: conn.close() # Example usage conn1 = sqlite3.connect('my_database.db') conn2 = sqlite3.connect('my_database.db') thread1 = threading.Thread(target=insert_record, args=(conn1, 1)) thread2 = threading.Thread(target=insert_record, args=(conn2, 1)) thread1.start() thread2.start() thread1.join() thread2.join() ``` The table is defined as follows: ```sql CREATE TABLE my_table ( key_column INTEGER PRIMARY KEY, value_column TEXT NOT NULL ); ``` After running this, if both threads try to insert the same `key_column`, I expect one of them to raise an `IntegrityError` and rollback the transaction, but it seems that the other thread's changes are still committed even after the behavior in the first thread. I checked the isolation level and it's set to the default, but it seems like the rollback isn't affecting the other connection. How can I ensure that the entire transaction is rolled back in both threads if one of them fails due to a constraint violation?