How to lock a sqlite3 database in Python?

Question

Is there a way to explicitly acquire a lock on a sqlite3 database in Python?

Solution 1

The way to explicitly lock the database is start a transaction as explained in the documentation:

When a database is accessed by multiple connections, and one of the processes modifies the database, the SQLite database is locked until that transaction is committed.

One way to initiate a transaction is use the connection as a context manager:

import sqlite3
con = sqlite3.connect(...)
...
with con:
    # Database is locked here

Also note that some transactions happen implictly by default:

By default, the sqlite3 module opens transactions implicitly before a Data Modification Language (DML) statement (i.e. INSERT/UPDATE/DELETE/REPLACE), and commits transactions implicitly before a non-DML, non-query statement (i. e. anything other than SELECT or the aforementioned).

Solution 2

From the sqlite FAQ, "Can multiple applications or multiple instances of the same application access a single database file at the same time?":

Multiple processes can have the same database open at the same time. Multiple processes can be doing a SELECT at the same time. But only one process can be making changes to the database at any moment in time, however.

Whether or not you use the with connection construct, many processes can read from by only one can write to the database at any given time.

Solution 3

We can use multiprocessing commands to lock the DB write and read process. I am using the following commands and it's working fine:

from multiprocessing import Lock 
l.Lock()
l.acquire()
# Read/Write DB 
l.release()

Comments

[Stephen Gross]

Just to be clear: If I use the 'with con' trick, am I guaranteed that all read & write actions with the database within that block take place together? (That is, another thread can't alter something I'm reading).

[Stephen Gross]

I posted a followup question at stackoverflow.com/questions/9070369/…

[ghchoi]

It seems like... Even without with, python locks the sqlite db (file) against future connections until con.close(). I recommend to time.sleep(10) before con.close() and SELECT twice.

[M Katz]

A corollary of this answer is that the accepted answer is not correct. Using a transaction is a way of making multiple database operations atomic -- that is, they either all succeed or they all fail and leave the database unchanged. Locking means preventing collisions when two threads/processes are operating on the database at once (whether by a transaction or a single exec call). It's safe for multiple threads/processes to read from a database at the same time, but if a thread is making changes to a database (whether as a transaction or as a single exec call), only that thread can be running.

[M Katz]

Furthermore, when it says "only one process can be making changes to the database at any moment," you don't have to use locks to guarantee that. It's already guaranteed by SQLite.