sqlite and "database is locked" error
dave
I am using SQLAlchemy in a multi-threaded app with a SQLite backend.
I've been careful to scope sessions down to a single method and made
sure a session is never used by more than one thread.
Some threads are reading data from the DB, some are writing it, some
are updating it. As could have been expected, those commit() calls
committing updates or inserts sometimes error out with
"OperationalError: (OperationalError) database is locked" errors.
I can only imagine another thread is in the middle of reading from or
writing to the DB and in possession of a shared or exclusive lock.
It appears SQLAlchemy won't wait for the lock to become available nor
will it retry a failed operation - is that correct? Is there a
recommended pattern or method for handling multiple simultaneous reads
and writes? Given that some of the data from the DB is being lazy
loaded as instances are being used, how would I use mutexes to control
access to the DB (if that's even what I should be doing)?
I'd be happy to provide more information if necessary. Thanks,
-dave
Michael Bayer
On Oct 22, 2008, at 9:47 PM, dave wrote:
>
> Hi there,
>
> I am using SQLAlchemy in a multi-threaded app with a SQLite backend.
> I've been careful to scope sessions down to a single method and made
> sure a session is never used by more than one thread.
>
> Some threads are reading data from the DB, some are writing it, some
> are updating it. As could have been expected, those commit() calls
> committing updates or inserts sometimes error out with
> "OperationalError: (OperationalError) database is locked" errors.
>
> I can only imagine another thread is in the middle of reading from or
> writing to the DB and in possession of a shared or exclusive lock.
>
> It appears SQLAlchemy won't wait for the lock to become available nor
> will it retry a failed operation - is that correct?
usually, the transactional capabilities of the database handle
contention of resources. SQLite is designed specifically to suck at
this. retrying failed operations is also outside of the scope of
SQLA since "failure" is an incredibly broad category for which
recovery scenarios vary wildly, if even available.
> Is there a
> recommended pattern or method for handling multiple simultaneous reads
> and writes? Given that some of the data from the DB is being lazy
> loaded as instances are being used, how would I use mutexes to control
> access to the DB (if that's even what I should be doing)?
you could use mutexes, or alternatively lockfiles, since you're
ultimately dealing with a file based resource. most people that
start having contention issues with SQLite just switch to PG or MySQL.
Here's some "multiple reader/single writer" synchronizers of each type
(see FileSynchronizer, ConditionSynchronizer) used by Beaker to
synchronize access to dbm and flat files:
https://www.knowledgetap.com/hg/beaker/file/7621e71729b3/beaker/synchronization.py
if you have success with those in this context, let me know.