Database resiliency

Question

I'm designing an application that relies heavily on a database. I need my application to be resilient to short losses of connectivity to the database (network going down for a few seconds for example). What is the usual patterns that people use for these kind of problems. Is there something that I can do on the database access layer to handle gracefully a small glitch in the network connection to the db (i'm using hibernate + oracle jdbc + dbcp pool).

Answer 1

I'll assume you have hidden every database access behind a DAO or something similiar.

Now create wrappers around these DAOs, that try to call them, and in case of an exception wait a second and retry. Of course this will cause 'hanging' of the application during db-outage, but it will come back to live when the database becomes available.

If this is not acceptable you'll have to move the cut up closer to the ui layer. Consider the following approach.

User causes a request.

wrap all the request information in a message and put it in the queue.

return to the user, telling him that his request will get processed in a short time.

A worker registered on the queue will process the request, retrying when database problems exist.

Note that you are now deep in concurrency land. So you must handle things like requests referencing an entity which already got deleted.

Read up on 'eventual consistency'

Since you are using hibernate, you'll have to deal with lazy loading. An interruption in connectivity will kill your session, so for you it might be best not to use lazy loading at all, but work with detached objects.