SQLite concurrency, SQLite3::BusyException

rubyonrails-talk
1

I am currently experiencing concurrency issues after moving from MySQL to SQLite. My original program worked fined using MySQL but is now returning "SQLite3::BusyException" errors. The same result happens whether or not I enable the allow_concurrency flag.

If I do manually acquire a lock on the SQLite DB the problem would disapear, but I thought that rails was supposed to handle this internally (optimistic locking?)

For example the following code (shortened for clarity purposes) caused no exception with MySQL but would not run with SQLite. Is this a rails issue or is it simply normal behavior?

----- lib/person_updator.rb ----- class PersonUpdator

  def self.start     ActiveRecord::Base.allow_concurrency = true     p1 = Person.find_by_id(1)     p2 = Person.find_by_id(2)

    t = Thread.start do       5.times do         p1.name = "Michael"         p1.save       end     end     5.times do       p2.name = "Joe"       p2.save     end     t.join   end

end

2

I am currently experiencing concurrency issues after moving from MySQL to SQLite. My original program worked fined using MySQL but is now returning "SQLite3::BusyException" errors. The same result happens whether or not I enable the allow_concurrency flag.

If I do manually acquire a lock on the SQLite DB the problem would disapear, but I thought that rails was supposed to handle this internally (optimistic locking?)

For example the following code (shortened for clarity purposes) caused no exception with MySQL but would not run with SQLite. Is this a rails issue or is it simply normal behavior?

sqlite has less support for concurency: the whole database needs to be
locked for writing, whereas on mysql you've got either row level
locking (innodb) or table level locking (mysql). Optimistic locking avoids having to acquire a lock while you edit the
row, but at the point where you write to the database you need write
access to it, which only one connection to the database can have in
sqlite. I would have thought that sqlite would just wait for
outstanding writes to complete rather than raising an exception though.

Fred

3

Thanks for the answer. In this case, would that mean that existing rails 1.2 code (using MySQL) cannot be used "at is" when switching over to rails 2.0.2 with SQLite?

4

Thanks for the answer. In this case, would that mean that existing rails 1.2 code (using MySQL) cannot be used "at is" when switching over to rails 2.0.2 with SQLite?

Maybe, maybe not. You'd have to give it a go (and check that you're
not using any features of mysql that aren't supported)

Fred

5

Because I wanted to have rails with SQLite to behave the same way as it did with MySQL (and avoid adding lock acqs and releases everywhere in my existing code), I fixed this problem I had by adding just two lines to the rails SQLite adapter (sqlite_adapter.rb). Am I right in thinking that rails should handle all such DB concurrency issues and leave the application layer free of such concerns? If so, would the following modification be the correct way to fix this problem?

The file I modified is the following: \ruby\lib\ruby\gems\1.8\gems\activerecord-2.0.2\lib\active_record \connection_adapters\sqlite_adapter.rb

And below are the modifications I made (taken from diff tool)

*** sqlite_adapter.rb.org 2008-01-25 16:23:37.000000000 +0900 --- sqlite_adapter.rb 2008-01-25 16:24:44.000000000 +0900

6

Should the problem and fix described above be issued as a bug/patch in the rails tracker?

7

BTW, that patch is not what you want, it would only help for concurrent access across threads, not between processes.

The best solution to this issue, if you're dead-set on using SQLite, is to set a timeout in your connection specification:

dev_sqlite:   adapter: sqlite3   dbfile: db/dev.db   timeout: 15000

That sets the retry period to 15 seconds. You'll only get the BusyException if it takes longer.