[jboss-user] [Persistence, JBoss/CMP, Hibernate, Database] - Re: Connection with active local transaction being reused?

[brent.atkinson]

Ok, after digging a little deeper it appears that there were really multiple things going on and while they appeared to be connected they weren't. I have been able to reproduce the issue by setting up a similar (but simpler) application that uses a similarly configured datasource. I simulate the disconnected database by setting a breakpoint after the getConnection() call to get the connection and enabling an IPSec policy that blocks all traffic to the target database.

First, the sticky transaction issue was taken care of by converting from the JDBC transaction programming style to using UserTransaction. I believe (please correct me if I am wrong), this succeeds because the UserTransaction rollback when the underlying connection is closed still cleans up. The JDBC style transaction throws an exception when trying to rollback (because the connection is closed) and so whatever hooks that usually cleanup don't complete. This results in an unfinished local JTA transaction being associated with the connection. Once I changed to using JTA transaction handling using the pattern shown in the wiki at http://www.jboss.com/wiki/Wiki.jsp?page=WhatIsTheCorrectPatternForUserTransactions, I no longer get the "Unfinished local transaction" messages. This however, did not fix the problem of the closed connection still being handed out as if it was connected, effectively killing all the applications using the connection pool.

I worked around the dead connection in the pool by configuring my datasource like the following:

<datasources>
  | 	<local-tx-datasource>
  | 		<jndi-name>jdbc/AppName</jndi-name>
  | 
  | 		<connection-url>
  | 			jdbc:oracle:thin:@host:1521:DEV
  | 		</connection-url>
  | 		<driver-class>oracle.jdbc.driver.OracleDriver</driver-class>
  | 		
  | 		<!-- This checks the connections are good before handing them out -->
  | 		<valid-connection-checker-class-name>
  | 			org.jboss.resource.adapter.jdbc.vendor.OracleValidConnectionChecker
  | 		</valid-connection-checker-class-name>
  | 
  | 		<exception-sorter-class-name>
  | 			org.jboss.resource.adapter.jdbc.vendor.OracleExceptionSorter
  | 		</exception-sorter-class-name>
  | 		<metadata>
  | 			<type-mapping>Oracle9i</type-mapping>
  | 		</metadata>
  | 	</local-tx-datasource>
  | </datasources>

Once configured this way, I ran my test again and I now see this after the call to getConnection():

12:01:57,656 INFO  [SampleJTAServlet] getting connection from datasource
  | 12:01:57,656 WARN  [TxConnectionManager] Connection error occured: org.jboss.resource.connectionmanager.TxConnectionManager$TxConnectionEventListener at 1ecbac8[state=NORMAL mc=org.jboss.resource.adapter.jdbc.local.LocalManagedConnection at 6c9b2 handles=0 lastUse=1198774893984 permit=false trackByTx=false mcp=org.jboss.resource.connectionmanager.JBossManagedConnectionPool$OnePool at 1a3d58b context=org.jboss.resource.connectionmanager.InternalManagedConnectionPool at 118a7e0 xaResource=org.jboss.resource.connectionmanager.TxConnectionManager$LocalXAResource at 1b59c1b txSync=null]
  | java.sql.SQLException: pingDatabase failed status=-1
  | 	at org.jboss.resource.adapter.jdbc.vendor.OracleValidConnectionChecker.isValidConnection(OracleValidConnectionChecker.java:72)
  | 	at org.jboss.resource.adapter.jdbc.BaseWrapperManagedConnectionFactory.isValidConnection(BaseWrapperManagedConnectionFactory.java:435)
  | 	at org.jboss.resource.adapter.jdbc.BaseWrapperManagedConnection.checkValid(BaseWrapperManagedConnection.java:231)
  | 	at org.jboss.resource.adapter.jdbc.local.LocalManagedConnectionFactory.matchManagedConnections(LocalManagedConnectionFactory.java:200)
  | 	at org.jboss.resource.connectionmanager.InternalManagedConnectionPool.getConnection(InternalManagedConnectionPool.java:209)
  | 	at org.jboss.resource.connectionmanager.JBossManagedConnectionPool$BasePool.getConnection(JBossManagedConnectionPool.java:529)
  | 	at org.jboss.resource.connectionmanager.BaseConnectionManager2.getManagedConnection(BaseConnectionManager2.java:341)
  | 	at org.jboss.resource.connectionmanager.TxConnectionManager.getManagedConnection(TxConnectionManager.java:301)
  | 	at org.jboss.resource.connectionmanager.BaseConnectionManager2.allocateConnection(BaseConnectionManager2.java:396)
  | 	at org.jboss.resource.connectionmanager.BaseConnectionManager2$ConnectionManagerProxy.allocateConnection(BaseConnectionManager2.java:842)
  | 	at org.jboss.resource.adapter.jdbc.WrapperDataSource.getConnection(WrapperDataSource.java:88)
  | 	at pkg.sample.SampleJTAServlet.doGet(SampleJTAServlet.java:70)
  | 	at javax.servlet.http.HttpServlet.service(HttpServlet.java:697)
  | 	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.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.jboss.web.tomcat.tc5.jca.CachedConnectionValve.invoke(CachedConnectionValve.java:156)
  | 	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)
  | 12:01:57,671 WARN  [JBossManagedConnectionPool] Destroying connection that could not be successfully matched: org.jboss.resource.connectionmanager.TxConnectionManager$TxConnectionEventListener at 1ecbac8[state=DESTROYED mc=org.jboss.resource.adapter.jdbc.local.LocalManagedConnection at 6c9b2 handles=0 lastUse=1198774893984 permit=false trackByTx=false mcp=org.jboss.resource.connectionmanager.JBossManagedConnectionPool$OnePool at 1a3d58b context=org.jboss.resource.connectionmanager.InternalManagedConnectionPool at 118a7e0 xaResource=org.jboss.resource.connectionmanager.TxConnectionManager$LocalXAResource at 1b59c1b txSync=null]

The query then continues as normal and the transaction completes - problem(s) solved. I am still left with the question, why doesn't the connection pool cleanup the managed connection when usage of it results in a connection closed SQLException? The solution I used seems to handle it ok, but it needs to check on every access rather than lazily being handled when the exception occurs. Am I missing something obvious here?



View the original post : http://www.jboss.com/index.html?module=bb&op=viewtopic&p=4115770#4115770

Reply to the post : http://www.jboss.com/index.html?module=bb&op=posting&mode=reply&p=4115770