SQLAlchemy 和 SQLite:数据库被锁定 [英] SQLAlchemy and SQLite: database is locked

问题描述

我有一个使用最新 sqlalchemy 的 python 脚本.当我使用 sqlite 时，只有 sqlite，其他数据库运行良好，出现以下错误:

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....

有什么提示吗?

我的代码示例(简化)，我有几种这样的方法来选择、更新和删除事物:

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()

推荐答案

SQLite 会在写入数据库时​​锁定数据库，例如发送 UPDATE、INSERT 或 DELETE 时.使用 ORM 时，这些会在刷新时发送.数据库将保持锁定状态，直到发生 COMMIT 或 ROLLBACK.

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.

我经常在多线程情况下看到数据库被锁定"错误.一个线程将锁定数据库，另一个线程将尝试写入自己的数据.如果第一个线程在超时期限内(默认情况下为 4-5 秒，如果我记得的话)没有释放锁，则在第二个线程上引发 OperationalError.

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.

知道何时刷新可能很棘手，因此当会话具有 autoflush=True(默认设置)时对数据库进行写入，因为 any查询将导致刷新.有时，打开 SQL 日志记录有助于澄清事情发生的时间:

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)

这里有一些相关文档:http://docs.sqlalchemy.org/en/rel_0_9/dialects/sqlite.html#database-locking-behavior-concurrency

这篇关于SQLAlchemy 和 SQLite:数据库被锁定的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！