sqlite3.OperationalError: database is locked

[oldani]

From time to time when the package is using sqlite, it locks the db and by then any endpoint is accesible. Here the traceback.

Traceback (most recent call last):
  File "/home/lwserver/env/lib/python3.4/site-packages/flask/app.py", line 1836, in __call__
    return self.wsgi_app(environ, start_response)
  File "/home/lwserver/env/lib/python3.4/site-packages/flask/app.py", line 1820, in wsgi_app
    response = self.make_response(self.handle_exception(e))
  File "/home/lwserver/env/lib/python3.4/site-packages/flask/app.py", line 1403, in handle_exception
    reraise(exc_type, exc_value, tb)
  File "/home/lwserver/env/lib/python3.4/site-packages/flask/_compat.py", line 33, in reraise
    raise value
  File "/home/lwserver/env/lib/python3.4/site-packages/flask/app.py", line 1817, in wsgi_app
    response = self.full_dispatch_request()
  File "/home/lwserver/env/lib/python3.4/site-packages/flask/app.py", line 1477, in full_dispatch_request
    rv = self.handle_user_exception(e)
  File "/home/lwserver/env/lib/python3.4/site-packages/flask/app.py", line 1381, in handle_user_exception
    reraise(exc_type, exc_value, tb)
  File "/home/lwserver/env/lib/python3.4/site-packages/flask/_compat.py", line 33, in reraise
    raise value
  File "/home/lwserver/env/lib/python3.4/site-packages/flask/app.py", line 1475, in full_dispatch_request
    rv = self.dispatch_request()
  File "/home/lwserver/env/lib/python3.4/site-packages/flask/app.py", line 1461, in dispatch_request
    return self.view_functions[rule.endpoint](**req.view_args)
  File "/home/lwserver/env/lib/python3.4/site-packages/flask_profiler/flask_profiler.py", line 137, in wrapper
    return wrapped(*args, **kwargs)
  File "/home/lwserver/env/lib/python3.4/site-packages/flask_profiler/flask_profiler.py", line 116, in wrapper
    collection.insert(measurement.__json__())
  File "/home/lwserver/env/lib/python3.4/site-packages/flask_profiler/storage/sqlite.py", line 128, in insert
    name))
sqlite3.OperationalError: database is locked

[oldani]

@muatik I will try to replicate and fix it and submit a PR, i need this fix asap

[teleyinex]

We've had this issue as well here.

[muatik]

@teleyinex Can you switch to using sqlalchemy configurations? https://github.com/muatik/flask-profiler#sqlachemy

[teleyinex]

That doesn't work either. Interestingly I get an SSL EOF error from psycopg2. Our solution has been to use MongoDB for now. I guess the main issue, is that flask_profile issues too many writes, and sqlite locks itself. Maybe changing the sampling could fix this?

[muatik]

yes, you can reduce the sampling ratio via configuration too. but it will not be a permanent solution anyway.

[teleyinex]

Yep. I don't know, the issue is that we're using them in production, and that's why we cannot debug it unless we write some load testing for the dev server. With sqlite I can imagine the issue is that, while with SQLAlchemy I'm not sure. We'll keep an eye on it in any case ;-)

[aardvark82]

Same issue here. How do you recover?

[muatik]

@aardvark82 you can use another db vendor via SQLAlchemy integration. check out README.md page for more information.

[amarvin]

Same issue here. SQLAlchemy docs seem to say that SQLite3 database locking could be even worse when using SQLAlchemy rather than using the native SQLite3 connector: https://docs.sqlalchemy.org/en/14/dialects/sqlite.html#database-locking-behavior-concurrency.