Simultaneous writing with sqlite3

Question

I am using the python sqlite3 module to write the results of batch jobs to a shared .db file. I chose SQLite because several processes may be trying to write at the same time, and as I understand it, SQLite should do this well. I'm not sure what happens when several processes end and try to write at the same time. Therefore, if several processes that look like

 conn = connect('test.db') with conn: for v in xrange(10): tup = (str(v), v) conn.execute("insert into sometable values (?,?)", tup) 

execute immediately, will they throw an exception? Wait politely for other processes to write? Is there a better way to do this?

Answer 1

The sqlite library locks the database for each process when writing to the database, and each process will wait for the lock to be released to get its turn.

The database does not need to be written until it is committed. You use the connection as a context manager (good!), So the commit happens after the loop finishes and all insert statements are executed.

If your database has uniqueness constraints, it may be that the commit is not running because one process has already added rows that another process conflicts with.

Answer 2

If each process has its own connection, this should be good. What happens is that when the process is recorded, the database will be locked, so that all other processes will be blocked. They will throw an exception if the timeout waits until the database is released. The timeout can be configured through a connection call:

http://docs.python.org/2/library/sqlite3.html#sqlite3.connect

It is not recommended that you have a DB file in a network share.

Update:

You can also check the isolation level: http://docs.python.org/2/library/sqlite3.html#sqlite3.Connection.isolation_level

Answer 3

The good news is that the SQLLite library implicitly uses a transaction that locks the database during DML. This means that other simultaneous calls to the database will wait for the completion of the DML request, having completed / canceled the transaction. Note, however, that several processes can execute SELECT at the same time.

Also, refer to Python SQL Lite 3.0 in section 11.13.6 - Transaction Management, how transactions can be managed.