SQLAlchemy and SQLite: database is locked

问题:

I have a python script which uses the latest sqlalchemy. When i use sqlite,only sqlite, other db works well, i get the following error:

sqlalchemy.exc.OperationalError: (OperationalError) database is locked u'SELECT blabla....

Any hint?

Example from my code (simplified), i have several methods like this, to select, update and delete things:

class MyDb(object):
    def __init__(self):
        engine = create_engine("sqlite:///file", poolclass=NullPool, pool_threadlocal=True)
        engine.pool_size=1
        engine.pool_timeout = 60
        self.sess = sessionmaker(bind=engine)

    def del_stuff(self):
        sess = self.sess()
        sess.query(Stuff).delete()
        try:
            sess.commit()
        except:
            sess.rollback()

    def set_stuff(self, id, bar):
        sess = self.sess()
        sess.query(Foo).get(id).bar = bar
        try:
            sess.commit()
        except:
            sess.rollback()

回答1:

SQLite locks the database when a write is made to it, such as when an UPDATE, INSERT or DELETE is sent. When using the ORM, these get sent on flush. The database will remain locked until there is a COMMIT or ROLLBACK.

I've mostly seen the "database is locked" error in multi-threading situations. One thread will lock the database and another thread will attempt a write of its own. If the first thread doesn't release the lock within the timeout period (4-5 seconds by default, if I recall) the OperationalError is raised on the second thread.

It can be tricky to know when a flush, and therefore a write is made to the database when the session has autoflush=True (the default setting) since any query will cause a flush. Sometimes turning on the SQL logging can help clarify when things are happening:

logging.getLogger('sqlalchemy.engine').setLevel(logging.INFO)

There is some relevant documentation here: http://docs.sqlalchemy.org/en/rel_0_9/dialects/sqlite.html#database-locking-behavior-concurrency

回答2:

you should use a single session across all objects in a thread. sqlite really doesn't like multiple connections, and sqlalchemy is effectively a connection per session (it looks like you may have a session for each class, which implies multiple sessions in a single thread).

回答3:

sqlite databases only allow one process to access it at a time. Perhaps you have a separate process using the database?

回答4:

Check your code for these points:

1. Instance of MyDb must be one for all application lifetime. MyDb must be a singleton.
2. Try using 'plain' strategy for engine but not pool_threadlocal=True
3. Close session on each logical request is done.

For example:

def set_stuff(self, id, bar):
    sess = self.sess()
    sess.query(Foo).get(id).bar = bar
    try:
        sess.commit()
    except:
        sess.rollback()
    finally:
        sess.close()

回答5:

Check any commits pending in database through any developer tools.

As everyone told above sqlite databases only allow one process to access it at a time. In my case I am using DB browser for sqllite and in the same i didn't commit a query. That's also lock the DB and will not allow the application to write to database.