XA Connection error

[smithbstl]

I have an XA datasource set up and I am using JDBC. Everytime I try to close my connection object, I get this error log

[org.jboss.resource.connectionmanager.TxConnectionManager] Error during tidyup org.jboss.resource.connectionmanager.TxConnectionManager$TxConnectionEventListener@11e1813[state=NORMAL mc=org.jboss.resource.adapter.jdbc.xa.XAManagedConnection@192b12d handles=0 lastUse=1157571656156 permit=true trackByTx=false mcp=org.jboss.resource.connectionmanager.JBossManagedConnectionPool$OnePool@1ae0436 context=org.jboss.resource.connectionmanager.InternalManagedConnectionPool@196a753 xaResource=org.jboss.resource.adapter.jdbc.xa.XAManagedConnection@192b12d txSync=null]
org.jboss.resource.JBossResourceException: xa tx only!
 at org.jboss.resource.adapter.jdbc.xa.XAManagedConnection.getLocalTransaction(XAManagedConnection.java:80)
 at org.jboss.resource.connectionmanager.TxConnectionManager$TxConnectionEventListener.tidyup(TxConnectionManager.java:657)
 at org.jboss.resource.connectionmanager.BaseConnectionManager2.returnManagedConnection(BaseConnectionManager2.java:336)
 at org.jboss.resource.connectionmanager.TxConnectionManager$TxConnectionEventListener.connectionClosed(TxConnectionManager.java:623)
 at org.jboss.resource.adapter.jdbc.BaseWrapperManagedConnection.closeHandle(BaseWrapperManagedConnection.java:266)
 at org.jboss.resource.adapter.jdbc.WrappedConnection.close(WrappedConnection.java:129)
 at com.stlouiscity.database.util.JDBCUtil.close(JDBCUtil.java:121)
 at com.stlouiscity.budget.database.dao.AccountDAOJDBCImpl.findAll(AccountDAOJDBCImpl.java:98)
 at com.stlouiscity.budget.jsf.delegates.AccountService.fillAccountList(AccountService.java:57)
 at com.stlouiscity.budget.jsf.delegates.AccountService.getAccounts(AccountService.java:41)
 at com.stlouiscity.budget.jsf.beans.AccountBacking.listAccounts(AccountBacking.java:91)
 at com.stlouiscity.budget.jsf.beans.AccountBacking.<init>(AccountBacking.java:49)
 at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
 at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:39)
 at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:27)
 at java.lang.reflect.Constructor.newInstance(Constructor.java:494)
 at java.lang.Class.newInstance0(Class.java:350)
 at java.lang.Class.newInstance(Class.java:303)
 at org.apache.myfaces.util.ClassUtils.newInstance(ClassUtils.java:274)
 at org.apache.myfaces.util.ClassUtils.newInstance(ClassUtils.java:265)
 at org.apache.myfaces.config.ManagedBeanBuilder.buildManagedBean(ManagedBeanBuilder.java:49)
 at org.apache.myfaces.el.VariableResolverImpl.resolveVariable(VariableResolverImpl.java:311)
 at oracle.adfinternal.view.faces.el.AdfFacesVariableResolver.resolveVariable(AdfFacesVariableResolver.java:40)
 at org.apache.myfaces.el.ValueBindingImpl$ELVariableResolver.resolveVariable(ValueBindingImpl.java:569)
 at org.apache.commons.el.NamedValue.evaluate(NamedValue.java:124)
 at org.apache.commons.el.ComplexValue.evaluate(ComplexValue.java:140)
 at org.apache.myfaces.el.ValueBindingImpl.getValue(ValueBindingImpl.java:378)
 at org.apache.myfaces.application.ApplicationImpl.createComponent(ApplicationImpl.java:425)
 at javax.faces.webapp.UIComponentTag.createComponentInstance(UIComponentTag.java:437)
 at javax.faces.webapp.UIComponentTag.findComponent(UIComponentTag.java:391)
 at javax.faces.webapp.UIComponentTag.doStartTag(UIComponentTag.java:219)
 at oracle.adf.view.faces.webapp.UIXComponentTag.doStartTag(UIXComponentTag.java:85)
 at org.apache.jsp.AccountList_jsp._jspx_meth_af_table_0(AccountList_jsp.java:333)
 at org.apache.jsp.AccountList_jsp._jspx_meth_h_form_0(AccountList_jsp.java:306)
 at org.apache.jsp.AccountList_jsp._jspx_meth_afh_body_0(AccountList_jsp.java:280)
 at org.apache.jsp.AccountList_jsp._jspx_meth_f_view_0(AccountList_jsp.java:162)
 at org.apache.jsp.AccountList_jsp._jspService(AccountList_jsp.java:108)
 at org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:97)
 at javax.servlet.http.HttpServlet.service(HttpServlet.java:810)
 at org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:332)
 at org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:314)
 at org.apache.jasper.servlet.JspServlet.service(JspServlet.java:264)
 at javax.servlet.http.HttpServlet.service(HttpServlet.java:810)
 at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:252)
 at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:173)
 at org.apache.catalina.core.ApplicationDispatcher.invoke(ApplicationDispatcher.java:672)
 at org.apache.catalina.core.ApplicationDispatcher.processRequest(ApplicationDispatcher.java:463)
 at org.apache.catalina.core.ApplicationDispatcher.doForward(ApplicationDispatcher.java:398)
 at org.apache.catalina.core.ApplicationDispatcher.forward(ApplicationDispatcher.java:301)
 at org.apache.myfaces.context.servlet.ServletExternalContextImpl.dispatch(ServletExternalContextImpl.java:415)
 at org.apache.myfaces.application.jsp.JspViewHandlerImpl.renderView(JspViewHandlerImpl.java:234)
 at oracle.adfinternal.view.faces.application.ViewHandlerImpl.renderView(ViewHandlerImpl.java:157)
 at org.apache.myfaces.lifecycle.LifecycleImpl.render(LifecycleImpl.java:352)
 at javax.faces.webapp.FacesServlet.service(FacesServlet.java:107)
 at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:252)
 at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:173)
 at oracle.adfinternal.view.faces.webapp.AdfFacesFilterImpl._invokeDoFilter(AdfFacesFilterImpl.java:367)
 at oracle.adfinternal.view.faces.webapp.AdfFacesFilterImpl._doFilterImpl(AdfFacesFilterImpl.java:336)
 at oracle.adfinternal.view.faces.webapp.AdfFacesFilterImpl.doFilter(AdfFacesFilterImpl.java:196)
 at oracle.adf.view.faces.webapp.AdfFacesFilter.doFilter(AdfFacesFilter.java:87)
 at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:202)
 at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:173)
 at org.jboss.web.tomcat.filters.ReplyHeaderFilter.doFilter(ReplyHeaderFilter.java:96)
 at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:202)
 at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:173)
 at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:213)
 at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:178)
 at org.jboss.web.tomcat.security.SecurityAssociationValve.invoke(SecurityAssociationValve.java:175)
 at org.jboss.web.tomcat.security.JaccContextValve.invoke(JaccContextValve.java:74)
 at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:126)
 at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:105)
 at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:107)
 at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:148)
 at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:869)
 at org.apache.coyote.http11.Http11BaseProtocol$Http11ConnectionHandler.processConnection(Http11BaseProtocol.java:664)
 at org.apache.tomcat.util.net.PoolTcpEndpoint.processSocket(PoolTcpEndpoint.java:527)
 at org.apache.tomcat.util.net.MasterSlaveWorkerThread.run(MasterSlaveWorkerThread.java:112)
 at java.lang.Thread.run(Thread.java:595)

