Concurrent writing with sqlite3
concurrency, python, sqlite
Solution
The `sqlite` library will lock the database per process when writing to the database and each process will wait for the lock to be released to get their turn.
The database doesn't need to be written to until commit time however. You are using the connection as a context manager (good!) so the commit takes place after your loop has completed and all `insert` statements have been executed.
If your database has uniqueness constraints in place, it may be that the commit fails because one process has already added rows that another process conflicts with.
Problem
I'm using the `sqlite3` python module to write the results from batch jobs to a common `.db` file. I chose SQLite because multiple processes may try to write at the same time, and as I understand it SQLite should handel this well. What I'm unsure of is what happens when multiple processes finish and try to write at the same time. So if several processes that look like this ``` conn = connect('test.db') with conn: for v in xrange(10): tup = (str(v), v) conn.execute("insert into sometable values (?,?)", tup) ``` execute at once, will they throw an exception? Wait politely for the other processes to write? Is there some better way to do this?