SQLite concurrency, SQLite3::BusyException

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

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

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?

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

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

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

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.