SQLite: How to Handle Query Timeout Errors When Using Transactions with Bulk Inserts
I'm migrating some code and I'm stuck on something that should probably be simple... I'm currently working with SQLite 3.36.0 and working with timeout errors when trying to perform bulk inserts within a transaction. I'm using the following code snippet to insert a large number of records into a table: ```sql BEGIN TRANSACTION; INSERT INTO my_table (column1, column2) VALUES (1, 'A'); INSERT INTO my_table (column1, column2) VALUES (2, 'B'); -- More insert statements... COMMIT; ``` However, sometimes I receive the behavior message: `database is locked` after a few hundred inserts, which causes the entire transaction to unexpected result. To mitigate this, I have attempted to set a longer busy timeout using: ```sql PRAGMA busy_timeout = 30000; -- 30 seconds ``` Despite this, the behavior continues, especially when the number of inserts increases significantly. I’ve also tried using a batch insert approach, but the locking scenario remains. I’m looking for best practices or configuration settings to improve the performance during bulk inserts without running into locking issues. Are there any specific patterns or configurations in SQLite that could help resolve this question? Has anyone else encountered this? This is for a service running on macOS. I'm working with Sql in a Docker container on macOS. Could someone point me to the right documentation?