CODEWITHSUNDEEP
javajdbctransactionsconnection-poolingrollback
SPIRiT_1984
SPIRiT_1984

Reputation: 2787

Should the connection be closed after each query in a long time transaction?

We are currently dealing with the function, that has to work partially with the database, and partially with a service, whice operations are time-consuming. So, generally speaking, here is a transactional method, that has a code like this:

Connection conn = null;
try {
  conn = getConnection(); // This I get from the connection pool
  Employee emp = queryDatabase(id); 
  // Point A - ??
  Response resp = makeLongTimeServiceCall(emp);
  // Point B - ??
  anotherQueryDatabase(resp);

} catch (Exception e) {
  throw e; // And this also rolls back the transaction
} finally {
  // If conn is not closed, close it, so there would be no leaks
}

So the big question is - should I close the connection at point A and then get it again from the connection pool at point B, so that other servlets could use that connection while I interact with the service? My guess is that I should, but will this hurt my transaction?

Upvotes: 4

Views: 9978

Answers (4)

MxR
MxR

Reputation: 21

if your application doesn't close connection properly may lead to some issues like the connection pool maxing out.

Applications Not Properly Closing Connections:

When writing an application to use the WebSphere Application Server(WAS) datasource, the best way is fallowing get/use/close pattern.

1.get

-This is when the application makes a request to get a connection from the pool.

The application will first lookup the datasource name and then do a getConnection() call.

2.use

-This is when the application executes a query and waits for a response

3.close

-This is the final stage when the application has received the response from the database and is now done using that connection.

-The application should call close() in a finally block to close out the connection and return it to the free pool.

If your application does not follow this pattern, you may see connections staying open for long periods of time. This is especially seen when the application does not close the connection properly. After the response is received from the database, the application should be calling close in a finally block. If the application does not explicitly close the connection, that connection will stay open forever in WAS until that server is restarted. This is known as a connection leak. To resolve this type of issue, you will have to modify your application to close every connection

for further information: https://www.ibm.com/support/pages/common-reasons-why-connections-stay-open-long-period-time-websphere-application-server

Upvotes: 1

gerrytan
gerrytan

Reputation: 41143

In many circumstances: yes, closing and reopening the connection sounds good. However you need to understand the implication of doing them in two separate transactions (by closing and re-opening the connection you are inherently doing them in separate transaction).

If another user invokes an operation that changes the state of your data at Point B will the end result still be correct?

Upvotes: 1

Mahbub Moon
Mahbub Moon

Reputation: 511

Database connections shouldn't be left open. Open the connections just when you need to execute a query and close it as early as possible. So my solution is, YES.

Upvotes: 2

Davide Lorenzo MARINO
Davide Lorenzo MARINO

Reputation: 26946

If you don't have a transaction you can close the connection and ask for a new connection when needed.

Remember that if you are using a connection pool closing a connection will not really close the connection, but only flag it as reusable by other threads.

Upvotes: 0

Related Questions