Page MenuHomeSoftware Heritage

"Save code now" may fail with "OperationalError: database is locked"
Open, NormalPublic

Description

SQLite only has some limited support for concurrency, and it looks like it's not enough for us, as it caused an error while @moranegg was doing a demo yesterday: https://sentry.softwareheritage.org/share/issue/7b8cc0d9f4134051979b9b8cb166605c/

My guess is, she clicked "Save code now" right when an other transaction was being committed to the DB.

Event Timeline

vlorentz triaged this task as Normal priority.Dec 15 2020, 2:16 PM
vlorentz created this task.

I guess it is time to migrate the webapp database from SQLite to PostgreSQL.

Also the Save code now requests table is constantly growing and now contains more than 50000 lines, making the select requests quite slow.

I will check locally if the migration is straightforward or not.

Also the Save code now requests table is constantly growing and now contains more than 50000 lines, making the select requests quite slow.

Are you sure that's an SQLite issue? Looking at the models declared in swh/web/common/models.py, there doesn't seem to be any index at all.

Thanks for the indexes ! Regarding the OperationalError, maybe we could mitigate it with a retry process as in the storage ?

Other solution, increase timeout value for waiting on the database lock.

This really feels like the wrong solution to the problem... postgres is the right one

Also the Save code now requests table is constantly growing and now contains more than 50000 lines, making the select requests quite slow.

Turns out the slow requests was not due to the database but rather an implementation flaw while retrieving a save requests page. The fix is short but I realized that code is not covered so more work to do before submitting a diff.

Also the Save code now requests table is constantly growing and now contains more than 50000 lines, making the select requests quite slow.

Turns out the slow requests was not due to the database but rather an implementation flaw while retrieving a save requests page. The fix is short but I realized that code is not covered so more work to do before submitting a diff.

--> D4816

This really feels like the wrong solution to the problem... postgres is the right one

I have created T2945 on the subject.