<p>Running a rails site right now using SQLite3.</p>

<p>About once every 500 requests or so, I get a</p>

<p>ActiveRecord::StatementInvalid (SQLite3::BusyException: database is locked:...</p>

<p>What's the way to fix this that would be minimally invasive to my code?</p>

<p>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.</p>

[{'answer_id': 78817, 'author': 'Sargun Dhillon', 'author_id': 10432, 'author_profile': 'https://Stackoverflow.com/users/10432', 'pm_score': -1, 'selected': False, 'text': '<p>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? </p>\n'}, {'answer_id': 78870, 'author': 'Brian R. Bondy', 'author_id': 3153, 'author_profile': 'https://Stackoverflow.com/users/3153', 'pm_score': 0, 'selected': False, 'text': '<p>Source: <a href="http://nessie.de/mroth/lua-sqlite3/documentation.html" rel="nofollow noreferrer">this link</a></p>\n\n<pre><code>- Open the database\ndb = sqlite3.open("filename")\n\n-- Ten attempts are made to proceed, if the database is locked\nfunction my_busy_handler(attempts_made)\n  if attempts_made &lt; 10 then\n    return true\n  else\n    return false\n  end\nend\n\n-- Set the new busy handler\ndb:set_busy_handler(my_busy_handler)\n\n-- Use the database\ndb:exec(...)\n</code></pre>\n'}, {'answer_id': 78881, 'author': 'David Medinets', 'author_id': 219658, 'author_profile': 'https://Stackoverflow.com/users/219658', 'pm_score': 0, 'selected': False, 'text': '<p>What table is being accessed when the lock is encountered?</p>\n\n<p>Do you have long-running transactions?</p>\n\n<p>Can you figure out which requests were still being processed when the lock was encountered?</p>\n'}, {'answer_id': 78958, 'author': 'Voltaire', 'author_id': 14621, 'author_profile': 'https://Stackoverflow.com/users/14621', 'pm_score': 0, 'selected': False, 'text': "<p>Argh - the bane of my existence over the last week. Sqlite3 locks the db file when any process <em>writes</em> to the database. IE any UPDATE/INSERT type query (also select count(*) for some reason). However, it handles multiple reads just fine.</p>\n\n<p>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.</p>\n\n<p>And yea, its slow as hell. But its also fast enough and <em>correct</em>, which is a nice property to have.</p>\n"}, {'answer_id': 95255, 'author': 'ravenspoint', 'author_id': 16582, 'author_profile': 'https://Stackoverflow.com/users/16582', 'pm_score': 4, 'selected': True, 'text': '<p>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.</p>\n\n<pre>\n    // set SQLite to wait and retry for up to 100ms if database locked\n    sqlite3_busy_timeout( db, 100 );\n</pre>\n'}, {'answer_id': 739037, 'author': 'Rifkin Habsburg', 'author_id': 89619, 'author_profile': 'https://Stackoverflow.com/users/89619', 'pm_score': 6, 'selected': False, 'text': '<p>You mentioned that this is a Rails site. Rails allows you to set the SQLite retry timeout in your database.yml config file:</p>\n\n<pre><code>production:\n  adapter: sqlite3\n  database: db/mysite_prod.sqlite3\n  timeout: 10000\n</code></pre>\n\n<p>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.</p>\n\n<p>This is just a temporary solution, though. If your site needs true concurrency then you will have to migrate to another db engine.</p>\n'}, {'answer_id': 739472, 'author': 'alfredodeza', 'author_id': 89528, 'author_profile': 'https://Stackoverflow.com/users/89528', 'pm_score': 1, 'selected': False, 'text': "<p>Sqlite can allow other processes to wait until the current one finished.</p>\n\n<p>I use this line to connect when I know I may have multiple processes trying to access the Sqlite DB:</p>\n\n<p>conn = sqlite3.connect('filename', <strong>isolation_level = 'exclusive'</strong>)</p>\n\n<p>According to the Python Sqlite Documentation:</p>\n\n<blockquote>\n  <p>You can control which kind of BEGIN\n  statements pysqlite implicitly\n  executes (or none at all) via the\n  isolation_level parameter to the\n  connect() call, or via the\n  isolation_level property of\n  connections.</p>\n</blockquote>\n"}, {'answer_id': 4226570, 'author': 'ybakos', 'author_id': 129622, 'author_profile': 'https://Stackoverflow.com/users/129622', 'pm_score': 2, 'selected': False, 'text': "<p>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?</p>\n\n<p>@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).</p>\n\n<p>This problem likely has to do with the phenomena of file locking w/ NFS shares and SQLite's lack of concurrency.</p>\n"}, {'answer_id': 5112460, 'author': 'meredrica', 'author_id': 633375, 'author_profile': 'https://Stackoverflow.com/users/633375', 'pm_score': 1, 'selected': False, 'text': '<p>I had a similar problem with rake db:migrate. Issue was that the working directory was on a SMB share.\nI fixed it by copying the folder over to my local machine.</p>\n'}, {'answer_id': 6099601, 'author': 'Ignacio Huerta', 'author_id': 766296, 'author_profile': 'https://Stackoverflow.com/users/766296', 'pm_score': 2, 'selected': False, 'text': '<p>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.</p>\n\n<p>After some more investigation we found a possibly related bug in Rails dev: <a href="http://dev.rubyonrails.org/ticket/8811" rel="nofollow">http://dev.rubyonrails.org/ticket/8811</a>. And after some more investigation we found <a href="http://webcache.googleusercontent.com/search?q=cache:6_UlZMo3eH4J:https://rails.lighthouseapp.com/projects/8994/tickets/5941+ActiveRecord+SQLite3+BusyException&amp;cd=10&amp;hl=en&amp;ct=clnk&amp;client=ubuntu&amp;source=www.google.com" rel="nofollow">the solution</a> (tested with Rails 2.3.8):</p>\n\n<p>Edit this ActiveRecord file: activerecord-2.3.8/lib/active_record/connection_adapters/sqlite_adapter.rb</p>\n\n<p>Replace this:</p>\n\n\n\n<pre class="lang-rb prettyprint-override"><code>  def begin_db_transaction #:nodoc:\n    catch_schema_changes { @connection.transaction }\n  end\n</code></pre>\n\n<p>with</p>\n\n\n\n<pre class="lang-rb prettyprint-override"><code>  def begin_db_transaction #:nodoc:\n    catch_schema_changes { @connection.transaction(:immediate) }\n  end\n</code></pre>\n\n<p>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!</p>\n'}, {'answer_id': 13189516, 'author': 'xijing dai', 'author_id': 628568, 'author_profile': 'https://Stackoverflow.com/users/628568', 'pm_score': 0, 'selected': False, 'text': '<p>I found a deadlock on sqlite3 ruby extension and fix it here: have a go with it and see if this fixes ur problem. </p>\n\n<pre>\n\n    https://github.com/dxj19831029/sqlite3-ruby\n\n</pre>\n\n<p>I opened a pull request, no response from them anymore.</p>\n\n<p>Anyway, some busy exception is expected as described in sqlite3 itself. </p>\n\n<p><strong>Be aware</strong> with this condition: <a href="http://www.sqlite.org/c3ref/busy_handler.html" rel="nofollow">sqlite busy</a></p>\n\n<pre>\n\n    The presence of a busy handler does not guarantee that it will be invoked when there is \n    lock contention. If SQLite determines that invoking the busy handler could result in a \n    deadlock, it will go ahead and return SQLITE_BUSY or SQLITE_IOERR_BLOCKED instead of \n    invoking the busy handler. Consider a scenario where one process is holding a read lock \n    that it is trying to promote to a reserved lock and a second process is holding a reserved \n    lock that it is trying to promote to an exclusive lock. The first process cannot proceed \n    because it is blocked by the second and the second process cannot proceed because it is \n    blocked by the first. If both processes invoke the busy handlers, neither will make any \n    progress. Therefore, SQLite returns SQLITE_BUSY for the first process, hoping that this \n    will induce the first process to release its read lock and allow the second process to \n    proceed.\n\n</pre>\n\n<p>If you meet this condition, timeout isn\'t valid anymore. To avoid it, don\'t put select inside begin/commit. or use exclusive lock for begin/commit.</p>\n\n<p>Hope this helps. :)</p>\n'}, {'answer_id': 14625992, 'author': 'Anno2001', 'author_id': 1201015, 'author_profile': 'https://Stackoverflow.com/users/1201015', 'pm_score': 0, 'selected': False, 'text': '<p>this is often a consecutive fault of multiple processes accessing the same database, i.e. if the "allow only one instance" flag was not set in RubyMine</p>\n'}, {'answer_id': 26150137, 'author': 'Adrien Jarthon', 'author_id': 304434, 'author_profile': 'https://Stackoverflow.com/users/304434', 'pm_score': 2, 'selected': False, 'text': '<p>If you have this issue but <strong>increasing the timeout does not change anything</strong>, you might have another concurrency issue with transactions, here is it in summary:</p>\n\n<ol>\n<li>Begin a transaction (aquires a <em>SHARED</em> lock)</li>\n<li>Read some data from DB (we are still using the <em>SHARED</em> lock)</li>\n<li>Meanwhile, another process starts a transaction and write data (acquiring the <em>RESERVED</em> lock).</li>\n<li>Then you try to write, you are now trying to request the <em>RESERVED</em> lock</li>\n<li>SQLite raises the SQLITE_BUSY exception <strong>immediately</strong> (indenpendently of your timeout) because your previous reads may no longer be accurate by the time it can get the <em>RESERVED</em> lock.</li>\n</ol>\n\n<p>One way to fix this is to patch the <code>active_record</code> sqlite adapter to aquire a <em>RESERVED</em> lock directly at the begining of the transaction by padding the <code>:immediate</code> 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 <code>prepend</code> (Ruby 2.0+) put this in a initializer:</p>\n\n<pre><code>module SqliteTransactionFix\n  def begin_db_transaction\n    log(\'begin immediate transaction\', nil) { @connection.transaction(:immediate) }\n  end\nend\n\nmodule ActiveRecord\n  module ConnectionAdapters\n    class SQLiteAdapter &lt; AbstractAdapter\n      prepend SqliteTransactionFix\n    end\n  end\nend\n</code></pre>\n\n<p>Read more here: <a href="https://rails.lighthouseapp.com/projects/8994/tickets/5941-sqlite3busyexceptions-are-raised-immediately-in-some-cases-despite-setting-sqlite3_busy_timeout" rel="nofollow">https://rails.lighthouseapp.com/projects/8994/tickets/5941-sqlite3busyexceptions-are-raised-immediately-in-some-cases-despite-setting-sqlite3_busy_timeout</a></p>\n'}, {'answer_id': 28494279, 'author': 'Balaji Radhakrishnan', 'author_id': 3930758, 'author_profile': 'https://Stackoverflow.com/users/3930758', 'pm_score': 2, 'selected': False, 'text': '<pre><code>bundle exec rake db:reset\n</code></pre>\n\n<p>It worked for me it will reset and show the pending migration.</p>\n'}, {'answer_id': 28494445, 'author': 'John', 'author_id': 3525295, 'author_profile': 'https://Stackoverflow.com/users/3525295', 'pm_score': 0, 'selected': False, 'text': '<p>Try running the following, it may help: </p>\n\n<pre><code>ActiveRecord::Base.connection.execute("BEGIN TRANSACTION; END;") \n</code></pre>\n\n<p>From: <a href="https://stackoverflow.com/questions/7154664/ruby-sqlite3busyexception-database-is-locked">Ruby: SQLite3::BusyException: database is locked:</a></p>\n\n<p>This may clear up the any transaction holding up the system</p>\n'}, {'answer_id': 47793885, 'author': 'Elindor', 'author_id': 3443954, 'author_profile': 'https://Stackoverflow.com/users/3443954', 'pm_score': 1, 'selected': False, 'text': '<p>Most answers are for Rails rather than raw ruby, and OPs question IS for rails, which is fine. :)</p>\n\n<p>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.</p>\n\n<p>After instancing the connection, you can set it like this:</p>\n\n<pre><code>db = SQLite3::Database.new "#{path_to_your_db}/your_file.db"\ndb.busy_timeout=(15000) # in ms, meaning it will retry for 15 seconds before it raises an exception.\n#This can be any number you want. Default value is 0.\n</code></pre>\n'}]
['https://Stackoverflow.com/questions/78801', 'https://Stackoverflow.com', 'https://Stackoverflow.com/users/14559/']