SQLite3::BusyException

ruby-on-rails ruby database sqlite

27,076

Solution 1

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 );

Solution 2

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.

Solution 3

All of these things are true, but it doesn't answer the question, which is likely: why does my Rails app occasionally raise a SQLite3::BusyException in production?

@Shalmanese: what is the production hosting environment like? Is it on a shared host? Is the directory that contains the sqlite database on an NFS share? (Likely, on a shared host).

This problem likely has to do with the phenomena of file locking w/ NFS shares and SQLite's lack of concurrency.

Solution 4

If you have this issue but increasing the timeout does not change anything, you might have another concurrency issue with transactions, here is it in summary:

Begin a transaction (aquires a SHARED lock)
Read some data from DB (we are still using the SHARED lock)
Meanwhile, another process starts a transaction and write data (acquiring the RESERVED lock).
Then you try to write, you are now trying to request the RESERVED lock
SQLite raises the SQLITE_BUSY exception immediately (indenpendently of your timeout) because your previous reads may no longer be accurate by the time it can get the RESERVED lock.

One way to fix this is to patch the active_record sqlite adapter to aquire a RESERVED lock directly at the begining of the transaction by padding the :immediate option to the driver. This will decrease performance a bit, but at least all your transactions will honor your timeout and occurs one after the other. Here is how to do this using prepend (Ruby 2.0+) put this in a initializer:

module SqliteTransactionFix
  def begin_db_transaction
    log('begin immediate transaction', nil) { @connection.transaction(:immediate) }
  end
end

module ActiveRecord
  module ConnectionAdapters
    class SQLiteAdapter < AbstractAdapter
      prepend SqliteTransactionFix
    end
  end
end

Solution 5

bundle exec rake db:reset

It worked for me it will reset and show the pending migration.

View more solutions

27,076

Author by

Shalmanese

Updated on January 06, 2021

Comments

Shalmanese over 3 years

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.
ThatHybrid about 14 years

Where do you put the sqlite3_busy_timeout?
ravenspoint about 14 years

Placement is not critical. Somewhere after opening the database and before doing the request that is blocked. For convenience, I place it immediatly after opening the database.
docwhat over 13 years

This will call the sqlite3_busy_timeout on the database connection for you.
docwhat over 13 years

Modify the database.yml config file as mentioned by Rifkin Habsburg, instead.
ybakos almost 13 years

Thanks Ignacio. For AR 3.0.9, just note that the method is a little different but you still change transaction() to transaction(:immediate). I wonder why this isn't explicitly in the AR codebase?
Kris over 12 years

The right tool for the job. Example one: SQLite is great for testing (it can be run in memory so is fast). Example Two: A CMS/Blog website, they are usually low volume and with caching the database hardly gets hit at all.
c2h2 over 11 years

u think the person ask this question dont understand the diff of SQLite vs MySQL
Anno2001 over 11 years

this is not a direct answer to your question, but since we often end up here as a result of stackoverflows search I answered this here
Kyle Carlson about 8 years

Just make sure to restart your Rails app after making this change. Didn't work until I did. :)
Roman Gaufman over 2 years

It seems to work in some cases, if the database is busy and I open another console and try to write, it will hang for the period of time. However if I open 2 consoles and have both write in a loop, one of them will die immediately without respecting the timeout. Any ideas?
Hackeron over 2 years

This is still an issue in 2021 :( - do you have an up to date example that works in Rails6 please?
Adrien Jarthon over 2 years

@Hackeron I don't unfortunately, I haven't used sqlite much since then.
Hackeron about 2 years

We gave up trying to make this work and switched to MariaDB