Help with thread and database connection

Hi guys, i need help with this:

# enviroment.rb

config.active_record.allow_concurrency = true

# billing.rb

pid = Fork.begin(:detach => true) do
# Fork.begin is a custom module, makes a fork with the properly mysql
connection and detach it ...

  while service.status # while status remains true... search for
incoming billing (MAIN LOOP)

    messages = self.find(:first, :select => "count(id) as
messages_count", :conditions => "etc etc etc")
    if messages.messages_count.to_i > 0 # if nothing, search again for
incoming billing

      self.connect_billing_socket(service, operator) # attempt to connect
to the billing socket

      10.times do |i|
        Thread.new do
          while true
            break unless message = self.get_message(service, operator)
rescue false # get a message to make the bill
            message.cobro_socket(service, operator) # send through socket
the message, phone number of remitent, and others info to make the
billing transaction with the operator
          end
        end
      end

    end
    sleep 5
    service.reload
  end

end

So.. basically this is the most important part of the script.
I skipped many things to simplify this example.
The script connects to a socket to send a billing info, and
this socket can process up to 10 asynchronous request.
The problem is than each thread creates a new connection to
the database, with the time, the database crash
for have too many connections open, this happens when all
threads finish and the "MAIN LOOP" start over and
find more incoming billings, then "10.times do" makes 10
threads again and each thread creates a new database
connection...

How i can re-use open connections, or best, can
multiples threads share a single database connection??

I really not whant to close the old connections manually..

Thanks

So.. basically this is the most important part of the script.
I skipped many things to simplify this example.
The script connects to a socket to send a billing info, and
this socket can process up to 10 asynchronous request.
The problem is than each thread creates a new connection to
the database, with the time, the database crash
for have too many connections open, this happens when all
threads finish and the "MAIN LOOP" start over and
find more incoming billings, then "10.times do" makes 10
threads again and each thread creates a new database
connection...

How i can re-use open connections, or best, can
multiples threads share a single database connection??

What you're asking for is connection pooling and rails doesn't have
that. Nick Sieger is working on implementing that (last I heard it was
basically done - it may already have been merged into edge).
As far as I know for now you'll have to tear down the connections
yourself. (That said it's not clear to me why you need those 10 threads)

Fred

thanks for you reply frederick... the 10 thread is because i need to
send 10 asynchronous request to the socket at the same time in the
same connection.. the operator socket accept up to 10 asynchronous
request per session, each request to the socket have a response of 4
to 10 seconds, sending only one request, means that in one minute the
system will process in good conditions 15 request... in the other
hand, if i have 10 asynchronous thread, sending 10 request at the same
time, the system will process 150 request... it's a big diference..

i have this working on php, and works fine, but i want to migrate the
my old php system to rails...

Rails is not (yet) threadsafe, so I don't doubt you are having trouble
using it in a threaded manner. This being the case, there's little we
can do to help support your use of threads in rails.

okdk... many thanks Rein

ActiveRecord can be used in a thread, but as you've found will leak connections.
You could probably get around it by doing the activerecord stuff in your main thread and only spinning off the secondary threads for the network stuff.

Fred

Or you can use something like Backgroundrb to spawn new processes
which will at least safeguard against this situation. Multiple
processes instead of threads is an expensive trade-off though, but can
work if you throw enough resources at it.

Bharat

i found an exit.. not elegant, but works fine...

first at all "allow_concurrency = true" sucks... really.. it creates
too many connections, and never close it...
so, manually i have to creates a new connection for each Thread

10.times do |i|
  config = Fork::thread do
    etc..etc..etc...
  end
end

ActiveRecord::Base.establish_connection(config) unless con =
ActiveRecord::Base.connection.active? rescue false

very similar to "http://blog.ardes.com/2006/12/12/testing-concurrency-
in-rails-using-fork", but, when the thread finish, close the
connection, and the parent process stay with no db conection.. so.. at
the en of the "10.times do |i|" i have to reopen the db connection...
it seemed that it works, at least the db connections remains always..
I will continue trying to do of another way.. but, i do not have many
hopes... create, an manipulate forks and thread it´s very easy and
elegant in rails, but Active Record seems to be very fragile in
matters of parallelism..