Database Transaction Leak in Java Application

In a real time application the Database leak occurs due to Unclosed transactions created by the programmers. The stakes are high in this case because with every connection that remains unclosed, the connection pool gets exhausted which in-turn leads to the failure of entire application. As we are unable to establish a connection to the application (login), In most cases it remains untraceable.

 Here are a few scenario’s

1. Exception occurs before commit transaction

In this case the developer performs the commit operation in try block but not within the catch block. Here the developer presumes that the particular piece of code works fine during the phase of development but fails at a later point of time due to NullPointerException which results in unclosed connection. This is one of the inevitable issue that goes by unnoticed during the development activity until and unless the exception occurs. Even-though you take measures to prevent it.

To prevent this we can include it as part of finally block either to commit or rollback the transaction.

2. Disconnected Object: 

In this case even though you close the session, the transaction object remains open unless you perform rollback or commit operation on it. The thread remains in active state or the object is alive until the particular thread is garbage collected.

Perform the commit or rollback operation before closing the session.

3. Specific catch block exception:

In some cases the developer ends up initially adding a catch block with generic Exception. Later, as part of the code review comments, the developer is asked to catch a specific exception in which case he does not add the commit or rollback operation as part of it.

Add the rollback operation as part of the new catch block.

4. Auto-commit:

This one is specific based on the design of the application by the architect, in some applications the auto-commit option is set to true by default. In this case if the developer is trying to perform a commit operation at a later point of time. It will result in a NullPointerException, since the transaction was already committed. This problem does not come into light until we reach a point where we are unable to login to the application after the predefined number of pool sessions(connections) get exhausted. eg: 100 sessions.

A complete pool release is said to be done when both connection and session are released. We can set the AutoCommit default option in try block to false, so that when the code reaches the finally block it can perform commit operation.

We can find these issues by looking into Thread dumps.

1. Thread state:

In case of no leak:

The thread consumes the transaction and if the thread is still active, it means it is working on the specified task.

In case of a leak:

The thread has moved on or thread is in idle state, but the transaction is still present then it is a leak. In this case we can look up the thread dump to know which transactions are open.

2. Obtain and Release of Object locks (Threads)

When you find a pattern wherein the pool acquires and releases it, but at a certain point the thread just borrows but does not return it as shown below is a lead to suspicious activity which needs further inspection.

If you enjoyed reading it ,you can click the like, share button and let others know about it. If you would like me to add anything else, please feel free to leave a response ??