SQLite Concurrency
Recently, I wrote an application that used a SQLite database and wrote data into a common database rapidly, over several connections.
I initially wrote the application to use one thread, and only one database connection, but as I improved the software, I converted it to use multiple threads with one connection per thread.
I quickly ran into the problem though that the database quickly became locked – one thread would block out the other and would not return the lock. It wasn’t consistent though as to which thread would be blocked – it was usually the second thread, but not consistently.
Having read a post from the pysqlite mailing list, I think it explains the problem a little bit more accurately, and kind of exaplins a solution. Although, it does seem a little bit drastic (essentially, reconnect everytime you need to run a concurrent query).
I’m now very interested to find out whether this will cure my problem. It does seem a bit drastic, and quite IO intensive, but it’s worth a shot!
I’ll let you know my mileage….
