Rails - Jul 2006 - Delete_all causes a deadlock on MSSQL database ???

Hi all,
When i use the ''delete_all'' function to remove a few rows from
my
database, it causes a deadlock on the sql server. The delete seems to be 
successful, it then redirects to the ''view'' page, and the
record does
appear to be deleted as far as the ''view'' rails page shows.
BUT if i go into sql query analyser, i can''t do a ''select *
from foo''
anymore to see if the record has indeed been deleted, because of the 
deadlock.
Its exactly as though rails has opened a transaction but not committed 
it.
Here''s the funnier rub though, if i replace the delete_all with a 
destroy_all, i don''t have any problems!!!

Anyone know whats going on?

Here''s the code


  def save
    # scan through the params, looking for any starting with
''reason_''
to save
    params.each {|key,reason|
      if ''reason_'' == key[0,7]
        pc,what = key[7,99].split ''_''
        if '''' == reason
          # delete this one
          MissingEntriesReason.destroy_all \
            "profit_centre_id = #{pc} AND which_month = 
#{params[:month]} and which_year = ''#{params[:f_year]}'' and 
what=''#{what}''"
          # can''t use delete_all - this causes a deadlock in the 
database !??!
       else
          # save this one
          r = 
MissingEntriesReason.find_or_create_by_profit_centre_id_and_which_month_and_which_year_and_what
\
            pc, params[:month], params[:f_year], what
          r.reason = reason
          r.save
        end
      end
    }

    flash[:lower_notice] = ''Saved!''
    redirect_to :action => ''index''
  end

-- 
Posted via http://www.ruby-forum.com/.

Any ideas anyone?

-- 
Posted via http://www.ruby-forum.com/.

chris wrote:
> Any ideas anyone?
Ive seen these deadlocks too. I have no idea where they come from, but I 
was also seeing them with out the delete that you saw.

I''ll add it to my list of things to do to take a better look at what is
going on

-- 
Posted via http://www.ruby-forum.com/.

Paul wrote:
> I''ll add it to my list of things to do to take a better look at
what is
> going on
That''d be great... so you also use mssql? I think a lot of the code for
mssql isn''t used very much, hence hasn''t had all the bugs
ironed out of
it. Most people seem to use mysql with rails.

-- 
Posted via http://www.ruby-forum.com/.

On 27/07/06, chris hulbert <chris.hulbert-nospam@gmail.com>
wrote:
> Paul wrote:
> > I''ll add it to my list of things to do to take a better look
at what is
> > going on
>
> That''d be great... so you also use mssql? I think a lot of the
code for
> mssql isn''t used very much, hence hasn''t had all the bugs
ironed out of
> it. Most people seem to use mysql with rails.
That''s true, though there is an ongoing effort to reduce the bugs at
the moment.  There are also some assumptions in ActiveRecord that the
adapter has to work around (particularly involving limits and
offsets).

As to your problem, can I ask whether you use ADO or ODBC mode?

Tom

> As to your problem, can I ask whether you use ADO or ODBC mode?
> 
> Tom
ADO, i think. My database.yml is like this:
---
production:
  adapter: sqlserver
  database: reports
  host: financeapps
  username: reports
  password: abc123
---

I followed the instructions from this page
http://wiki.rubyonrails.com/rails/pages/HowtoConnectToMicrosoftSQLServer

-- 
Posted via http://www.ruby-forum.com/.

Yes, that''s ado.  Could you try connecting in odbc mode isntead?

You''ll need to change your database.yml file to something like this
(using a proper ODBC connection string):

production:
  adapter: sqlserver
  mode: ODBC
  dsn: Driver={SQL
Server};Server=financeapps;Database=reports;Uid=reports;Pwd=abc123

Tom

On 28/07/06, chris hulbert <chris.hulbert-nospam@gmail.com>
wrote:
> > As to your problem, can I ask whether you use ADO or ODBC mode?
> >
> > Tom
>
> ADO, i think. My database.yml is like this:
> ---
> production:
>   adapter: sqlserver
>   database: reports
>   host: financeapps
>   username: reports
>   password: abc123
> ---
>
> I followed the instructions from this page
> http://wiki.rubyonrails.com/rails/pages/HowtoConnectToMicrosoftSQLServer
>
> --
> Posted via http://www.ruby-forum.com/.
> _______________________________________________
> Rails mailing list
> Rails@lists.rubyonrails.org
> http://lists.rubyonrails.org/mailman/listinfo/rails
>

Tom Ward wrote:
> Yes, that''s ado.  Could you try connecting in odbc mode isntead?
> 
> You''ll need to change your database.yml file to something like
this
> (using a proper ODBC connection string):
> 
> production:
>   adapter: sqlserver
>   mode: ODBC
>   dsn: Driver={SQL
> Server};Server=financeapps;Database=reports;Uid=reports;Pwd=abc123
> 
> Tom
Sorry for the delay, but i simply can''t get the problem to re-occur any
more. It just seems to work now: both delete_all and destroy_all work 
fine now.
There are no deadlocks, even though before, i could repeatedly 
demonstrate only destroy_all worked, and delete_all always locked the 
database.

Strange, but thanks anyway

-- 
Posted via http://www.ruby-forum.com/.