Ruby: SQLite3::BusyException: database is locked:

问题:

Ran into this error message whilst developing tonight: SQLite3::BusyException: database is locked:

I have two models:

• Podcasts have many Tracks
• Tracks belong to Podcasts.
• Podcast files are hosted on mixcloud.

To create a Podcast:

• user submits a url for a podcast on mixcloud
• rails app grabs json feed associated with url
• json is used to set attributes (title, image etc) on the new Podcast object

I'm trying to get my rails app to take advantage of the fact that the json feed also details the names (and artists) of the Tracks that belong to this Podcast.

I thought the following before_validation method would automatically create all associated Tracks whenever we create a new Podcast.

class Podcast < ActiveRecord::Base
  attr_accessible :mixcloud_url, :lots, :of, :other, :attrs
  has_many :tracks    
  before_validation :create_tracks
  def create_tracks
    json = Hashie::Mash.new HTTParty.get(self.json_url)    
    json.sections.each do |section|
      if section.section_type=="track"
          Track.create(:name=>section.track.name, :podcast_id=>self.id)
      end
    end             
  end
end

How can I get round this? It looks like rails (or sqlite3) doesn't like me creating new instances of an associated model in this way. How else can I do this? I suspect this is as much a rails problem as an sqlite3 one. I can post more code if it's gonna help.

回答1:

For anyone else encountering this issue with SQLite locking in development when a Rails console is open, try this:

Just run the following:

ActiveRecord::Base.connection.execute("BEGIN TRANSACTION; END;")

For me anyway, it appears to clear any transaction that the console was holding onto and frees up the database.

This is especially a problem for me when running delayed_job, which seems to fail at closing the transaction quite often.

回答2:

For me...the issue I had was that it seemed that the Rails Console I had open for a while was locking up a connection with SQLite.

So once I exited that console, and restarted my webserver (Thin) it worked perfectly.

I tried @trisweb's suggestion but it didn't work for me.

回答3:

SQLite is not really supposed to be used for concurrent access which is the issue you are running into here. You can try increasing the timeout in your database.yml file which may be a workaround for you in this case. However, I would recommend you switch to another database that supports multiple connections like MySQL or PgSQL.

回答4:

I had the same "ActiveRecord::StatementInvalid: SQLite3::BusyException: database is locked: INSERT INTO "users" ("created_at", "email", "name", "password_digest", "updated_at") VALUES (?, ?, ?, ?, ?)" issue. I tried every way around found in Google and I failed. The problem was solved for me when I closed my SQLite Database Browser.

回答5:

Make sure you don't have 2 guards or several consoles running. If you want make sure desperately see the "No Name's" answer above.

You can also try increasing pool:

for example: change test section in your config/database.yml as below

test:
    adapter: sqlite3
    database: db/test.sqlite3
    pool: 50
    timeout: 5000

回答6:

Probably you have a Rails console open on another bash, if so you have to close it (ctrl+D).

回答7:

actually for me, I found killing rails help to fix this problem.

use "ps aux | grep rails" to find out ongoing rails process id. then use

"kill -9 [rails-pid]"

to kill processes.

Then it will work

回答8:

It is most likely not related to rails code. Using at the same time the console with the sandbox option (rails console --sandbox), makes the problem systematic with SQLite, since the console is basically waiting to quit to rollback everything.

The solution above from @trisweb will not work in this case, but quitting the console will.

回答9:

Yes this is an old question and there are many answers on here already. But none of them worked for me, meaning it took me a long time to finally figure out the problem. I found what worked and will share it in case it is what might be causing the problem for you too.

I was using the SQLITE Browser (its a GUI database browser). I will refer to it as "GUI" here (to prevent confusion with the word browser being your localhost::8000 chrome browser or whatever.
http://sqlitebrowser.org/

I was monitoring what was being written to the database and had the GUI open while my rails app ran in my chrome browser. I would refresh the GUI to see if it was adding the data as I expected it to.

As a matter of debugging I had decided to delete a row from the SQLite GUI so I could see if my app would react appropriately to the row being missing now.

As it turns out, the SQLite Browser does not actually delete the row (causing confusion on my end as to why my app was acting like the row was still there, even though it was visually missing on the GUI). Anyway after 30 minutes of frustration I closed the SQLite GUI and then got a notice that asked if i wanted to save any changes to the database that I made. I naively clicked "No" and closed the app.

Apparently what happens is that the GUI then locked the database because there were rows in my database that had been sort of "soft-deleted" without committing to the delete. So the GUI was (for lack of a better term) holding the database in Limbo.

This explains why a) my app wasnt acting like the row was missing, because it hadn't actually been deleted yet, and B) explains why the database locked up. It was still waiting for me to commit the deletes.

So to solve the problem, I simply opened up the GUI once again and deleted the same row and then closed the GUI and this time I clicked "Yes" when asking to save changes to the database. It saved the delete and unlocked the database and now my app works!

---

I hope this helps someone else that might be having the same issue but was using the SQLite Browser GUI interface. This might be what is locking your database.

回答10:

I had the same issue. For those with SQLite Database Browser. I did not need to close SQLite Database Browser. I only had to click the "Write Changes" button. It is highlighted and needs to not be highlighted.

回答11:

SQLite has troubles with concurrency. I changed sqlite on Postgresql and the issue is gone

回答12:

This happens when you make any changes manually directly into the SQlite DB Browser (like delete a row or change the value of any column) and forget to save those changes. Any changes made need to be saved (ctrl + s). If not saved, SQLite locks the Database until u save those changes.

I did the same and my issue got resolved!

回答13:

my trouble is: I opened a database management program named "DB Browser for SQlite". Closed this database management program, and problem solved.

回答14:

I was using DB Browser for SQLite and rails console simultaneously. Closing the DB Browser for SQLite fixed the issue for me.

回答15:

try restarting the server or closing any running rails console, worked for me