Menu
▾
▴
Re: [JBoss-user] Re: multiple concurent DB connections
|
From: Michael J. <Mic...@gt...> - 2001-07-03 20:45:08
|
You have a very good point. If your business methods need to be in a
transaction, my solution may not work for you.
In my case, I had one or two read-only business methods which were accessed
most often. My problems occurred during authentication/authorization to
execute these methods (because the login module would hit the database every
so often.) It looked like the login module was inheriting my business
methods' transaction attributes, so my retry loop always failed. Once I
turned off transactions to these methods (via the deployment descriptor,
"<trans-attribute>NotSupported</trans-attribute>",) the retries started
working. This worked for me because they were read-only anyway.
If you need transactions on your business methods, that makes it harder. It
would be messy, but you could try proxying your transacted business methods
through a stateless session bean with non-transacted methods. You would
create a stateless session bean mirroring the API of the bean you were
trying to call. This stateless session bean's business methods would be
deployed with trans-attribute=NotSupported. The bean you're trying to call
could have business methods deployed with trans-attribute=Required or
Mandatory. Each of the methods would call the "real" bean's method inside a
try/catch block, looping if the call failed.
You might be able to bypass the connection pool, creating your own
connection? That sounds like a hack. I'm afraid my JDBC knowledge is
pretty weak, so I don't know a lot about what you can and can't do there.
I beleive this to be a fairly fundamental problem though: if your connection
fails inside a transaction, your whole transaction must be rolled back. If
you weren't using EJB, you would simply perform your try/catch/loop outside
of the transaction, retrying the whole transaction each time. I think the
closest thing to this would be the proxy session bean as I described above.
Mike
----- Original Message -----
From: "vcp" <ed...@vc...>
To: <jbo...@li...>
Sent: Tuesday, July 03, 2001 11:03 AM
Subject: [JBoss-user] Re: multiple concurent DB connections
> Mike, first of all thanks for your response.
>
> I read your message and I would have some questions concerning it:
>
> - how did you turn off transactions for the method responsible for DB
> connection?
> - have you used transaction attributes in this case or maybe some
different
> solution?
>
> I see here following problem - for business method I can use transaction
> attributes. Thus if I create business method that implements establishing
DB
> connection. I can switch off transactions for that method. However if I
call
> such method (not transacted) from another business method, still it will
be
> included in the transaction of calling method. Thus this way doesn't seem
to
> be a good solution especially in case of getting DB connection which is
used
> in many transacted business methods.
>
> Therefore please say sth more about your solution.
>
> Edka
>
> > From: "Michael Jara" <Mic...@gt...>
> > To: <jbo...@li...>
> > Subject: Re: [JBoss-user] multiple concurent DB connections
> > Date: Mon, 2 Jul 2001 15:12:47 -0600
> > Reply-To: jbo...@li...
> >
> > I've been trying to solve a problem similar to this. In my case I'm
using
> > Sybase and a database JAAS login module. If I leave my app client
running
> > for several hours, which periodically polls the server via a SOAP
servlet
> > (login-poll-logout), I eventually get an auth failed message followed by
a
> > "connection closed" message from the JDBC driver.
> >
> > I have a loop similar to yours for my JDBC access, and I have the same
> > problem. I haven't found a complete solution yet, but I think the
problem
> > relates to transactions. You cannot get more than one database
connection
> > within a transaction, so if you have network trouble during the
> transaction,
> > everything in that transaction will fail / roll back.
> >
> > To test this, I wrote a simple method which does some JDBC access (using
> my
> > try/catch/loop code.) When the bean was transacted (by default), the
> method
> > would always fail on network interruption. When I turned transactions
off
> > for that method, my loop managed to get a new connection and continue.
> >
> > So for read-only JDBC access inside an EJB, you have a simple solution.
> > Read-write would be a little more difficult. I'm not quite sure if this
> is
> > my solution, because as far as I can tell, authentication doesn't occur
> > within a transaction! So, I'm still working on it.
> >
> > Mike
> >
> > ----- Original Message -----
> > From: "vcp" <ed...@vc...>
> > To: <jbo...@li...>
> > Sent: Monday, July 02, 2001 11:08 AM
> > Subject: [JBoss-user] multiple concurent DB connections
> >
> >
> > > Hi,
> > >
> > > At the very beginning - sorry for the size of this message but I
wanted
> to
> > > present all things I did to solve the problem I met.
> > >
> > > >From some time I've been trying to solve the problem with DB
connection
> > and
> > > more precisly with multiple concurent DB connections. So
> > > far I've tried both JDBC pool and connection manager. Both are working
> OK
> > > for some time (about an hour or two) and then most
> > > connections taken from the pool are not valid. Before I describe the
> > details
> > > I must mention that everything works fine with
> > > small number of DB connections - such tests of EJB application, I'm
> > working
> > > on, were running for more than 48 hours without
> > > any problems. And here are the details concerning the problem:
> > >
> > >
> >
>
****************************************************************************
> > > **********************
> > > - when I used JDBC pool with the following configuration:
> > > ***************************************
> > > <mbean code="org.jboss.jdbc.XADataSourceLoader"
> > > name="DefaultDomain:service=XADataSource,name=MSSQLDB">
> > > <attribute
> > >
> >
>
name="DataSourceClass">org.jboss.pool.jdbc.xa.wrapper.XADataSourceImpl</attr
> > > ibute>
> > > <attribute name="PoolName">MSSQLDB</attribute>
> > > <attribute name="Properties">user=xxx;password=xxx</attribute>
> > > <attribute
> > >
> name="URL">jdbc:weblogic:mssqlserver4:xx...@xx...:1433</attribute>
> > > <attribute name="GCMinIdleTime">1200000</attribute>
> > > <attribute name="MaxSize">50</attribute>
> > > <attribute name="GCEnabled">true</attribute>
> > > <attribute name="InvalidateOnError">true</attribute>
> > > <attribute name="TimestampUsed">true</attribute>
> > > <attribute name="Blocking">true</attribute>
> > > <attribute name="GCInterval">120000</attribute>
> > > <attribute name="IdleTimeout">2800000</attribute>
> > > <attribute name="IdleTimeoutEnabled">true</attribute>
> > > <attribute name="LoggingEnabled">true</attribute>
> > > <attribute name="MaxIdleTimeoutPercent">1.0</attribute>
> > > <attribute name="MinSize">1</attribute>
> > > </mbean>
> > >
> > > the exceptions received look like this:
> > >
> > > ///////////////////////////////////////////////
> > > [MSSQLDB] Pool MSSQLDB [10/10/50] gave out pooled object:
> > > org.jboss.pool.jdbc.xa.wrapper.XAConnectionImpl@3a6ecc
> > > [Default] user.dao: get connection
> > > [Default] alert.dao: closing connection
> > > [Default] Transaction exception:
> com.vitalcontact.vcp.data.DataException:
> > > javax.ejb.FinderException: SQLException while
> > > checking for an existing alert - name -> 6722655_31-alt :
> > > weblogic.jdbcbase.mssqlserver4.TdsException: I/O exception while
> > > talking to the server, java.net.SocketException: Connection reset by
> peer:
> > > socket write error
> > > [Default] user: store
> > > [Transact] XAException: tx=XidImpl [FormatId=257, GlobalId=smart//212,
> > > BranchQual=] errorCode=XA_UNKNOWN(0)
> > > [MSSQLDB] Pool MSSQLDB [9/9/50] destroyed object
> > > org.jboss.pool.jdbc.xa.wrapper.XAConnectionImpl@387202.
> > > [Default] user.dao: closing connection
> > > [Default] lang-context: get user model
> > > [Default] user: activate
> > > [Default] user: load
> > > [Default] user.dao: queryString is: SELECT
> > > usr_id,nms_id,usr_login,usr_password,usr_xml_def FROM VC_USERS WHERE
> > usr_id
> > > = '535'
> > > [Default] user.dao: get connection
> > > [Default] Fri Jun 29 03:03:39 EDT 2001: worker-6: transaction
exception:
> > > javax.transaction.TransactionRolledbackException:
> > > null; nested exception is: javax.ejb.EJBException
> > > javax.ejb.EJBException at
> > >
> >
>
com.vitalcontact.vcp.transaction.ejb.TransactionEJB.executeTransaction(Trans
> > > actionEJB.java:244)
> > > at java.lang.reflect.Method.invoke(Native Method)
> > > at
> > >
> >
>
org.jboss.ejb.StatelessSessionContainer$ContainerInterceptor.invoke(Stateles
> > > sSessionContainer.java:543)
> > > at
> > >
> >
>
org.jboss.ejb.plugins.StatelessSessionInstanceInterceptor.invoke(StatelessSe
> > > ssionInstanceInterceptor.java:87)
> > > at
> > >
> >
>
org.jboss.ejb.plugins.TxInterceptorCMT.invokeNext(TxInterceptorCMT.java:133)
> > > at
> > >
> >
>
org.jboss.ejb.plugins.TxInterceptorCMT.runWithTransactions(TxInterceptorCMT.
> > > java:298)
> > > at
> > org.jboss.ejb.plugins.TxInterceptorCMT.invoke(TxInterceptorCMT.java:99)
> > > at
> > >
> >
>
org.jboss.ejb.plugins.SecurityInterceptor.invoke(SecurityInterceptor.java:12
> > > 7)
> > > at
org.jboss.ejb.plugins.LogInterceptor.invoke(LogInterceptor.java:195)
> > > at
> > >
> >
>
org.jboss.ejb.StatelessSessionContainer.invoke(StatelessSessionContainer.jav
> > > a:286)
> > > at
> > >
> >
>
org.jboss.ejb.plugins.jrmp.server.JRMPContainerInvoker.invoke(JRMPContainerI
> > > nvoker.java:480)
> > > at
> > >
> >
>
org.jboss.ejb.plugins.jrmp.interfaces.GenericProxy.invokeContainer(GenericPr
> >
> > > oxy.java:335)
> > > at
> > >
> >
>
org.jboss.ejb.plugins.jrmp.interfaces.StatelessSessionProxy.invoke(Stateless
> > > SessionProxy.java:123)
> > > at $Proxy10.executeTransaction(Unknown Source)
> > > at
> > >
> >
>
com.vitalcontact.vcp.engine.ThreadPool$WorkerThread.processAlertEvent(Thread
> > > Pool.java:183)
> > > at
> > >
> >
>
com.vitalcontact.vcp.engine.ThreadPool$WorkerThread.run(ThreadPool.java:134)
> > > //////////////////////////////////////////////
> > >
> > > or like this:
> > > /////////////////////////////////////////////
> > > [MSSQLDB] Pool MSSQLDB [5/10/50] gave out pooled object:
> > > org.jboss.pool.jdbc.xa.wrapper.XAConnectionImpl@7140d0
> > > [Default] user.dao: get connection
> > > [Default] user.dao: closing connection
> > > [Default] Transaction exception:
> > com.vitalcontact.vcp.lang.ExecuteException:
> > > lang context initialization failed:
> > > javax.ejb.FinderException: SQLException while checking for an existing
> > > user - name -> loadTest4 :
> > > weblogic.jdbcbase.mssqlserver4.TdsException: I/O exception while
talking
> > to
> > > the server, java.net.SocketException: Connection
> > > reset by peer: JVM_recv in socket input stream read
> > > [Transact] XAException: tx=XidImpl [FormatId=257, GlobalId=smart//256,
> > > BranchQual=] errorCode=XA_UNKNOWN(0)
> > > ////////////////////////////////////////////// etc..
> > >
> > > These exceptions are generated during SQL query execution on the
> > connections
> > > received from the pool
> > > (dbConncetion.executeQuery(...)). I am aware that DB can close its
> > > connections and the app server is not informed about that.
> > > In such situation calling isClosed() on such connection (closed by the
> DB)
> > > will return false because noone called close() on
> > > this connection earlier. In order to avoid that I made a loop that
tests
> > the
> > > connection (through the execution of simple SQL
> > > query) and closes it (dbConnection.close()) in case of SQL exception
> > > appearance:
> > >
> > > ////////////////////////////////////////////
> > > ...
> > > while (true) {
> > > try {
> > > dbConnection = dataSource.getConnection();
> > > Debug.printEvent("Get-Connection Loop: connection received",
> > > "Connection Reference: " + dbConnection);
> > > try {
> > > Statement stmt = dbConnection.createStatement();
> > > ResultSet result = stmt.executeQuery(queryStr);
> > > stmt.close();
> > > } catch (Exception e) {
> > > if(!dbConnection.isClosed()) {
> > > Debug.println("### BEFORE CLOSING THE CONNECTION ###");
> > > dbConnection.close();
> > > Debug.println("### AFTER CLOSING THE CONNECTION ###");
> > > }
> > > throw new Exception("Invalid connection !!!");
> > > }
> > >
> > > } catch(Exception e) {
> > > Debug.printEvent("Get-Connection Loop ONLY: ", "connection
> > > invalid - loop number: "+loopNo);
> > > loopNo+=1;
> > > file://e.printStackTrace();
> > > continue;
> > > }
> > > Debug.printEvent("Get-Connection Loop ONLY: ", "connection
> valid -
> > > loop number: "+loopNo);
> > > break;
> > > }
> > > return dbConnection;
> > > //////////////////////////////////////////
> > >
> > > As you can see I assumed here that after one or few attempts new
> > connection
> > > received from the pool will be valid. However my
> > > assumption was wrong because either after few or thousands of
attempts,
> > > valid connection to the DB was not established. It
> > > looks like each time JDBC pool returns the reference to the same
> physical
> > > connection (inside this loop there were no logs
> > > saying that DB connetion is gave out from the pool or returned to the
> pool
> > > or distroyed).
> > > (I tried also different numbers of MaxSize attribute included in JDBC
> pool
> > > configuration like 100, 1000, 10000 but with no
> > > influence on results.)
> > >
> > >
> >
>
****************************************************************************
> > > **********************
> > > - when I used connection manager with the following configuration
> (instead
> > > of JDBC pool):
> > > ***************************************
> > > ///////////////////////////////////////////
> > > <mbean code="org.jboss.resource.ConnectionFactoryLoader"
> > > name="JCA:service=ConnectionFactoryLoader,name=MSSQLDB">
> > > <attribute name="FactoryName">MSSQLDB</attribute>
> > > <attribute
> name="RARDeployerName">JCA:service=RARDeployer</attribute>
> > > <attribute name="ResourceAdapterName">
> > > Minerva JDBC LocalTransaction ResourceAdapter
> > > </attribute>
> > > <attribute name="Properties">
> > >
> > >
> >
>
ConnectionURL=jdbc:weblogic:mssqlserver4:xx...@xx...:1433;user=xxx;p
> > > assword=xxx
> > > </attribute>
> > >
> > > <attribute name="ConnectionManagerFactoryName">
> > > MinervaSharedLocalCMFactory
> > > </attribute>
> > >
> > > <attribute name="ConnectionManagerProperties">
> > > # Pool type - uncomment to force, otherwise it is the default
> > > #PoolConfiguration=per-factory
> > >
> > > # Connection pooling properties - see
> > > # org.jboss.pool.PoolParameters
> > > MinSize=1
> > > MaxSize=10000
> > > Blocking=false
> > > GCEnabled=true
> > > IdleTimeoutEnabled=true
> > > InvalidateOnError=true
> > > TrackLastUsed=tue
> > > GCIntervalMillis=120000
> > > GCMinIdleMillis=1200000
> > > IdleTimeoutMillis=2800000
> > > MaxIdleTimeoutPercent=1.0
> > > LoggingEnabled=true
> > > TimestampUsed=true
> > > </attribute>
> > >
> > > <attribute name="PrincipalMappingClass">
> > > org.jboss.resource.security.ManyToOnePrincipalMapping
> > > </attribute>
> > > <attribute name="PrincipalMappingProperties">
> > > userName=xxx
> > > password=xxx
> > > </attribute>
> > > </mbean>
> > > /////////////////////////////////////////
> > >
> > > received results were similar. After about 170 connections ([MSSQLDB]
> Pool
> > > org.jboss.pool.connector.jdbc.JDBCManagedConnectionFactory-1
> > > [170/170/Unlimited]) similar/the same exceptions, as presented above,
> are
> > > generated.
> > > I tried to overwrite default settings of unlimited number of
connections
> > > opened to the database but without success. I can't
> > > find good way to define "ConnectionManagerProperties" (I tried few) to
> > > overwrite default settings from
> > > org.jboss.pool.PoolParameters.java . I tried also to download
snapshots
> of
> > > the sources (jbosspool-cvs.zip and jboss-cvs.zip)
> > > in order to change the pool parameters directly in the code and then
> > compile
> > > and run JBOSS server. Unfortunatelly after any
> > > change of pool parameters in org.jboss.pool.PoolParameters.java, JBOSS
> > > server did not start correctly (some
> > > java.nullPointerExceptions were generated) and the connection to the
DB
> > was
> > > not established even once. When I returned to the
> > > default settings of pool parameters, JBOSS started OK and the
situation
> > was
> > > the same as described above.
> > > In case of connection manager I've got also the problem to force JBOSS
> to
> > > bind the pool in JNDI before the servlet starts to
> > > work.
> > >
> >
>
****************************************************************************
> > >
> > > Now my questions are as follows:
> > >
> > > Why can't I receive valid connection from JDBC pool by using the loop
> > > presented above ?
> > > How to overwrite default settings of the connection manager ?
> > > How to force JBOSS first to create the pool and bind it in JNDI and
then
> > to
> > > start the servlet ?
> > > Is the aproach I took to solve this problem correct ?
> > > Maybe someone had similar problem and found out sth helpful ?!
> > >
> > > Please help me with this problem and of course thanks in advance.
> > >
> > > Best regards,
> > > Edyta Kalka
> > >
> > >
>
>
>
> _______________________________________________
> JBoss-user mailing list
> JBo...@li...
> http://lists.sourceforge.net/lists/listinfo/jboss-user
>
|