CODEWITHSUNDEEP
sqlruby-on-railsrubyactiverecordtimeout
Matt Westlake
Matt Westlake

Reputation: 3651

Why does Timeout.timeout(sec) not work with activerecord

I have the following code running to catch any SQL statements that might get hung. While trying to test this, I wrote a horibly optimized sql statement that takes over a minute to run. I put a 20 second timeout wrapper around an activerecord execute sql statement, but it doesn't seem to interrupt the sql call for taking to long. This is running against Oracle databases.

  start_time = Time.now
  Timeout.timeout(20) do #20 timeout for long running sql
    @connection.connection.execute(sql_string)
  end
  total_sql_time = Time.now - start_time
  puts "Sql statement took #{total_sql_time} seconds

output

  Sql statement took 64 seconds

Upvotes: 3

Views: 1491

Answers (3)

Denis de Bernardy
Denis de Bernardy

Reputation: 78443

(Just a guess…)

Ruby's Timeout.timeout() is probably operating in much the same way as the equivalent function in PHP, which is to say that it counts time executed within Ruby, and stops counting when you're waiting for database queries to run, waiting to finish reading or writing files, waiting to for a network request to complete, etc. — i.e. waiting for anything IO-related.

Upvotes: 2

mralexlau
mralexlau

Reputation: 1993

Remember that ActiveRecord is an ORM, which is to say it's a layer of abstraction that sits on top of your database.

This is important in the context of what happens when Timeout.timeout executes. Database adapters are responsible for taking a command in ruby and converting them into commands that are executed in your database, an entirely different process.

When a timeout happens, the call to @connection.connection.execute, as a ruby command, is interrupted in the adapter but not the sql code itself as you've noted.

You should be able to make use of the optional Exception Class parameter klass in Timeout.timeout to specify a signal/command to send to your database in order to cancel the query itself. However this would depend on your database adapter. Here's the kill query command I would try to execute if I were using mysql.

Upvotes: 1

IAmNaN
IAmNaN

Reputation: 10582

Admittedly, I don't have much experience with Rails backed by Oracle, but there's the timeout option in the database config file.

# config/database.yml
production:
  ...
  timeout: 20000

It's in milliseconds.

If I can assume you are in the development environment, you might want to explicitly set your explain timeout and review the console log:

# config/environments/development.rb
config.active_record.auto_explain_threshold_in_seconds 20

Upvotes: 1

Related Questions