Most of that garbage is JSF MyFaces stuff but the section at the top is relavant.

Here is my datasource

<datasources>
 <xa-datasource>
 <jndi-name>XAOracleDS</jndi-name>
 <track-connection-by-tx/>
 <isSameRM-override-value>false</isSameRM-override-value>
 <xa-datasource-class>oracle.jdbc.xa.client.OracleXADataSource</xa-datasource-class>
 <xa-datasource-property name="URL">jdbc:oracle:thin:@XXX.XXX.X.XXX:XXXX:ora1</xa-datasource-property>
 <xa-datasource-property name="User">budget_access</xa-datasource-property>
 <xa-datasource-property name="Password">budget_access</xa-datasource-property>
<exception-sorter-class-name>org.jboss.resource.adapter.jdbc.vendor.OracleExceptionSorter</exception-sorter-class-name>
 <no-tx-separate-pools/>
 <!--pooling parameters-->
 <min-pool-size>1</min-pool-size>
 <max-pool-size>100</max-pool-size>
 <blocking-timeout-millis>5000</blocking-timeout-millis>
 <idle-timeout-minutes>15</idle-timeout-minutes>
 <metadata>
 <type-mapping>Oracle9i</type-mapping>
 </metadata>
 </xa-datasource>

 <mbean code="org.jboss.resource.adapter.jdbc.vendor.OracleXAExceptionFormatter"
 name="jboss.jca:service=OracleXAExceptionFormatter">
 <depends optional-attribute-name="TransactionManagerService">jboss:service=TransactionManager</depends>
 </mbean>

