Solution to the long running query problem in a web application (asynchronous request)

Question

Here is the problem

A user of an enterprise web application is performing a task that results in a long (very long) database query (or other long processing intensive task)

Problems:

• Request timeout - after a while the user may get a request timeout
• Session timeout - if no session keeping methods are used, a session timeout can occur
• Request thread lock
  • since the request thread is not returning, it may block new requrests (if reaches the pool limit)
  • In some application servers the server's health status might trigger a forced restart of the node or application(due to a long running request thread)
• If the user leaves the page:
  • the transaction is not canceled - resulting in useless processing no one will benefit from
  • the user can't return to see the results after they complete
• no progress indication - the user just waits for the page to refresh

There are several solutions I came up with, but I'm not sure I know which is better (in all aspects, peformance, best practice, elegance and maintainability) and I would like to know what is your recommended solution, and if there is a solution that I missed? (probably yes, and many)

The bad solution: use the request thread as a worker thread,save progress state in the session, have an AJAX call check the status (in the session) in another paralel request

The compromise solution: create your own thread pool, handle a monitoring thread, a worker thread and take care of clustering by syncronizing the states in either a distrubuted transactional cache or persistant storage. this releases the request, but creats threads the application server is not aware of, and won't close in undeployment. Its up to you to shutdown threads in a clean way, and there is always a chance you will end up leaking something. This is not the J2EE way to do it either.

The J2EE solution: use JMS for the asynchronous task, this is what it's ment for

the Spring solution: use Spring batch

What would you do / did in your projects? What other solutions you know? which of the ones I noted above is the winner in your opinion?

Answer 1

The solution I have used before involves Jetty Cometd instead of AJAX. The main difference between Ajax and Cometd is that Cometd can uses more of a pub/sub model - the client (web browser in this case) subsribes to the publisher (your app) and the app pushes out updates and notifications to the web browser as apposed to an ajax model of constant polling of the server by the web browser. You can make use of the Cometd solution even if you are not using jetty - you can drop the jetty jars into the lib folder of your respective web server and you should be good to go.

Answer 2

How long running are these queries?

If you're talking about sessions expiring, perhaps it's best for the user not to be waiting around for it. It will always be annoying for the user to wait 20 minutes peaking every so often in that query's tab.

So, if really long queries are the case, perhaps it's best to change the UI approach and have the user "order" a query that he (she) later comes back to view, or is even notified by mail when it's ready.

In such a case I'd have the query prepared in the DB and cached, in a separate table, there. Meaning, I'd have the web server work once to register the request, have a DB task or a separate process/server prepare queries upon requests and notify the user, and then have the web server display them when the user comes back for the results.

Answer 3

I would do a combination of your so called "bad solution" and the "j2ee solution":

1. The original UI thread sends a asynchronous JMS message to the "backend" and returns.
2. The backend receives the asynchronous request and processes it.
3. When a result (or error) is reached in the backend, it is returned to the controller layer.
4. The UI, still doing AJAX polling or using Bayeux/Cometed, receives and displays the result.

The trick is to match the request / response pair. I would do it like this:

1. Create a "AsyncManagerService" (AMS) that has SESSION, maybe even APPLICATION wide scope so all threads talk to the same instance.
2. The AMS holds a map with an id as key and any object as value.
3. When creating the request JMS message, generate a unique, random key and put it into the jmsCorrelationId of the message as well as into the map, with NULL as a value. Also pass that id to the UI.
4. Let the UI thread poll the AMS with the previously generated id as long as the value in the map is NULL.
5. When the result is ready, let your JMS receiver put it into the AMS' map at the given id.
6. Next time the UI thread polls the map, it will receive the answer and stop polling.

This is clean and well abstracted from any concrete domain. A purely technical solution.

Even if you don't like polling, HTTP is stateless by design and I think this way polling only happens at well defined intervals.

Anyway, I implemented a system with exactly this pattern and it runs just fine...

Answer 4

Show a message to the user that "Your request is accepted and will take an hour to get updated"

You create a table which stores all these transactions and process these in a batch on server.

User would not have to wait for long and he will be happy to see the message. You can send a confirmation email once your the transaction is processed.

This is the best solution I think.