Ruby：SQLite3::BusyException：数据库被锁定：

Ruby: SQLite3::BusyException: database is locked:

提问by stephenmurdoch

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

今晚开发时遇到此错误消息： SQLite3::BusyException: database is locked:

I have two models:

我有两个模型：

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

• 播客有很多曲目
• 曲目属于播客。
• 播客文件托管在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

• 用户在 mixcloud 上提交播客的 URL
• rails 应用程序抓取与 url 关联的 json 提要
• json 用于在新的 Podcast 对象上设置属性（标题、图像等）

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.

我正在尝试让我的 rails 应用程序利用这样一个事实，即 json 提要还详细说明了属于此 Podcast 的曲目的名称（和艺术家）。

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

我认为每当我们创建一个新的 Podcast 时，下面的 before_validation 方法会自动创建所有关联的轨道。

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.

我怎样才能解决这个问题？看起来rails（或sqlite3）不喜欢我以这种方式创建关联模型的新实例。我还能怎么做？我怀疑这和 sqlite3 一样是一个 rails 问题。如果有帮助，我可以发布更多代码。

回答by trisweb

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

对于在 Rails 控制台打开时在开发中遇到 SQLite 锁定问题的其他人，请尝试以下操作：

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.

这对我来说在运行 delay_job 时尤其成问题，它似乎经常无法关闭事务。

回答by Devin M

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.ymlfile 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.

SQLite 不应该真正用于并发访问，这是您在这里遇到的问题。您可以尝试增加database.yml文件中的超时时间，在这种情况下这可能是您的解决方法。但是，我建议您切换到另一个支持多个连接的数据库，如 MySQL 或 PgSQL。

回答by marcamillion

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.

对我来说……我遇到的问题是，我打开了一段时间的 Rails 控制台似乎锁定了与 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.

我尝试了@trisweb 的建议，但对我不起作用。

回答by Tarique

I had the same

我有同样的

ActiveRecord::StatementInvalid: SQLite3::BusyException: database is locked: INSERT INTO "users" ("created_at", "email", "name", "password_digest", "updated_at") VALUES (?, ?, ?, ?, ?)"

ActiveRecord::StatementInvalid: SQLite3::BusyException: 数据库被锁定: 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.

问题。我尝试了在 Google 中找到的所有方法，但都失败了。当我关闭 SQLite 数据库浏览器时，问题就解决了。

回答by Luke

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.

确保您没有运行 2 个守卫或多个控制台。如果您想确保拼命地看到上面的“无名”答案。

You can also try increasing pool:

您还可以尝试增加池：

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

例如：更改 config/database.yml 中的 test 部分，如下所示

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

回答by ark1980

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

可能您在另一个 bash 上打开了一个 Rails 控制台，如果是这样，您必须关闭它（ctrl+D）。

回答by hofffman

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

我的麻烦是：我打开了一个名为“DB Browser for SQlite”的数据库管理程序。关闭此数据库管理程序，问题解决。

回答by Antoine Lizée

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.

它很可能与 rails 代码无关。同时使用带有沙箱选项 ( rails console --sandbox)的控制台，使 SQLite 的问题系统化，因为控制台基本上等待退出以回滚所有内容。

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

在这种情况下，@trisweb 的上述解决方案将不起作用，但退出控制台将起作用。

回答by BufBills

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

实际上对我来说，我发现杀死 rails 有助于解决这个问题。

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

用于"ps aux | grep rails"找出正在进行的 Rails 进程 ID。然后使用

"kill -9 [rails-pid]"

to kill processes.

杀死进程。

Then it will work

然后它会工作

回答by jacurtis

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/

我使用的是 SQLITE 浏览器（它是一个 GUI 数据库浏览器）。我将在此处将其称为“GUI”（以防止将浏览器一词与您的 localhost::8000 chrome 浏览器或其他内容混淆
。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.

我正在监视写入数据库的内容，并在我的 Rails 应用程序在 Chrome 浏览器中运行时打开 GUI。我会刷新 GUI 以查看它是否按照我的预期添加数据。

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.

作为调试的问题，我决定从 SQLite GUI 中删除一行，以便我可以查看我的应用程序是否会对现在丢失的行做出适当的反应。

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.

事实证明，SQLite 浏览器实际上并没有删除该行（这让我很困惑，为什么我的应用程序表现得好像该行仍然存在，即使它在 GUI 上在视觉上丢失了）。无论如何，经过 30 分钟的挫折后，我关闭了 SQLite GUI，然后收到一条通知，询问我是否要保存对数据库所做的任何更改。我天真地点击了“否”并关闭了应用程序。

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.

显然发生的情况是 GUI 然后锁定了数据库，因为我的数据库中有一些行在没有提交删除的情况下被“软删除”。所以 GUI 是（因为没有更好的术语）在 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.

这解释了为什么a)我的应用程序没有像行丢失一样，因为它实际上还没有被删除，并且B)解释了数据库锁定的原因。它仍在等待我提交删除。

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!

所以为了解决这个问题，我只需再次打开 GUI 并删除同一行，然后关闭 GUI，这次我在要求保存对数据库的更改时单击“是”。它保存了删除并解锁了数据库，现在我的应用程序可以运行了！

---

---

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.

我希望这可以帮助其他可能遇到相同问题但使用 SQLite 浏览器 GUI 界面的人。这可能是锁定数据库的原因。