Delete_all causes a deadlock on MSSQL database?

Rails
1

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

2

Any ideas anyone?

3

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

4

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.

5

On 27/07/06, chris hulbert [email protected] 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

6

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

7

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

8

Tom W. 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