SQLAlchemy and SQLite: database is locked

Question

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

score 35 · Answer 1 · answered Feb 26 '15 at 16:36

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

score 10 · Answer 2 · edited May 05 '19 at 11:17

10

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.

edited May 05 '19 at 11:17

recnac

3,744
6
24
46

answered Apr 04 '19 at 02:44

Bino

744
15
22

score 6 · Answer 3 · answered Dec 15 '12 at 19:07

6

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

answered Dec 15 '12 at 19:07

Y.H Wong

7,151
3
33
35

andrew cooke · Answer 4 · 2013-02-06T01:49:43.290

4

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

edited Feb 06 '13 at 01:49

answered Feb 05 '13 at 22:39

andrew cooke

45,717
10
93
143

score 4 · Answer 5 · answered Jul 24 '19 at 22:15

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

score 3 · Answer 6 · edited Dec 08 '14 at 16:10

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

score 2 · Answer 7 · answered May 11 '20 at 09:09

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!

score 2 · Answer 8 · answered Jul 21 '21 at 09:34

2

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

answered Jul 21 '21 at 09:34

Heinrich G

31
5

score 0 · Answer 9 · answered Jul 25 '20 at 13:10

0

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

answered Jul 25 '20 at 13:10

Ali

1

score 0 · Answer 10 · answered Nov 19 '21 at 14:40

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.

Albert · Answer 11 · 2022-07-12T19:42:36.380

0

In my case, my Antivirus thought that flask was a threat and locked the database. I solved it by excluding that flask process from the security check.

edited Jul 12 '22 at 19:42

answered Jul 11 '22 at 16:40

Albert

1
1

As it’s currently written, your answer is unclear. Please [edit] to add additional details that will help others understand how this addresses the question asked. You can find more information on how to write good answers [in the help center](/help/how-to-answer). – Community Jul 12 '22 at 06:11

score 0 · Answer 12 · answered Jan 10 '23 at 10:24

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

score -2 · Answer 13 · answered Nov 22 '19 at 11:07

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.

score -4 · Answer 14 · edited Nov 26 '20 at 09:53

-4

This might happen if there are duplicate records in the table

edited Nov 26 '20 at 09:53

Bhargav Rao

50,140
28
121
140

answered Nov 26 '20 at 09:44

kingdowliu

11

SQLAlchemy and SQLite: database is locked

14 Answers14

Linked