ActiveRecord, Sqlite3, and BusyException

I have a rails app configured with mixed mysql and sqlite3 db connections, and to associate specific models with sqlite3, i add an "establish_connection 'sqlite_db_config_name'" line 开发者_JAVA百科to each class definition.

When I try to save any sqlite3-connected model object individually, the save is successful, but when I try to save an object that is composed of other objects (via has_many), I get a BusyException. I have a feeling it's because each object has its own connection to the db and the top-level object locks the database then calls the member objects' save methods and they can't acquire the lock.

I'm assuming there's a way to make this work and I'm using establish_connection improperly.

Anyone else encounter this?

database.yml config:

dev:

development:
  adapter: mysql
  database: maindb
  username: root
  password:
  host: localhost

sqlite:
  adapter: sqlite3
  database: db/db.sqlite3
  timeout: 15000

model definitions:

class Foo < ActiveRecord::Base
  establish_connection 'sqlite'
  belongs_to :bar
end

class Bar < ActiveRecord::Base
  establish_connection 'sqlite'
  has_many :foo  
  def addFoo(item)
    self.foos << item
  end
end

class MysqlModel < ActiveRecord::Base
end

Other:

Ruby 1.8.7

Rails 2.3.4

Ubuntu 10.04

Update:

I tried using inheritance to isolate the establish_connection statement in a single class, based on the rails doc's explanation that "This feature is implemented by keeping a connection pool in ActiveRecord::Base that is a Hash indexed by the class. If a connection is requested, the retrieve_connection method will go up the class-hierarchy until a connection is found in the connection pool.". But for some reason rails associated the subclasses of the sqlite-connected class with the default mysql connection. So I've given up on attempting a has_many/belongs_to relationship with sqlite, and de-normalized my models.

I think "establish_connection 'sqlite'" is causing problem. Try remove that. SQLite only allow one connection for writing per database(a file)

If you perform multiple write to a database without closing it, it will cause that Exception

Rails can handle connection efficiently and automatically, so that I think we don't need to estabilish connection separately.

I found a deadlock on sqlite3 ruby extension and fix it here: have a go with it and see if this fixes ur problem.

    https://github.com/dxj19831029/sqlite3-ruby

I opened a pull request, no response from them anymore.

Anyway, some busy exception is expected as described in sqlite3 itself.

Be aware with this condition: sqlite busy

    The presence of a busy handler does not guarantee that it will be invoked when there is 
    lock contention. If SQLite determines that invoking the busy handler could result in a 
    deadlock, it will go ahead and return SQLITE_BUSY or SQLITE_IOERR_BLOCKED instead of 
    invoking the busy handler. Consider a scenario where one process is holding a read lock 
    that it is trying to promote to a reserved lock and a second process is holding a reserved 
    lock that it is trying to promote to an exclusive lock. The first process cannot proceed 
    because it is blocked by the second and the second process cannot proceed because it is 
    blocked by the first. If both processes invoke the busy handlers, neither will make any 
    progress. Therefore, SQLite returns SQLITE_BUSY for the first process, hoping that this 
    will induce the first process to release its read lock and allow the second process to 
    proceed.

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.

Hope this helps. :)