SQLite3::BusyException

Question

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.

Answer 1

I believe this happens when a transaction times out. You really should be using a "real" database. Something like Drizzle, or MySQL. Any reason why you prefer SQLite over the two prior options?

Answer 2

Source: this link

- Open the database
db = sqlite3.open("filename")

-- Ten attempts are made to proceed, if the database is locked
function my_busy_handler(attempts_made)
  if attempts_made < 10 then
    return true
  else
    return false
  end
end

-- Set the new busy handler
db:set_busy_handler(my_busy_handler)

-- Use the database
db:exec(...)

Answer 3

What table is being accessed when the lock is encountered?

Do you have long-running transactions?

Can you figure out which requests were still being processed when the lock was encountered?

Answer 4

Argh - the bane of my existence over the last week. Sqlite3 locks the db file when any process writes to the database. IE any UPDATE/INSERT type query (also select count(*) for some reason). However, it handles multiple reads just fine.

So, I finally got frustrated enough to write my own thread locking code around the database calls. By ensuring that the application can only have one thread writing to the database at any point, I was able to scale to 1000's of threads.

And yea, its slow as hell. But its also fast enough and correct, which is a nice property to have.

Answer 5

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

Answer 6

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.

Answer 7

Sqlite can allow other processes to wait until the current one finished.

I use this line to connect when I know I may have multiple processes trying to access the Sqlite DB:

conn = sqlite3.connect('filename', isolation_level = 'exclusive')

According to the Python Sqlite Documentation:

You can control which kind of BEGIN statements pysqlite implicitly executes (or none at all) via the isolation_level parameter to the connect() call, or via the isolation_level property of connections.