magnason
2006-Nov-09 06:00 UTC
Page Level Locks Left In MSSQL when finding with conditions and > 1 association
I am experiencing perplexing page level locking problems when using
Rails/Windows/MSSQL
The following code results in piles ( ~80 in this case ) of active page
locks which then prevent any updates on the table until the mssql
process is killed.
I have another part of the application that uses find_by_sql with
several joins and it exhibits similar problems.
I have run the code under WebBrick/Mongrel/FastCGI and it happens in
each case.
Here is the code ( models and rails environment listed at the bottom )
Registration.find(
:all,
:conditions => [ "trip.dateStart > ? ",
''2007-01-01'' ] ,
:include => [ :trip, :constituent ]
)
The pertinent lock info for one of the locks is:
Type: PAG
Resource: 1:1033 ( each lock has a different fileid:pagenumber here )
Mode: IS
Status: GRANT
ObjName: con
IndID: 0
IndName: con
If i tweak the statement a bit - there are NO locks:
In this case get rid of the second associated model and there are no
locks
Registration.find(
:all,
:conditions => [ "trip.dateStart > ? ",
''2007-01-01'' ] ,
:include => [ :trip ]
)
In this case get rid of the conditions and there are no locks
Registration.find(
:all,
:include => [ :trip, :constituent ]
)
Here are the models:
class Registration < ActiveRecord::Base
set_table_name "register"
set_primary_key "regID"
belongs_to :constituent, :foreign_key => "conID"
belongs_to :trip, :foreign_key => "tripID"
end
class Constituent < ActiveRecord::Base
set_table_name "con"
set_primary_key "conID"
end
class Trip < ActiveRecord::Base
set_table_name "trip"
set_primary_key "tripID"
end
The only thing that is probably out of the ordinary is that we are
dealing with a legacy DB and have to manually set all the table names
and primary keys.
Rails Environment:
Ruby version 1.8.4 (i386-mswin32)
RubyGems version 0.8.11
Rails version 1.1.6
Active Record version 1.14.4
Action Pack version 1.12.5
Action Web Service version 1.1.6
Action Mailer version 1.2.5
Active Support version 1.3.1
Thanks for any help.
Dan
--~--~---------~--~----~------------~-------~--~----~
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
-~----------~----~----~----~------~----~------~--~---
mistcat-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org
2006-Nov-09 18:02 UTC
Re: Page Level Locks Left In MSSQL when finding with conditions and > 1 association
Wow this sounds like a tough problem! I don''t have any ideas, but maybe some of the other wizards in here can chime in? Ehh, Ehh? -nate On Nov 9, 1:00 am, "magnason" <magna...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:> I am experiencing perplexing page level locking problems when using > Rails/Windows/MSSQL > > The following code results in piles ( ~80 in this case ) of active page > locks which then prevent any updates on the table until the mssql > process is killed. > I have another part of the application that uses find_by_sql with > several joins and it exhibits similar problems. > I have run the code under WebBrick/Mongrel/FastCGI and it happens in > each case. > > Here is the code ( models and rails environment listed at the bottom ) > > Registration.find( > :all, > :conditions => [ "trip.dateStart > ? ", ''2007-01-01'' ] , > :include => [ :trip, :constituent ] > ) > > The pertinent lock info for one of the locks is: > > Type: PAG > Resource: 1:1033 ( each lock has a different fileid:pagenumber here ) > Mode: IS > Status: GRANT > ObjName: con > IndID: 0 > IndName: con > > If i tweak the statement a bit - there are NO locks: > > In this case get rid of the second associated model and there are no > locks > Registration.find( > :all, > :conditions => [ "trip.dateStart > ? ", ''2007-01-01'' ] , > :include => [ :trip ] > ) > > In this case get rid of the conditions and there are no locks > Registration.find( > :all, > :include => [ :trip, :constituent ] > ) > > Here are the models: > > class Registration < ActiveRecord::Base > set_table_name "register" > set_primary_key "regID" > belongs_to :constituent, :foreign_key => "conID" > belongs_to :trip, :foreign_key => "tripID" > end > > class Constituent < ActiveRecord::Base > set_table_name "con" > set_primary_key "conID" > end > > class Trip < ActiveRecord::Base > set_table_name "trip" > set_primary_key "tripID" > end > > The only thing that is probably out of the ordinary is that we are > dealing with a legacy DB and have to manually set all the table names > and primary keys. > > Rails Environment: > Ruby version 1.8.4 (i386-mswin32) > RubyGems version 0.8.11 > Rails version 1.1.6 > Active Record version 1.14.4 > Action Pack version 1.12.5 > Action Web Service version 1.1.6 > Action Mailer version 1.2.5 > Active Support version 1.3.1 > > Thanks for any help. > > Dan--~--~---------~--~----~------------~-------~--~----~ 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 -~----------~----~----~----~------~----~------~--~---