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:in `join''
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:in `eval''
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:in `require''
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
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---
On 15 Jan 2008, at 10:11, mif wrote:> > 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> > > > > ----- 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:in `join'' > 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:in `eval'' > 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:in `require'' > 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 > >--~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---
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 Cheung <frederick.che...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:> On 15 Jan 2008, at 10:11, mif wrote: > > > > > 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 > > > > > ----- 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:in `join'' > > 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:in `eval'' > > 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:in `require'' > > 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--~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---
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> > > On Jan 15, 8:22 pm, Frederick Cheung <frederick.che...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> > wrote: >> On 15 Jan 2008, at 10:11, mif wrote: >> >> >> >>> 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 >> >> >> >>> ----- 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:in `join'' >>> 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:in `eval'' >>> 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:in `require'' >>> 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 > >--~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---
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 Cheung
<frederick.che...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org>
wrote:> 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
>
>
>
> > On Jan 15, 8:22 pm, Frederick Cheung
<frederick.che...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org>
> > wrote:
> >> On 15 Jan 2008, at 10:11, mif wrote:
>
> >>> 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
>
> >>> ----- 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:in `join''
> >>> 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:in `eval''
> >>> 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:in `require''
> >>> 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
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---
Should the problem and fix described above be issued as a bug/patch in the rails tracker? --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---
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. -- Posted via http://www.ruby-forum.com/. --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---