From: Brian Candler <B.Candler@...>
Date: 2003-04-09T01:24:11+09:00
Subject: Re: Problem with timeout for DBI on Solaris

On Wed, Apr 09, 2003 at 01:10:46AM +0900, Daniel Berger wrote:
> As a test, we took down one of our test database machines and tried to
> connect to it.  The problem is that, on Solaris boxes, it seems to
> ignore the 'timeout' that we've wrapped the connection in.  Here's the
> code:
...
>       timeout(3){
>          dbh = DBI.connect(dsn,user,passwd)
>       }

I think that the problem is that once DBI.connect has committed to calling
the Oracle C library, it won't come back until it has finished one way or
the other. In other words, it doesn't play nicely with Ruby's cooperative
threading. If a SIGALRM is raised after 3 seconds, Ruby just takes a note of
it and lets the library call complete before taking action.

For Oracle, you can try using the ruby-oci8 library from RAA which supports
non-blocking queries (although I don't know if it supports non-blocking
connect). It comes with its own DBD which you have to install manually into
/usr/local/lib/ruby/site_ruby/1.6/DBD/OCI8/OCI8.rb

Then modify it as follows:

require 'dbi'
require 'DBD/OCI8/OCI8'

module DBI
module DBD
module OCI8
class Driver
  def connect( dbname, user, auth, attr )
    handle = ::OCI8.new(user, auth, dbname, attr['Privilege'])
    handle.non_blocking = true if attr['NonBlocking']    #<<<< add this line
    return Database.new(handle, attr)
  rescue OCIError => err
    raise DBI::DatabaseError.new(err.message, err.code)
  end
end
end
end
end

(i.e. either just run the above code at the start of your program, or add
the one extra line into the corresponding place in DBD/OCI8/OCI8.rb)

Then connect to your database as:

    dbh = DBI.connect('dbi:OCI8:',user,password, {'NonBlocking'=>true} )

This at least allows a multi-threaded application to run multiple concurrent
queries on the same database, without a slow query locking out all other
Ruby threads; as I said, I've not tested it with connect.

Having said all that, I can't explain why Oracle under Linux doesn't have
this problem.

> Checking netstat -a, it appears that the connection is just sitting in a
> SYN_SENT state.

Normally a TCP socket has a 75-second connect timeout. So I also don't know
why you're getting three minutes; perhaps something is trying twice, or has
altered the default TCP timers.

Regards,

Brian.