</datasources>

This is how I am retrieving the connection

public static Connection getConnection()
 throws SQLException
 {
 DataSource ds = null;
 Connection connection = null;
 boolean exceptionRaised = false;
 ds = ServiceLocator.getDataSource("java:comp/env/jdbc/XAOracleDS");
 connection = ds.getConnection();
 connection.setAutoCommit( false );
 return connection;
 }

Here is how I am closing it

public static void close ( Connection connection )
 {
 try
 {
 if( connection != null )
 connection.close();
 }
 catch ( SQLException sqle ) {
 System.out.println(sqle.toString());
 }
 }

[weston.price]

Out of curiosity, why do you think you need to do

connection.setAutoCommit( false );

Hint: This is your problem...but I would really like to know why you think this is necessary.

[smithbstl]

I it is an artifact from before I was in a managed environment without JTA/JCA and was using JDBC only. I just never took it out.

That looks like it did the trick, thanks for your help.

Can you give me some more details as to why autoCommit was causing problems.

Again, I appreciate your help.

[smithbstl]

Ok, now I am getting this message when I try to issue a commit

2006-09-06 15:49:46,828 ERROR [STDERR] java.sql.SQLException: You cannot commit with autocommit set!

I can't find anywhere where I enabled AutoCommit. Do not need to issue commits under JTA/JCA?

[weston.price]

You are using the XA datasource improperly. XA means that you want JBoss to manage transactions for you.

Do you want to manage the transactions yourself? If so, you would want to look at using a no-tx-datasource and do the rest yourself.

For what you have described, and looking at your stacktrace, this is probably the better bet for you at this point.

[weston.price]

Sorry, should have been more clear:

If you want JBoss to manage the transactions for you (CMT) or you are using the UserTransaction object from JNDI then you would use either use an XA or LocalTransaction datasource. Using these DS/CM combinations requires you to remove the explicit setAutoCommit(), commit() method from your code as JBoss is controlling the transaction context at that point.

[smithbstl]

I was under the impression that connections and tranasctions were not the same animal. What I am doing is gaining a connection from the datasource looked up from JNDI, issueing an update through that connection, committing the update and closing the connection

public void update(AccountBean acctBean){
 Connection conn = null;
 PreparedStatement ps = null;
 int result = 0;
 String sql = "Update Budget.Account Set Acct_Num = ?, Acct_Name =?" +
 " Where Acct_ID = ?";
 try {
 conn = JDBCUtil.getConnection();
 ps = conn.prepareStatement(sql);
 ps.setString(1,acctBean.getAcctNum());
 ps.setString(2,acctBean.getAcctName());
 ps.setInt(3,acctBean.getAcctId());
 result = ps.executeUpdate();
 conn.commit();
 } catch (SQLException e) {
 System.err.println(e.toString());
 } finally {
 JDBCUtil.close(ps);
 JDBCUtil.close(conn);
 }
 }

JDBCUtil

public static Connection getConnection()
 throws SQLException
 {
 DataSource ds = null;
 Connection connection = null;
 boolean exceptionRaised = false;
 ds = ServiceLocator.getDataSource("java:comp/env/jdbc/XAOracleDS");
 connection = ds.getConnection();
 return connection;
 }

How would this look if I was letting JBoss manage the transaction?

I thought the only difference between TX and XA was the two phase commit?

Thanks for your patience and help.

[smithbstl]

Ok, I got it, I just need to let JBoss handle the commits as well. Thanks for your help and patience in explaining this to me. I am still new the JBoss (and really J2EE) game so I still have a great deal to learn. Thanks again.

-Brian

[weston.price]

Out of curiosity, is there something in your code (JSF) that is starting a user transaction?

[smithbstl]

I am not really sure what you mean. I am not starting a transaction anywhere that I can find. I am just gaining connections from the datasource looked up through JNDI and returning them when finished. I though JCA/JTA handled the transaction demarcation for me.

[weston.price]

Let me see if I can explain this a bit more clearly:

In your case, it does not appear that you are leveraging CMT or using the UserTransaction object from JNDI to start/commit a transaction. If that is indeed the case, you are going to want to use the

<no-tx-datasource>

Using this requires you to do the setAutoCommit(false) and commit explicitly, otherwise, ever statement issued will be done in the context of a seperate JDBC transaction. In that scenario your *code* was correct, but the type of datasource was wrong.

If you wanted to get away from managing your own transactions and leverage J2EE transaction management there are generally two approaches:

