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

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:

• [1] http://stackoverflow.com/questions/78801/sqlite3busyexception
• [2] http://www.sqlite.org/lang_transaction.html
• [3] http://www.sqlite.org/lockingv3.html#reserved_lock
• [4] http://www.sqlite.org/lockingv3.html#hot_journals
• [5] http://oreilly.com/catalog/9780596521196
• [6] http://dev.rubyonrails.org/ticket/8811