Non-blocking ODBC calls in Java

Question

I use pretty standard Java ODBC functionality - grab a Connection from the pool, create a Statement and execute it.

Our use-case is a game, logging game progress - ODBC calls are largely calls to stored procedures and in most cases there are no return values. So the fact the ODBC call blocks is annoying - the game is already turn based but users can see longer pauses if the DB is slow.

If I don't need to check the result of an ODBC call, is there any built-in functionality to execute the statement asyncronously? If not, what is a good way to do this without writing lots of code? I DO still need to catch ODBC exceptions when and if they occur.

This question looks related although not identical... Is asynchronous jdbc call possible?

Answer 1

Let's assume you have a OdbcCaller:

public class OdbcCaller {

public void callODBC() {
    // call ODBC directly
    // ...
}

You can wrap it in a runnable task and submit the task to a thread pool to make it executes asyncronously:

public void asyncCallODBC() {
    // wrap the call with a runnable task
    executor.execute(new Runnable() {

        @Override
        public void run() {
            callODBC();
        }
    });
    // the above line would return immediately.
}

The executor is a thread pool implementation provided by JDK, it could be defined as follows:

Executor executor = new ThreadPoolExecutor(/* core pool size */5,
        /* maximum pool size */10,
        /* keepAliveTime */1,
        /* time unit of keepAliveTime */TimeUnit.MINUTES,
        /* work queue */new ArrayBlockingQueue<Runnable>(10000),
        /* custom thread factory */new ThreadFactory() {
            private AtomicInteger counter = new AtomicInteger(0);

            @Override
            public Thread newThread(Runnable r) {
                Thread t = new Thread(r, "asyncCaller-" + (counter.incrementAndGet()));
                return t;
            }
        },
        /*
         * policy applied when all tasks are occupied and task
         * queue is full.
         */new ThreadPoolExecutor.CallerRunsPolicy());

The ThreadPoolExecutor is highly configurable and is welled documented in JavaDoc, you might want read it first.

Following are some sugguestion of thread pool configurations based on my experiences:

• The proper thread pool size is depending on the scenario, you may need run some tests to tune it.
• The work queue is used to cache the tasks when there are no available worker threads. A unbounded queue is not a good idea as you might run out your memory.
• It is a good practice to provide a ThreadFactory and give the threads a meaningful name. It will be very useful when you need to inspect threads states(using jstack or other tools).
• The reject policy is applied when no resources is available. You can choose one of the build-in policy(reject, discard, caller-run, discardOldest), or implement your own policy.