This project is archived and is in readonly mode.

#5941 ✓stale
The Doctor What

SQLite3::BusyExceptions are raised immediately in some cases, despite setting sqlite3_busy_timeout()

Reported by The Doctor What | November 9th, 2010 @ 09:01 PM

This is complicated, so bear with me.

We had a setup using multiple thin servers using SQLite3 as the
backend. We were getting occasional SQLite3::BusyExceptions. We found
a possible solution on StackOverflow[1]: set the timeout in
database.yaml, which calls sqlite3_busy_timeout().

This solution doesn't work if you use
ActiveRecord::Base.transaction() to group your writes into a single transaction. You still get immediate SQLite3::BusyExceptions.

Spending way to much time reading about how SQLite works, we
discovered that this is the correct behavior[2][3]. This is what we
figured out was happening:

SQL> begin transaction;
# The transaction has begun. We have acquired a SHARED lock.
SQL> select * from sometable;
# We are still using the SHARED lock.
# Meanwhile, another process acquires the RESERVED lock.
# and starts writing to the DB.
SQL> update sometable set value=2 where id = 3;
# We are now trying to request the RESERVED lock. SQLite
# raises the SQLITE_BUSY exception immediately because
# your previous selects may no longer be accurate[4]
# by the time it can get the RESERVED lock.

According to "Using SQlite" by Jay A. Kreibich[5], if you know you're
going to do writing in the transaction, you should use "begin
immediate transaction"[2]. This gets a RESERVED lock
immediately. If someone else holds the RESERVED lock, then it'll
return SQLITE_BUSY (aka SQLite::BusyException) immediately (or
after the sqlite_busy_timeout()). The great thing is none of the
statements in the transaction have run yet so you don't have to
rollback the ActiveRecord objects at all.

The quick fix is to modify begin_db_transaction
in
activerecord/lib/active_record/connection_adapters/sqlite3_adapter.rb to send :immediate to @connection.transaction():

def begin_db_transaction #:nodoc:
    @connection.transaction(:immediate)
end

While this is sufficent for our application, since we only use
transactions for UPDATEs and INSERTs, this would be a bad change
for people who use SELECT statements inside a transaction. They
will lose a lot of concurrency, since there is only one RESERVED
lock per database.

I suspect that to fix this correctly, we will need to modify
ConnectionAdapters::DatabaseStatements#transaction to accept another named argument to ActiveRecord::Base.transaction().

We can add an additional option (:write_timeout) which forces the
transaction to use :immediate instead of the default :deferred and
loops until the timeout hits. Here is my example code for
begin_db_transaction in activerecord/lib/active_record/connection_adapters/sqlite3_adapter.rb:

# write_timeout is in milliseconds.
def begin_db_transaction(write_timeout => nil) #:nodoc:
  if write_timeout.nil?
    @connection.transaction(:deferred) # Deferred is the default.
  else
    deadline = Time.new + (write_timeout.to_f / 1000)
    success = false
    while (!success and Time.new() < deadline) do
      begin
        @connection.transaction(:immediate)
        success = true
      rescue SQLite3::BusyException
        sleep 0.001
      end
    end
    raise SQLite3::BusyException unless success
  end
end

Either way, I happily volunteer to submit patches for rails 2.x and
rails 3.x to implement either solution (or a third, if someone comes
up with a different approach).

Ciao!

BTW: I suspect that this is the cause behind the old ticket 8811[6].

References:

Comments and changes to this ticket

  • Edzard Pasma

    Edzard Pasma November 12th, 2010 @ 01:06 PM

    Another solution may be found in the Python Sqlite interface. Python defines a common interface to all databases (DBAPI2) that specifies that database connections are by default NOT in autocommit mode. This may be different from Ruby (I'm completely ignorant here) but possibly the Python solution for SQLite brings a new idea. The Python interface needs to turn off the autocommit mode which is achieved by a simple BEGIN statement. But this is not issued rigth away but only when the first UPDATE (or other dml) statement is encountered. The interface scans each SQL statement to determine this. This way the RESERVED lock will be acquired when the connection does not yet have a SHARED lock and then the busy timeout will be neatly observed. The idea for Ruby would be to do something similar within the ActiveRecord transaction method (only for SQLite).

    Excuse me if this is not to the point. Also note that the idea is of no use if a connection already has a shared lock (by some unfinished cursor) before the transaction is started.

  • Santiago Pastorino

    Santiago Pastorino February 12th, 2011 @ 07:18 PM

    • State changed from “new” to “open”

    This issue has been automatically marked as stale because it has not been commented on for at least three months.

    The resources of the Rails core team are limited, and so we are asking for your help. If you can still reproduce this error on the 3-0-stable branch or on master, please reply with all of the information you have about it and add "[state:open]" to your comment. This will reopen the ticket for review. Likewise, if you feel that this is a very important feature for Rails to include, please reply with your explanation so we can consider it.

    Thank you for all your contributions, and we hope you will understand this step to focus our efforts where they are most helpful.

  • Santiago Pastorino

    Santiago Pastorino February 12th, 2011 @ 07:18 PM

    • State changed from “open” to “stale”

Create your profile

Help contribute to this project by taking a few moments to create your personal profile. Create your profile »

<h2 style="font-size: 14px">Tickets have moved to Github</h2>

The new ticket tracker is available at <a href="https://github.com/rails/rails/issues">https://github.com/rails/rails/issues</a>

People watching this ticket

Pages