1) Use EJB (or some other declaractive transaction technology)

2) Use the UserTransaction object from JNDI to start/commit/rollback a transaction.

Typically #2 is used with straight Web (non-EJB) applications and looks something like this:

Servlet or JSP

Context ic = new InitialContext();
UserTransaction ut =
 (UserTransaction) ic.lookup("java:comp/UserTransaction");
ut.begin();
// access resources transactionally here
ut.commit();

What we are talking about is transaction 'boundaries'. Technologies like EJB(2/3) allow you to declare transactions on method boundaries. Servlets/JSP do not, but allow access to the UserTransaction object (as can be seen above).

Note, either approach is neither 'right' or 'wrong', it's simply a matter of what your application requires. However, since you are running in a J2EE environment, CMT or UserTransaction delinated boundaries are typcially the preferred approach.

[weston.price]

To be more clear :-)

JCA never *starts* a transaction for you, it will merely enlist/delist a connection from an *existing* transaction depending upon the type of connection manager in use.

NoTxConnectionManager -- no enlist/delist
TxConnectionManager (local and xa) enlist/delist depending upon transaction state at the time of getConnection()


This is a typical assumption people make when coming from a straight JDBC environment.

[smithbstl]

Again, I appreciate you taking the time to explain all of this. I am not using EJB, so I will go with "option 2". Basically you are saying that I should wrap my JDBC inside of a "UserTransaction". This abstracts the actual database transaction from me. If I omit the UserTransaction, then the environment (JBoss) will create one anyway for each Connection I open. Are these correct assumptions?

To use the UserTransaction (interface), I need to perform a JNDI lookup like you have shown? Based on the following, the UserTransaction MBean requires no special configuration on my part and is automatically published by JBoss?

http://docs.jboss.org/jbossas/admindevel326/html/ch4.chapt.html

To use the UserTransaction interface in other places, the org.jboss.tm.usertx.server.ClientUserTransactionService MBean must be configured and started. This MBean publishes a UserTransaction implementation under the JNDI name UserTransaction. This MBean is configured by default in the standard JBoss distributions and has no configurable attributes.

Thanks again for all your help.

[weston.price]

Not a problem.

So option 2 it is :-)

If I omit the UserTransaction, then the environment (JBoss) will create one anyway for each Connection I open. Are these correct assumptions?

Nope.

In option 2, you are responsible for creating the UserTransaction and 'demarcating' the transaction boundaries (note, this is much the same as you would do with JDBC, just at a different layer (Servlet/JSP).

Option 1, EJB, gives you declarative (automatic) transaction support.

Everything is bound and ready to go in JNDI when JBoss starts up so there is nothing special you need to do in this regard. Just get the UserTransaction, start the transaction and commit/rollback the transaction when necessary.

Again, this is very much like JDBC just at a different layer.

Note, this is a primary reason people often use EJB/Spring or some other framework that automatically does the start/commit/rollback. Servlets/JSF have no such declarative transaction model though you can use other frameworks to achieve these results inside a Web environment.

Again, I am not a JSF expert so there may be some tag/configuration that will start/rollback/commit for you. You may want to take a look at the JSF doc for clarification.

[weston.price]

Note, one more word to the wise :-)

If you don't need XA (enlistment of multiple resources in a transaction) then don't use it and use a <local-tx-datasource>.

XA is typically used for things like updating multiple DB's in the same UOW, JMS and JDBC in the same transaction etc. etc.

There are examples of <local-tx-datasource> configurations in the JBOSS_ROOT/doc/examples/jca directory. For your stuff, you will want to look at oracle-ds.xml. The new *-ds.xml file would look something like

<datasources>
 <local-tx-datasource>
 <jndi-name>OracleDS</jndi-name>
 <track-connection-by-tx/>
 <driver-class>oracle.jdbc.driver.OracleDriver</driver-class>
 <user-name>x</user-name>
 <password>y</password>
 <connection-url>yoururl</connection-url>
<exception-sorter-class-name>org.jboss.resource.adapter.jdbc.vendor.OracleExceptionSorter</exception-sorter-class-name>
 <no-tx-separate-pools/>
 <!--pooling parameters-->
 <min-pool-size>1</min-pool-size>
 <max-pool-size>100</max-pool-size>
 <blocking-timeout-millis>5000</blocking-timeout-millis>
 <idle-timeout-minutes>15</idle-timeout-minutes>
 <metadata>
 <type-mapping>Oracle9i</type-mapping>
 </metadata>
 </local-tx-datasource>
 </datasources>

Obviously you will want to replace the properties in config with your own.