SQLite concurrency, SQLite3::BusyException

Rails
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

C:\test>ruby script\runner ‘PersonUpdator.start’
c:/ruby/lib/ruby/gems/1.8/gems/rails-2.0.2/lib/commands/runner.rb:47:
c:/ruby/lib/ruby/gems/1.8/gems/activerecord-2.0.2/lib/active_record/
connection_adapters/abstract_adapter.rb:150:in log': SQLite3::BusyException: database is locked: UPDATE people (ActiveRecord::StatementInvalid) SET "name" = 'Michael', "lock_version" = 16 WHERE id = 1 AND "lock_version" = 15 from C:/test/lib/person_updator.rb:18:injoin’
from C:/test/lib/person_updator.rb:18:in start' from (eval):1 from c:/ruby/lib/ruby/site_ruby/1.8/rubygems/custom_require.rb: 27:ineval’
from c:/ruby/lib/ruby/gems/1.8/gems/rails-2.0.2/lib/commands/
runner.rb:47
from c:/ruby/lib/ruby/site_ruby/1.8/rubygems/custom_require.rb:
27:in gem_original_require' from c:/ruby/lib/ruby/site_ruby/1.8/rubygems/custom_require.rb: 27:inrequire’
from script/runner:3

Using the following gems/environment:
rails 2.0.2
activerecord 2.0.2
sqlite3-ruby 1.2.1
sqlite 3.5.1
Windows XP

2

On 15 Jan 2008, at 10:11, mif wrote:

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?

On Jan 15, 8:22 pm, Frederick C. [email protected]

4

On 16 Jan 2008, at 01:39, mif wrote:

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

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

6

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

*** 1,4 ****
— 1,5 ----
require ‘active_record/connection_adapters/abstract_adapter’

  • require ‘thread’

    module ActiveRecord
    class Base

*** 129,135 ****
# DATABASE STATEMENTS ======================================

    def execute(sql, name = nil) #:nodoc:

! catch_schema_changes { log(sql, name)
{ @connection.execute(sql) } }
end

    def update_sql(sql, name = nil) #:nodoc:

— 130,138 ----
# DATABASE STATEMENTS ======================================

    def execute(sql, name = nil) #:nodoc:

! Thread.exclusive do
! catch_schema_changes { log(sql, name)
{ @connection.execute(sql) } }
! end
end

    def update_sql(sql, name = nil) #:nodoc:

On Jan 16, 4:41 pm, Frederick C. [email protected]

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.