DBD::Oracle9 and non-blocking mode

[Jim Cain]

I'm about ready to make available an early copy of my library, but first
I have some questions regarding non-blocking mode. I've never actually
used this feature, so I'd like to hear from those that do how they use
it. Do you need more support for it beyond simply setting/resetting the
mode? What is the most convenient way for you to be notified that an OCI
call is still executing? When a call is still running, would you like to
call Statement#execute again to see if it's finished, or would you like
some other method?

Thanks for the input.

Cheers,
Jim

 

[Brian Candler]

I'm about ready to make available an early copy of my library, but first
I have some questions regarding non-blocking mode. I've never actually
used this feature, so I'd like to hear from those that do how they use
it. Do you need more support for it beyond simply setting/resetting the
mode? What is the most convenient way for you to be notified that an OCI
call is still executing? When a call is still running, would you like to
call Statement#execute again to see if it's finished, or would you like
some other method?

I have used it briefly: setting non-blocking mode on Takehiro's ruby-oci8
library, I was able to get several threads to run concurrent slow Oracle
queries. It didn't need any special support for notification; I just let
each thread block until its particular query had finished. (ruby-oci8 deals
with this by polling the OCI library at increasing intervals until OCI says
the query has completed, and then fetches the results)

However, I wasn't particularly happy with this approach, so in the end I
changed my application to run as multiple processes (in fact running under
FastCGI, so Apache controls the number of processes which are spawned), each
of which is a single thread so I don't care if OCI blocks.

Regards,

Brian.

 

[KUBO Takehiro]

I have used it briefly: setting non-blocking mode on Takehiro's ruby-oci8
library, I was able to get several threads to run concurrent slow Oracle
queries. It didn't need any special support for notification; I just let
each thread block until its particular query had finished. (ruby-oci8 deals
with this by polling the OCI library at increasing intervals until OCI says
the query has completed, and then fetches the results)

I implemented it in the ruby layer.
In the next release I do it in the C API layer.
See the macro oci_rc2 in the following URL.
http://www.jiubao.org/ruby-oci8/ruby-oci8-v0.2-unusable-2003-0626/oci8.h
Its license is same with ruby. So you can freely use its code fragment
in your library or application.

However, I wasn't particularly happy with this approach, so in the end I
changed my application to run as multiple processes (in fact running under
FastCGI, so Apache controls the number of processes which are spawned), each
of which is a single thread so I don't care if OCI blocks.

Hmm. What approach you prefer?

 

[Brian Candler]

Hmm. What approach you prefer?

It just seemed to work faster when I had five separate processes doing
Oracle queries, than one process with five threads. Also, using
Apache/Fastcgi gave me a ready-made mechanism for creating a pool of
processes and automatically restarting them if one fails. Using threads I'd
have to write my own thread manager to do that.

Cheers,

Brian.

 

[KUBO Takehiro]

It just seemed to work faster when I had five separate processes doing
Oracle queries, than one process with five threads.

My approach wastes time. Undoubtedly your prospect is correct in
theory. OCI lacks any notification method whether the OCI call is
finished or not. So I use polling as a last resort.

I have one idea:
1. create a native thread per one connection by using OCI thread APIs.
2. when executing a OCI call which may be blocked, request the
associated native thread to execute it, then stop its ruby thread
by calling rb_thread_stop().
3. after OCI call is finished, the associated native thread wakes up
the ruby thread by calling rb_thread_wakeup().
But I hadn't tested it. I don't know whether it works.

Also, using
Apache/Fastcgi gave me a ready-made mechanism for creating a pool of
processes and automatically restarting them if one fails. Using threads I'd
have to write my own thread manager to do that.

Thanks.