Menu
Running a rails site right now using SQLite3.
About once every 500 requests or so, I get a
ActiveRecord::StatementInvalid (SQLite3::BusyException: database is locked:...
What's the way to fix this that would be minimally invasive to my code?
I'm using SQLLite at the moment because you can store the DB in source control which makes backing up natural and you can push changes out very quickly. However, it's obviously not really set up for concurrent access. I'll migrate over to MySQL tomorrow morning.
755
SQLite is a software library that implements a self-contained, serverless, zero-configuration, transactional SQL database engine. The sqlite3 has no synonyms but sqlite has sqlitedatabase as a solitary synonym. Normally version tags are used for questions about features specific for that version.
There should be a database file with sqlite extension on db directory of the app. On DB Browser, select Open Database option and choose that file and you should be able to view the data. Show activity on this post. Restart your server.
The amount of web traffic that SQLite can handle depends on how heavily the website uses its database. Generally speaking, any site that gets fewer than 100K hits/day should work fine with SQLite. The 100K hits/day figure is a conservative estimate, not a hard upper bound.
First open the SQLite database from File > Open Database… Now select your SQLite database file and click on Open. Your database should be opened. Now you can click on File > Export and then select either Database to SQL file… or Table(s) as CSV file… or Table(s) to JSON… to export the database to your desired format.
You mentioned that this is a Rails site. Rails allows you to set the SQLite retry timeout in your database.yml config file:
production: adapter: sqlite3 database: db/mysite_prod.sqlite3 timeout: 10000 The timeout value is specified in miliseconds. Increasing it to 10 or 15 seconds should decrease the number of BusyExceptions you see in your log.
This is just a temporary solution, though. If your site needs true concurrency then you will have to migrate to another db engine.
141
By default, sqlite returns immediatly with a blocked, busy error if the database is busy and locked. You can ask for it to wait and keep trying for a while before giving up. This usually fixes the problem, unless you do have 1000s of threads accessing your db, when I agree sqlite would be inappropriate.
// set SQLite to wait and retry for up to 100ms if database locked sqlite3_busy_timeout( db, 100 );
31
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
