SQLite 'database is locked' as soon as two people use my app

Solved · 432 views · asked last month by ruby_t · edited

Flask app on Replit for our climbing gym's route log. SQLite file. With just me it's fine. When two staff members save at the same moment one gets:

sqlite3.OperationalError: database is locked

Do I need to move to Postgres already? We have 6 staff.

What I’ve tried

Replit AI suggested adding check_same_thread=False to connect(). Didn't change anything.

Comment

Are you using one global connection object, or one per request? fatima_z last month · edited

no idea honestly, Replit wrote it. there is a conn = sqlite3.connect(...) at the top of app.py ruby_t last month · edited

3 answers

Marked as helpful by the asker

fatima_zlast month · edited

No, 6 staff is nothing for SQLite. Three settings fix 95% of these:

conn = sqlite3.connect('routes.db', timeout=10)
conn.execute('PRAGMA journal_mode=WAL')
conn.execute('PRAGMA busy_timeout=5000')

WAL mode lets readers keep reading while one writer writes. It's stored in the file, so setting it once is enough.
busy_timeout makes a writer wait up to 5 seconds for the lock instead of failing immediately.

And check your code for a connection that opens a transaction and never commits, e.g. a global connection where a failed request left a write open. Open a connection per request and close it (Flask's g + teardown_appcontext).

check_same_thread=False is unrelated and slightly dangerous when you share one connection across threads, I'd remove it.

Comment

There was a global conn at the top of app.py. Moved to per request + WAL. No more errors this week ruby_t last month · edited

+1 to all of this. The global connection is the real bug in most of these reports. olu_backend last month · edited

olu_backendlast month · edited

The per-request pattern Fatima mentions, for reference:

from flask import g

def get_db():
    if 'db' not in g:
        g.db = sqlite3.connect('routes.db', timeout=10)
        g.db.execute('PRAGMA busy_timeout=5000')
    return g.db

@app.teardown_appcontext
def close_db(exc):
    db = g.pop('db', None)
    if db is not None:
        db.close()

Comment

ahmed_elsayedlast month · edited

had the same on replit, one more thing: make sure the db file isn't inside a folder that gets synced or restored on deploy, I lost data that way

Comment

Keyboard shortcuts

SQLite 'database is locked' as soon as two people use my app

What I’ve tried

3 answers

Your answer

SQLite 'database is locked' as soon as two people use my app

What I’ve tried

3 answers

Related

Your answer