How to time out if proc stored procedure doesn't return

I am implementing the following scenario:

100 threads spawned and each thread is connecting to DB with separate connections and run time contexts. Each thread will be executing a stored procedure which returns ref cursor.

Problem: at times call to stored procedure ne开发者_如何学运维ver returns (stored procedure call from proc hangs)

Question: is there any way to time out the call to stored procedure from proc if it doesn't return in specified time?

Why not implement a timer in your thread(s)? If it times out then re-establish your database connection and retry.

The only problem with timeouts is if the database server is very busy it may not be hung, just running very slowly.

On a POSIX platform, if you are doing a normal blocking syscalls such as read, write, wait, etc. you can either use select or poll to monitor the descriptor for when data is available to prevent blocking (with an associated time-out parameter), or use an alarm to trigger a signal that will cause the syscall to return with a EINT error.

Now, are the calls that are blocking some Oracle DB specific calls, or just normal syscalls? If the former (i.e., not syscalls), then you can take another approach where you have a timeout alarm, that when it goes off, you kill any threads that are still running. Using pthreads, start each child thread from a parent thread as a detached thread so that when the call completes successfully, it will end itself without you having to manually kill it or call pthread_join. Keep an array to all the pthread ID's, and when the alarm goes off, simply call pthread_cancel on all the thread descriptors from the main parent thread. For threads that have completed, this will do nothing, but for threads that are stuck, they will be killed.

You can block the alarm signal from all the threads by blocking it in the main parent thread before you spawn any child threads, and then just use sigwait to monitor the arrival of the timeout alarm from the parent thread. Doing this will prevent any of the child threads from catching the alarm signal (i.e., only the parent will catch and process the alarm signal).

The database library might already support a time-out feature. Sybase dblib does.