Search code examples
javadatasource

How does the DataSource.getConection() and Connection.close() works?

I don't really understand how the DataSource/Connection works in Java.

Here are the classes I use :

import javax.naming.InitialContext;
import javax.sql.DataSource;
import java.sql.Connection;

Here is a code sample. It's not my real code, but a simple example to explain my problem :

InitialContext context = new InitialContext();
DataSource dataSource1 = (DataSource) context.lookup(JNDI_ORACLE_1);
dataSource1.getConnection().close();
DataSource dataSource2 = (DataSource) context.lookup(JNDI_ORACLE_2);
dataSource2.getConnection();

The way I think it should work :

  • I initialize a first DataSource
  • I open a connection with this DataSource and I close it immediately. Closing the connection should alow other connections to be oppenned.
  • I initialize a second DataSource
  • I open a connection to the second DataSource, with the first connection already closed it should be able to open this new connection without any problem.

But Instead :

When the second getConnection() is called I got a "java.lang.IllegalStateException: Local transaction already has 1 non-XA Resource: cannot add more resources."

It looks like something about the first connection isn't over yet, and I can't figure out why.

What did I miss here ?

Edit

More information about the DataSource configuration

Both of the DataSource are configured in the "JDBC Connection Pools" of Glassfish with the same parameters :

General Settings Pool Settings and Transaction

Solution

  • WebSphere app Server is creating an LTC (Local Transaction Containment) for you that controls the transactional behavior of your app. Although not shown in your sample, I suspect your code is in a servlet or ejb and thus these containers are creating and controlling the lifecycle of the LTC for you. When you lookup and then use the second datasource within the scope of the LTC, it attempts to enroll it in the LTC. Enrolling/enlisting multiple datasources in a transaction requires the use of XA capable datasources, and it appears that that your datasources are not defined as being XA capable, thus the exception. To get started on understanding how transactions and XA-capable datasources work in WebSphere App Sever, see this IBM Knowledge Center topic and the subtopics under it.