Database lock causes error 500 and exception

Issue #439 wontfix
gomox.ar created an issue

Hello,

I am using Rhodecode under SQLite and it seems that whenever two concurrent operations are happening one gets stuck and ends up timing out after quite a while (10+ seconds) with an error 500 along the lines of:

{{{ WebApp Error: <class 'sqlalchemy.exc.OperationalError'>: (OperationalError) database is locked u'UPDATE cache_invalidation SET cache_active=? WHERE cache_invalidation.cache_id = ?' (1, 9) }}}

I understand this is because of how SQLite locks work, but since I can't really see a way to migrate out to MySQL (is this supported somehow? I googled for generic Pylons/SQLAlchemy tools with no luck) I would like to see it fail more gracefully.

¿Is there anything I can do to improve the situation? The server is not under a lot of load, 10 devs using Mercurial and a CI server polling every few minutes at most.

Comments (5)

  1. Marcin Kuzminski repo owner

    This is sad limitation of sqlite. You should really switch to real database like Postgres or Mysql. The only tool i know for migrating a database to different engine is https://github.com/ricardochimal/taps.

    If you can maybe try to lower the CI puling intgerval and try setting (untested) sqlalchemy.db1.timeout = 1000

  2. gomox.ar reporter

    Hey Marcin, thanks for the reply.

    If I were to delete the SQLite database and switch to a MySQL one, what do I have to setup if I copy the repos? Users, permissions or am I missing anything else?

  3. gomox.ar reporter

    Migration sort of works with this script I previously used for migrating Redmine.

    http://www.redmine.org/boards/2/topics/12793

    There are a few caveats:

    • There is a column in RhodeCode called "read" which is a reserved word and isn't quoted in the exported SQL script (just add backticks to fix it)
    • I got an error afterwards about cache invalidation (duplicated key in the cache_invalidation table)
    • I ended up redoing the system configuration using MySQL and just using the insert statements from the tables mentioned above.

    Problem indeed seems to go away when replacing SQLite with MySQL. Thanks!

  4. Log in to comment