Ruby-on-rails SQLite3::BusyException
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/78801/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQLite3::BusyException
提问by Shalmanese
Running a rails site right now using SQLite3.
现在使用 SQLite3 运行 Rails 站点。
About once every 500 requests or so, I get a
大约每 500 个请求一次，我得到一个
ActiveRecord::StatementInvalid (SQLite3::BusyException: database is locked:...
ActiveRecord::StatementInvalid (SQLite3::BusyException: 数据库被锁定:...
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.
我目前正在使用 SQLLite，因为您可以将数据库存储在源代码管理中，这使备份变得自然，并且您可以非常快速地推出更改。然而，它显然并没有真正设置为并发访问。明天早上我将迁移到 MySQL。
采纳答案by ravenspoint
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.
默认情况下，如果数据库忙且锁定，sqlite 会立即返回阻塞、忙错误。您可以要求它等待并在放弃之前继续尝试一段时间。这通常可以解决问题，除非您确实有 1000 个线程访问您的数据库，但我同意 sqlite 是不合适的。
// set SQLite to wait and retry for up to 100ms if database locked
sqlite3_busy_timeout( db, 100 );
回答by Rifkin Habsburg
You mentioned that this is a Rails site. Rails allows you to set the SQLite retry timeout in your database.yml config file:
您提到这是一个 Rails 站点。Rails 允许您在 database.yml 配置文件中设置 SQLite 重试超时：
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.
超时值以毫秒为单位指定。将其增加到 10 或 15 秒应该会减少您在日志中看到的 BusyExceptions 的数量。
This is just a temporary solution, though. If your site needs true concurrency then you will have to migrate to another db engine.
不过，这只是一个临时解决方案。如果您的站点需要真正的并发，那么您将不得不迁移到另一个数据库引擎。
回答by ybakos
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?
所有这些都是真的，但它并没有回答这个问题，这很可能是：为什么我的 Rails 应用程序偶尔会在生产中引发 SQLite3::BusyException？
@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).
@Shalmanese：生产托管环境是什么样的？是在共享主机上吗？包含 SQLite 数据库的目录是否位于 NFS 共享上？（可能在共享主机上）。
This problem likely has to do with the phenomena of file locking w/ NFS shares and SQLite's lack of concurrency.
这个问题可能与 NFS 共享的文件锁定现象和 SQLite 缺乏并发性有关。
回答by Ignacio Huerta
Just for the record. In one application with Rails 2.3.8 we found out that Rails was ignoring the "timeout" option Rifkin Habsburg suggested.
只是为了记录。在 Rails 2.3.8 的一个应用程序中，我们发现 Rails 忽略了 Rifkin Habsburg 建议的“超时”选项。
After some more investigation we found a possibly related bug in Rails dev: http://dev.rubyonrails.org/ticket/8811. And after some more investigation we found the solution(tested with Rails 2.3.8):
经过更多调查，我们在 Rails 开发中发现了一个可能相关的错误：http: //dev.rubyonrails.org/ticket/8811。经过更多调查，我们找到了解决方案（使用 Rails 2.3.8 测试）：
Edit this ActiveRecord file: activerecord-2.3.8/lib/active_record/connection_adapters/sqlite_adapter.rb
编辑这个 ActiveRecord 文件：activerecord-2.3.8/lib/active_record/connection_adapters/sqlite_adapter.rb
Replace this:
替换这个：
def begin_db_transaction #:nodoc:
catch_schema_changes { @connection.transaction }
end
with
和
def begin_db_transaction #:nodoc:
catch_schema_changes { @connection.transaction(:immediate) }
end
And that's all! We haven't noticed a performance drop and now the app supports many more petitions without breaking (it waits for the timeout). Sqlite is nice!
就这样！我们没有注意到性能下降，现在该应用程序支持更多请求而不会中断（等待超时）。Sqlite 不错！
回答by Adrien Jarthon
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 SHAREDlock)
- Read some data from DB (we are still using the SHAREDlock)
- Meanwhile, another process starts a transaction and write data (acquiring the RESERVEDlock).
- Then you try to write, you are now trying to request the RESERVEDlock
- 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 RESERVEDlock.
- 开始一个事务（获得一个共享锁）
- 从 DB 中读取一些数据（我们仍然使用SHARED锁）
- 同时，另一个进程启动一个事务并写入数据（获取RESERVED锁）。
- 然后你尝试写，你现在正在尝试请求RESERVED锁
- SQLite立即引发 SQLITE_BUSY 异常（独立于您的超时），因为到获得RESERVED锁时，您之前的读取可能不再准确。
One way to fix this is to patch the active_recordsqlite adapter to aquire a RESERVEDlock directly at the begining of the transaction by padding the :immediateoption 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:
解决此问题的一种方法是通过将选项填充到驱动程序来修补active_recordsqlite 适配器以在事务开始时直接获取RESERVED锁:immediate。这会稍微降低性能，但至少您的所有交易都会遵守您的超时并一个接一个地发生。以下是使用prepend（Ruby 2.0+）将其放入初始化程序的方法：
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
在此处阅读更多信息：https: //rails.lighthouseapp.com/projects/8994/tickets/5941-sqlite3busyexceptions-are-raised-immediately-in-some-cases-despite-setting-sqlite3_busy_timeout
回答by Balaji Radhakrishnan
bundle exec rake db:reset
It worked for me it will reset and show the pending migration.
它对我有用，它将重置并显示挂起的迁移。
回答by Elindor
Most answers are for Rails rather than raw ruby, and OPs question IS for rails, which is fine. :)
大多数答案是针对 Rails 而不是原始 ruby 的，OP 的问题是针对 Rails，这很好。:)
So I just want to leave this solution over here should any raw ruby user have this problem, and is not using a yml configuration.
因此，如果任何原始 ruby 用户遇到此问题，并且不使用 yml 配置，我只想将这个解决方案留在这里。
After instancing the connection, you can set it like this:
实例化连接后，您可以这样设置：
db = SQLite3::Database.new "#{path_to_your_db}/your_file.db"
db.busy_timeout=(15000) # in ms, meaning it will retry for 15 seconds before it raises an exception.
#This can be any number you want. Default value is 0.
回答by meredrica
I had a similar problem with rake db:migrate. Issue was that the working directory was on a SMB share. I fixed it by copying the folder over to my local machine.
我对 rake db:migrate 有类似的问题。问题是工作目录位于 SMB 共享上。我通过将文件夹复制到我的本地机器来修复它。
回答by alfredodeza
Sqlite can allow other processes to wait until the current one finished.
Sqlite 可以允许其他进程等待当前进程完成。
I use this line to connect when I know I may have multiple processes trying to access the Sqlite DB:
当我知道可能有多个进程试图访问 Sqlite DB 时，我使用此行进行连接：
conn = sqlite3.connect('filename', isolation_level = 'exclusive')
conn = sqlite3.connect('文件名', isolation_level = 'exclusive')
According to the Python Sqlite Documentation:
根据 Python Sqlite 文档：
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.
您可以通过connect()调用的isolation_level参数或连接的isolation_level属性来控制pysqlite隐式执行哪种BEGIN语句（或根本不执行）。
回答by Brian R. Bondy
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(...)
