Concurrent writing with sqlite3

Question

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?

not sure if this is a dup: I'm asking about the behavior of a specific python module, not SQLite in general. — Shep, Aug 13 '13 at 12:11

score 29 · Accepted Answer · answered Aug 13 '13 at 10:57

29

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.

answered Aug 13 '13 at 10:57

Martijn Pieters

1,048,767
296
4,058
3,343

1

so is the lock enabled at `connect` or when the commit happens? – Shep Aug 13 '13 at 11:01
1

The lock is grabbed when writing. – Martijn Pieters Aug 13 '13 at 11:01
1

@MartijnPieters ... and the lock is released when returning from the commit function call. Or am I wrong? – Sylvain Leroux Aug 13 '13 at 11:07
1

You are correct; once the commit has completed, the lock is released as the database no longer needs to be written to. – Martijn Pieters Aug 13 '13 at 11:10
4

See http://www.sqlite.org/lockingv3.html for the nitty gritty details on how locking works. – Martijn Pieters Aug 13 '13 at 11:10
Thanks for explaining, could you update this and refer to some documentation which backs your claims. While I'd like to trust the green checkmark on a StackOverflow page I'd trust documentation from Python or Sqlite even more ;-) – eric.frederich Jul 17 '17 at 19:30
@eric.frederich: see https://www.sqlite.org/lockingv3.html. I do mention that link in the comment right above yours. – Martijn Pieters Jul 17 '17 at 19:38

score 6 · Answer 2 · answered Aug 13 '13 at 10:57

If each process holds it's own connection than it should be fine. What will happen is that when writing the process will lock the DB, so all other process will block. They will throw an exception if the timeout to wait for the DB to be free is exceeded. The timeout can be configured through the connect call:

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

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

Update:

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

score 1 · Answer 3 · answered Aug 13 '13 at 11:27

The good news is that SQLLite library implicitly uses a transaction that locks a database whenever executing a DML. This means that other concurrent accesses to the database will wait till the executing DML request completes by commiting/rolling back a transaction. Note however that multiple processes can perform SELECT at the same time.

Also, please refer to the Python SQL Lite 3.0 module under section 11.13.6 - Controlling Transactions that details how transactions can be controlled.

Concurrent writing with sqlite3

3 Answers3

Linked