CODEWITHSUNDEEP
djangodatabasesqliteconcurrency
nikhil.doshi
nikhil.doshi

Reputation: 35

'Database is locked' error while using sqlite3 during atomic transactions

I had previously observed that sqlite db writes are significantly faster when I wrap around an atomic transaction (django) - 0.3 secs without and 0.004 secs with transaction. Hence, I applied transactions throughout my project. Strangely after doing that I started encountering 'database is locked' error which led me to debug it to find out that when an update is running over a transaction (lets call it update A) and when I try to concurrently run another update (B) over a transaction then it fails instantly without waiting for the timeout (5 secs default). But when I tried running update B without transaction, it waited for A to finish and then completed the update. Could anyone provide me with an possible explanation for this which doesn't include removing transactions.

Upvotes: 2

Views: 1189

Answers (2)

python481516
python481516

Reputation: 84

This happens because of these two conditions:

  • by default transaction.atomic() starts a DEFERRED transaction so no lock is acquired at first
  • you are reading inside the transaction first and then trying to write while another process already has a write lock on the database.

For example:

# no lock is acquired here because it executes BEGIN query which
# defaults to BEGIN DEFERRED
with transaction.atomic():

    # this acquires read lock on DB
    MyModelName.objects.all().first()

    # this tries to change read lock to write lock
    # but fails because another process is holding a write lock
    MyModelName.objects.create(name='Example')

    # "OperationalError: database is locked" is raised here
    # immediately ignoring the timeout

I am not entirely sure why this happens but I found another post saying that it could be due to a deadlock:

sqlite3 ignores sqlite3_busy_timeout?

So your options are:

  • Make sure that the write lock is acquired first inside the transaction (i.e. you don't have any read queries before the first write query). You can do this by taking out the read query outside the transaction if possible.
  • Monkey-patch and force transaction.atomic() to acquire the write lock immediately as described by btimby here:

Fix SQLite "database is locked" problems using "BEGIN IMMEDIATE"

Upvotes: 2

CL.
CL.

Reputation: 180280

SQLite's timeout can be set with PRAGMA busy_timeout.

The default value is zero, and this settings applies only to the connection (not to the database), so it looks as if not all connections got those 5 seconds.

Ensure that all connections have a proper timeout set by executing that PRAGMA. (And five seconds is dangerous; better use thirty seconds.)

Upvotes: 0

Related Questions