【python下用sqlite3, 多线程下报错，原因和解决 】

在python下用sqlite3, 多线程 在UPDATE 或者INSERT的时候, 会报错

sqlite3.OperationalError: cannot commit - no transaction is active

1. 原因

多线程写冲突

• 非原子写操作：如果多个线程同时执行非原子写操作，可能会导致数据覆盖或不一致。

2. 解决方案

使用锁

使用锁来确保同一时间只有一个线程可以执行写操作。

import sqlite3
import threadinglock = threading.Lock()def worker(conn, thread_id):with lock:cursor = conn.cursor()cursor.execute("INSERT INTO test (id, value) VALUES (?, ?)", (thread_id, f"value_{thread_id}"))conn.commit()cursor.close()def main():conn = sqlite3.connect('example.db', check_same_thread=False)cursor = conn.cursor()cursor.execute("CREATE TABLE IF NOT EXISTS test (id INTEGER PRIMARY KEY, value TEXT)")conn.commit()cursor.close()threads = []for i in range(5):thread = threading.Thread(target=worker, args=(conn, i))threads.append(thread)thread.start()for thread in threads:thread.join()conn.close()if __name__ == "__main__":main()

使用事务

显式事务管理：在每个线程中显式地开始和提交事务，确保事务的原子性和一致性。

import sqlite3
import threadingdef worker(conn, thread_id):cursor = conn.cursor()cursor.execute("BEGIN")cursor.execute("INSERT INTO test (id, value) VALUES (?, ?)", (thread_id, f"value_{thread_id}"))conn.commit()cursor.close()def main():conn = sqlite3.connect('example.db', check_same_thread=False)cursor = conn.cursor()cursor.execute("CREATE TABLE IF NOT EXISTS test (id INTEGER PRIMARY KEY, value TEXT)")conn.commit()cursor.close()threads = []for i in range(5):thread = threading.Thread(target=worker, args=(conn, i))threads.append(thread)thread.start()for thread in threads:thread.join()conn.close()if __name__ == "__main__":main()