Reputation: 3535

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

Upvotes: 21

Answers (14)

Albert

Reputation: 1

My Antivirus thought that flask was a threat and locked the database. I solved it by clicking exclude when the Antivirus prompt appears. This unlocks the database.

Upvotes: 0

Joseph Tam

Reputation: 1

If you have any database explorer like DB Browser for SQLite holding a lock on the db file, close any connection to the db there or just exit the app all together. Only one app can have the SQLite file open (connected) at a given time to maintain ACID

Upvotes: 0

Shadi

Reputation: 10355

For future comers, in my case, the pandas to_sql function (with a sqlalchemy connection string) to write to a sqlite file on a CIFS shared mount was failing. The same function would work on an ext4 disk. The fix was to add nobrl to the options in /etc/fstab, unmounting, then re-mounting the disk. Then the to_sql command worked.

Upvotes: 0

Heinrich G

Reputation: 41

I had the same error, which I could fix by:

db.session.close_all()

For me, the error occured due to too many open db connections, since I used the Spyder console instead of the terminal

Upvotes: 3

kingdowliu

Reputation: 11

This might happen if there are duplicate records in the table

Upvotes: -4

Ali

Reputation: 1

just use StaticPool. and also follow this answer (scoped_session): https://stackoverflow.com/a/9621251

create_engine(DB_PATH, echo=False, poolclass=StaticPool, connect_args={'check_same_thread': False})

Upvotes: 0

ccc77

Reputation: 21

also met the same problem:

  File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/engine/default.py", line 590, in do_execute
    cursor.execute(statement, parameters)
sqlalchemy.exc.OperationalError: (sqlite3.OperationalError) database is locked
[SQL: DELETE FROM vminds4 WHERE vminds4.id = ?]
[parameters: (2,)]
(Background on this error at: http://sqlalche.me/e/e3q8)

Finally, get here, bacause I just open another terminal open the sqlite3 files and after I closed the window, it works!

Upvotes: 2

Yashwant Kumar

Reputation: 425

My answer is only for those who are experimenting with the flask database and they are ready to delete their database for removing the database lock. If you are experimenting, then surely you can add data to tables again by running a python script.

Here we go....

delete the data.sqlite file present in your flask project
delete the migrations folder present in your flask project
Now run following commands to create a new database:
- flask db init
- flask db migrate -m "tables"
- flask db upgrade
Now you can run your python script to add data to the database or you can manually add data to tables using python promt/flask shell.

Upvotes: -3

Bogdan Korecki

Reputation: 87

In my case with quite a simple logic and no multithreading the source of the issue appeared to be quite banal...

'SQLite is not designed for a high level of write concurrency. The database itself, being a file, is locked completely during write operations within transactions, meaning exactly one “connection” (in reality a file handle) has exclusive access to the database during this period - all other “connections” will be blocked during this time.'

... so that 'enlightened' an idea: disconnect DB Browser which I used to check the db during the work. And it worked well. So if that is possibly your case - check if you are not connected to your sqlite via other tool ;)

Upvotes: 5

Bino

Reputation: 904

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

Upvotes: 11

Eric Smith

Reputation: 2899

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

Upvotes: 37

estin

Reputation: 3121

Check your code for these points:

Instance of MyDb must be one for all application lifetime. MyDb must be a singleton.
Try using 'plain' strategy for engine but not pool_threadlocal=True
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()

Upvotes: 3

andrew cooke

Reputation: 46872

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

Upvotes: 4

Y.H Wong

Reputation: 7244

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

Upvotes: 7

SQLAlchemy and SQLite: database is locked

Answers (14)

Related Questions