1 2 Previous Next 17 Replies Latest reply on Nov 1, 2001 6:13 PM by Erik Sliman

    Pooled Connections closed

    Erik Sliman Apprentice

      I am using BMP and pooled connections. The connections are returned to the pool within minutes. However, it appears as though the connections are never closed once they are opened.

      The problem is that if these connections sit in the pool for awhile, the connection closes from the database side, yet the container does not know it.

      Then, the next time the bean grabs the connection, it throws a BEAN EXCEPTION when it tries to execute the query. I know the connection object itself is ok, because it prepares the statement without any problem.

      I verified with a statement right before it attempts to execute the SQL that the connection is indeed closed.

      I do not see any way to reopen the connection, even though I can detect it is closed with the isClosed() method, which would be a workaround I would be willing to accept. Is there any way to resolve this without having to put a super high timeout on database connections? Of course, even a high timeout won't solve all conceivable situations.

      Connection pools should automatically close connections that have been idle too long. Is there a reason why JBoss doesn't do this? Is this a bug? Has it been fixed since 2.4?

      If I have to work around this, then it will not be the first time. So far I've had to cripple container managed transactions with code that commits, and even had to trap and discard errors with a try and empty catch because the commits violate the spec. Are all of these noted as bugs? Have they been fixed in JBoss 3.0? I am currently using JBoss-2.4.0_Tomcat-3.2.3.

      I've assured many while trying to build support in IT for both J2EE and open source that the apps I build with JBoss will be 100% compatible with other J2EE servers so long as I adhere to the spec. These workarounds are making it hard to keep saying that. For the sake of JBoss' future, can we please take these problems seriously?

      Thanks. Help is much appreciated!

      Erik
      erik@openstandards.net

        • 1. Re: Pooled Connections closed
          Dave Elliot Newbie

          In your XADataSource MBean:

          true
          200000
          true
          1800000


          Phew, JBoss's future saved!

          • 2. Re: Pooled Connections closed
            Erik Sliman Apprentice

            These are my settings:

            true
            300000
            1800000
            true

            All this appears to do is get the connection objects back into the pool. It does not seem to close or reopen the JDBC connections.

            The problem is that the garbage collector may get the connection object back into the pool, but it may sit there for awhile (such as overnight) until the object is needed again. By this time, the database may have closed the physical connection. The container, however, assumes it is still open.

            I believe you see this reflected by the fact that the second number (total objects pooled) never goes back down. What we need is for the connection pool to destroy objects that have sat in the pool too long. When I monitored connection pooling with ADO and SQL Server, connections available in the pool would be closed if they were idle for about 3 minutes.

            • 3. Re: Pooled Connections closed
              Dave Elliot Newbie

              Odd...we had the problem with stale connections because our Oracle DS was being taken down each morning (early am) for backup... The addition of those parameters cleared up the stale conns fine...

              This on Linux 2.4/JVM 1.3.1/JBoss 2.4/Seropto Driver.

              You mention bringing a connection back into the pool...I believe this relates to the GC parameters. But the IdleTimout parameters I think do actually close idle connections to a datasource...thereby shrinking the pool to whatever minimum size you have set...

              Check the doco for yourself...

              http://www.jboss.org/documentation/HTML/ch03s06.html


              • 4. Re: Pooled Connections closed
                Erik Sliman Apprentice

                Dave,

                Offline backups was one scenario where I figured that increasing connection timeouts at the database end would not work. If you got it to work, then there must be a solution. Can you post your whole connection pooling configuration setup?

                Are you using BMP? JBoss 2.4?

                Also, did you have to deal with the container not committing as well? This is a separate problem, but you might have solved this one as well. I had to put code into the BMP method to force the commit, which means that I am effectively overriding "container managed connections" to some extent, less than ideal, and potentially problematic as the app becomes more complex. Additionally, I had to trap an error where it reported that the commit violated the spec, which seems to indicate that the solution is also out of J2EE compliance.

                I am trying to build OpenStandards.net completely on open source, and cannot tell you how glad I am to be able to implement a J2EE solution thanks to JBoss. I look forward to being able to give JBoss due credit once the site is up and running. I am hoping I can say I am 100% J2EE compliant, and thanks to JBoss, using 100% open source (except for the OS, although I plan to use Linux in the future.)

                Thanks,

                Erik
                erik@openstandards.net

                • 5. Re: Pooled Connections closed
                  Erik Sliman Apprentice

                  correction:

                  overriding "container managed connections" to some extent

                  should have said

                  overriding "container managed transactions" to some extent

                  • 6. Re: Pooled Connections closed
                    Dave Elliot Newbie

                    Erik,

                    This is my datasource MBean:


                    OracleXXX
                    org.jboss.pool.jdbc.xa.wrapper.XADataSourceImpl
                    jdbc:inetora:xxxx:nnnn:yyyy
                    user
                    password
                    true
                    200000
                    true
                    1800000
                    15
                    true



                    Keep in mind I'm using the Seropto driver with an Oracle database. (and I only use BMP).

                    I'm also using container managed transactions... It is slightly odd in that if an exception occurs and triggers a rollback during an active transaction, you only see this:

                    Application error: BMT stateless bean MyBean should
                    complete transactions before returning (ejb1.1 spec,
                    11.6.1)

                    As opposed to the actual exception... I registered that as a bug, not a show-stopper tho.

                    Soldier on, JBoss does work, only thing I haven't sorted is client managed transactions.

                    Dave


                    • 7. Re: Pooled Connections closed
                      Erik Sliman Apprentice

                      Here is an interesting twist, and a glimmer of hope. Last night I deployed, and the connections worked great. This morning, as expected, it tried to use stale connections (valid connection objects, but connection closed). Now, I hot deployed to the same JBoss instance (did not restart JBoss since last night), and it worked again. It is also clear that the connection pool did actually have to recreate the connections. Here is a snapshot of the logs:

                      Last night:

                      [MySQLDB] Pool MySQLDB [0/5/202] returned object org.jboss.pool.jdbc.xa.wrapper.XAConnectionImpl@35998f to the pool.

                      -------------------------------------
                      This morning, the problem:

                      [Default] in selectByOrgType(), connection is closed.
                      [Default] in selectByOrgType(), executing query
                      [OrganizationEJB] BEAN EXCEPTION:ejbFindByOrgType: null
                      [Default] java.rmi.ServerException: Bean exception. Notify the application administrator; nested exception is:
                      javax.ejb.EJBException: ejbFindByOrgType: null
                      [Default] javax.ejb.EJBException: ejbFindByOrgType: null
                      [Default] <<no stack trace available>>

                      After hot deploying:

                      [MySQLDB] org.jboss.pool.jdbc.xa.wrapper.XADataSourceImpl created new Connection (org.gjt.mm.mysql.jdbc2.Connection) with XAResource org.jboss.pool.jdbc.xa.wrapper.XAResourceImpl and XAConnection org.jboss.pool.jdbc.xa.wrapper.XAConnectionImpl.
                      [MySQLDB] No transaction right now.
                      [MySQLDB] Pool MySQLDB [0/0/202] gave out new object: org.jboss.pool.jdbc.xa.wrapper.XAConnectionImpl@7ec028
                      -------------------------------------

                      I'm now starting to think that the problem isn't with the connection pooling itself, but the EJBs. It appears as though they are hanging onto the connection object even after it is 'returned' to the pool.

                      I had to enable GC just to get the objects to go back to the pool. Perhaps I need to update my container configuration. Are you using a custom container configuration? If so, can you please post it?

                      Thanks,

                      Erik

                      • 8. Re: Pooled Connections closed
                        Dave Elliot Newbie

                        What do you mean by 'custom container config' ?

                        As for hanging onto Connections...I'd recommend definitely not keeping a handle to a connection in an instance var (which some texts advocate).

                        Rather:

                        Connection connection = null;
                        Statement statement = null;

                        try {
                        connection = (a util function to do JNDI lookup on datasource).getConnection();

                        statement = connection.createStatement();

                        if (statement.executeUpdate(sql) !=1)
                        throw new EJBException(xxx);
                        }
                        catch(SQLException se) {
                        throw new EJBException(yyy);
                        }
                        }
                        finally {
                        try {
                        if(statement != null) statement.close();
                        if(connection != null) connection.close();
                        }
                        catch(Exception e) {}
                        }


                        Note, get a connection from the pool, use it, close it. No fooling around with resources like that (and don't go passing ResultSets around either ;-) )


                        Dave

                        • 9. Re: Pooled Connections closed
                          David Jencks Master

                          Note that Dave's working config has

                          GCInterval
                          set whereas the non-working one has
                          GCMinIdleTime
                          set. This is important!

                          (I hate all these irrelevant and badly named parameters in 2.4. Mostly fixed in 3.0)


                          Things to check for the transactions:

                          1. Remove your commit in code. It will only confuse things.

                          2. Make sure all the methods are getting a transaction, attribute Requires or RequiresNew.

                          3. Make sure all db access is like this:

                          (get the DataSource ds)

                          Connection c = ds.getConnection();
                          try {
                          PreparedStatement p = c...
                          try {
                          set params
                          ResultSet rs = ...
                          try {
                          ...
                          } finally {
                          rs.close;
                          }
                          } finally {
                          p.close();
                          }
                          } finally {
                          c.close();
                          }

                          Leaving out the c.close may break the connection so it doesn't commit properly and can't be reused.

                          This is a rather undesirable feature of jboss-- many Sun examples show caching the connection handle.

                          • 10. Re: Pooled Connections closed
                            Erik Sliman Apprentice

                            I overlooked the GC differences when posting, yet my config has both:

                            true
                            300000
                            120000

                            In both of your examples, is container managed transaction still supported? I don't understand how you can close a connection, and still have it partake in container managed transactions.

                            I want to use container managed transactions because I believe that methods should be able to participate in transactions without being aware. This is necessary for flexibility in complex data object models.

                            In the COM world, I accomplished this by allowing connections to be optionally passed to save, load and business methods.

                            Erik

                            • 11. Re: Pooled Connections closed
                              Erik Sliman Apprentice

                              Another question. If I follow your example, no longer caching the connection, should I cache the context in order to ensure that multiple methods can participate in a single transaction? Will this context automatically return the same connection if a transaction has begun? If I don't cache the context, will a new InitialContext() still return the same context within a transaction?

                              Thanks for your help! -E

                              • 12. Re: Pooled Connections closed
                                David Jencks Master

                                Tada! Its magic! You don't need to worry about the connection being in the right transaction, that is one of the container contracts. To look behind the curtain, an xa db driver doesn't care which connection you use, the transaction manager enrolls any connection in the transaction before you get it and unenrolls it when you "close" it. For jdbc 1 connections, the pooling mechanism goes to some trouble to always give you the same connection for all work in one transaction.

                                In jboss right now, you do need to close the connection before the transaction ends. This does not close the physical db connection, just tells the pooling machinery you are done with it for now.

                                You can do anything you like with InitialContext. You can also cache the DataSource if you want, although I think you have to set it to null on passivation.

                                • 13. Re: Pooled Connections closed
                                  Erik Sliman Apprentice

                                  Thanks for your help, David. My BMP appears to be working well now! I've been pumping out CMP beans as well, since they seem to be easier for now, although I'm currently limiting them to a section I plan to create a multi-app service with, application security.

                                  Things won't get sticky until I start to create relationships. If you're up to it, here are some questions I have been pondering on:

                                  - Can transactions cross BMP and CMP beans?

                                  - If I use external references, how far can the transactions span. I forsee applications being composed of services, and may need a business process to encompass multiple services.

                                  - Is there a way to encapsulate a single transaction from Session beans that call numerous entity beans, including both CMP and BMP?

                                  - In the Windows COM app world, I used the registry for dynamic server configuration, where the administrator used a simple client to change parameters, and the app then read the registry to find server-specific configuration. How can I accomplish this in the Java world? A deployment descriptor would not be dynamic enough, and would be a pain to configure for each server. I don't want to require a database either, since part of what I plan to parameterize will be necessary before you can connect, plus who wants to add more data modeling and database maintenance just for server specific configuration.

                                  If you create a new topic to answer any of these questions, just post the topic name here, and I'll gladly join it. This is obviously digressing from this thread.

                                  Thanks again,

                                  Erik
                                  erik@openstandards.net

                                  • 14. Re: Pooled Connections closed
                                    Dave Elliot Newbie

                                    > - Can transactions cross BMP and CMP beans?

                                    Well transactions can certainly cross CMP beans that have been configured with the correct transaction attribute (eg Required, Supports, etc....as opposed to Never). You can't have nested transactions with EJB so the BMP thing wouldn't be useful...

                                    > - If I use external references, how far can the
                                    > transactions span. I forsee applications being
                                    > composed of services, and may need a business
                                    > process to encompass multiple services.

                                    If you're asking what I think you're asking, a transaction can span any servers you like as long as your EJB server software supports distributed transactions...not sure about JBoss on that one.

                                    > - Is there a way to encapsulate a single transaction
                                    > from Session beans that call numerous entity beans,
                                    > including both CMP and BMP?

                                    Well you can't have a BMP entity bean, they're only CMP. But, yes, you can definitely have a BMP session bean run a transaction over X number of entity beans. That's standard procedure.

                                    > - In the Windows COM app world...

                                    I'd say JMX is part of the answer on this one...(if you're using JBoss...). I'm not savvy on JMX yet tho.


                                    1 2 Previous Next