1 2 Previous Next 25 Replies Latest reply on Oct 21, 2014 1:50 PM by loosehead Go to original post
      • 15. Re: Error: No ManagedConnections available within configured blocking timeout
        marcos_aps

        Eldon Coutinho wrote:

         

        Hi Marcos,

         

        Do you have news for us? Did you try the jdbc driver that João Paulo told you?

         

        Yes, as I said I'm using the jTDS JDBC driver, but unfortunately the problem hasn't gone away. JBoss is still creating more connections than necessary and holding them for a long time. But one thing that I noticed is that less connections are being created with this driver than with the SQL Server driver, although I haven't measured this and I'm not totally sure of this. I just think so based on simple observations.

         

        Here at TCU we have investigated the problem as follows:

         

        We did a three load test in the "problem" application with JMeter (development environment). The tests were done with the following settings:

         

        1. Connection pool configured for 10 connections, 20 users performing the use cases in sequence, with an interval of 5 seconds, 20 seconds of timeout waiting to acquire a new connection (blocking-timeout-millis).
        2. Connection pool configured for 10 connections, 30 users performing the use cases in sequence, with an interval of 2 seconds, 20 seconds of timeout waiting to acquire a new connection (blocking-timeout-millis).
        3. Connection pool configured for 10 connections, 30 users performing the use cases in sequence, with an interval of 2 seconds, 1 second timeout waiting to acquire a new connection (blocking-timeout-millis).

         

        To fulfill the above tests, we set up JBoss as follows:

         

        1. In jbossjca-service.xml.
        <attribute name="Debug"> true </ attribute>
        2. In file datasource (* ds.xml) turn on the debug not closing statements and results.
        <track-statements> true </ track-statements>
        3. On jboss-log4j.xml, turn on the trace of "consumption" of the connection pool:
        <! - Show the evolution of the DataSource pool in the logs [InUse / Available / Max] ->
        <category name="org.jboss.resource.connectionmanager.JBossManagedConnectionPool">
           <priority value="TRACE"/>
        </ Category>

         


        Tests 1 and 2 did not cause overflow pool. Actually, the log of JBoss said in a normal consumption of the pool, with connections being acquired and returned to the pool normally.

         

        Test 3, as expected, overflow the pool, as 30 users logging in 2 seconds and the 10 allocated faster.

         

        We have not seen in the log messages from JBoss indicating the closing of connections, which suggests that at least these use cases,
        there were no leaks connections (connections not returned to the pool).

         

        I think I'm going to do some tests with JMeter too.

         

        However, we received several messages of not closing statements and results.
        The strange thing is that, following the stacktrace, check the source code marked as "not normally close" ,
        and we did not observe this practice, ie the source code is correct,  closing statements and the results!
        Despite this, the consultant Leandro Abit of JBoss, said the not closing statements of results does not cause overflow in the connection pool.

         

        Same with me. I have already checked my code lots of time for bugs in leaking resources and this is not happening. JBoss is also configured to warn me if connections are left open after use.

        So far we suspect the following: our problem is in contention for the database, ie, long running thread.
        This contention hold connections and other requests end up consuming the connection pool.

         

        This is a good suspicion. Applications could cause contention in the database if they don't release resources (connections, ResultSets, Statements) right after they are used. For example, if it close these resources only long time after they're used or don't close them. I also know that this is not happening in my source code. But as you said, Statements and ResultSets left open don't cause connection pool overflows. Anyway I need to check this more carefully.

         

        By the way, do you call stored procedures in your code using JDBC directly? If so, do your stored procedures execute more than one SELECT while executing? Do you create temporary tables in your stored procedures?

         

        Marcos

         

        • 16. Re: Error: No ManagedConnections available within configured blocking timeout
          eldontc

          By the way, do you call stored procedures in your code using  JDBC directly? If so, do your stored procedures execute more than one  SELECT while executing? Do you create temporary tables in your stored  procedures?


           

                  Yes, we are calling store procedures in our code using JDBC. Yes again, our store procedures execute more than on SELECT. About temporary tables, no, we aren´t using this.

           

          Eldon

          • 17. Re: Error: No ManagedConnections available within configured blocking timeout
            marcos_aps

            Hello, Eldon!

             

            I have another project that is already in production. I'm using JPA in this project to deal with the database, but there's one search page in this application that, like the library project that I have, uses JDBC and stored procedures. I'm migrating this project to Java EE 6 (JBoss 6) and PostgreSQL. I'll replace the JDBC code and stored procedures to use only queries issued with JPA. So my application will be 100% JPA. I'm doing this because I got so traumatized with this error that I won't even wait to find a bug in my application. I don't think such a bug exists, but I've got a feeling that there's some issue with stored procedures that execute various SELECTs and using JDBC directly in the code. I'm not saying that our code has errors, but I suspect this combination in one way or another could cause problems with JBoss 4.2.3 + SQL Server 2000 + JDBC driver. I also think that my application is not causing contention in the database because of long transactions. I'm posting the code that I use for you to see here. If you find a bug that could cause resource leaks in this code (I haven't found any), please tell me:

             

            @Stateless
            @TransactionManagement(TransactionManagementType.BEAN)
            public class PesquisaAcervoBean implements IPesquisaAcervo
            {
            // Search
            public ResultadoPesquisa pesquisar(DadosPesquisa dadosPesquisa, int tamanhoPagina)
            {
                 return pesquisar(dadosPesquisa, tamanhoPagina, 0, true);
            }
             
            // Go to page
            public ResultadoPesquisa irParaPagina(int numeroPagina, DadosPesquisa dadosPesquisa, int tamanhoPagina)
            {
                 return pesquisar(dadosPesquisa, tamanhoPagina, numeroPagina, false);
            }

            // Internal search
            private ResultadoPesquisa pesquisar(DadosPesquisa dadosPesquisa,
                 int tamanhoPagina, int numeroPagina, boolean calcularTotalMateriais)
            {
                 validarDadosPesquisa(dadosPesquisa);
                 validarTamanhoPagina(tamanhoPagina);

             

                 dadosPesquisa = organizarDadosPesquisa(dadosPesquisa);

             

                 // Commom search? If so, search by stored procedure
                 boolean pesquisaComum = verificarSeEPesquisaComum(dadosPesquisa);
                 if (pesquisaComum)
                 {
                     // Search by stored procedure (this code is executed)
                     return pesquisarPorProcedimentoArmazenado(
                         dadosPesquisa, tamanhoPagina, numeroPagina);
                 }
                 else
                 {
                     // Search by SQL (this code is not executed in this case)
                     return pesquisarPorSQL(dadosPesquisa, tamanhoPagina, numeroPagina,
                         calcularTotalMateriais);
                 }
            }


            // Search by stored procedure
            private ResultadoPesquisa pesquisarPorProcedimentoArmazenado(
                 DadosPesquisa dadosPesquisa, int tamanhoPagina, int numeroPagina)
            {
                 Pesquisa pesquisa = new PesquisaPorProcedimentoArmazenado(
                     dadosPesquisa, tamanhoPagina, numeroPagina);
                 return pesquisa.pesquisar();
            }

             

            // more code...

             

            // Abstract search class
            private abstract static class Pesquisa
            {
                 // Search data
                 private final DadosPesquisa fDadosPesquisa;
                 // Page length
                 private final int fTamanhoPagina;
                 // Page number
                 private final int fNumeroPagina;

             

                 private Pesquisa(
                     DadosPesquisa dadosPesquisa, int tamanhoPagina, int numeroPagina)
                 {
                     fDadosPesquisa = dadosPesquisa;
                     fTamanhoPagina = tamanhoPagina;
                     fNumeroPagina = numeroPagina;
                 }

             

                 protected DadosPesquisa getDadosPesquisa()
                 {
                     return fDadosPesquisa;
                 }

             

                 protected int getTamanhoPagina()
                 {
                     return fTamanhoPagina;
                 }

              

                 protected int getNumeroPagina()
                 {
                     return fNumeroPagina;
                 }

             

                 // Search method
                 protected abstract ResultadoPesquisa pesquisar();
            }

            // Concrete search class by stored procedure
            private static class PesquisaPorProcedimentoArmazenado extends Pesquisa
            {
                 private PesquisaPorProcedimentoArmazenado(
                     DadosPesquisa dadosPesquisa, int tamanhoPagina, int numeroPagina)
                 {
                     super(dadosPesquisa, tamanhoPagina, numeroPagina);
                 }
             
                 // Search method
                 @Override
                 protected ResultadoPesquisa pesquisar()
                 {
                     Connection conexao = null;
                     CallableStatement procedimentoArmazenado = null;
                     ResultSet dados = null;
                     try
                     {
                         // Get connection through JNDI
                         conexao = Utils.obterConexao();
                         // Disable autocommit
                         conexao.setAutoCommit(false);
               
                         // Create the stored procedure
                         procedimentoArmazenado = criarProcedimentoArmazenado(conexao);

             

                         // Set the stored procedure parameters
                         int indiceParametroTotalMateriais =
                             definirParametrosProcedimentoArmazenado(
                             procedimentoArmazenado);

             

                         // Execute the stored procedure
                         dados = executarProcedimentoArmazenado(procedimentoArmazenado);

             

                         // Read data
                         List<Material> materiais = new ArrayList<Material>();
                         while (dados.next())
                         {
                             String tipo = dados.getString(1); // TIPO
                             Material material = new Material(
                                 tipo,
                                 dados.getInt(2), // SEQUENCIA
                                 dados.getString(3), // TITULO
                                 dados.getString(4), // AUTOR
                                 dados.getString(5), // ASSUNTO
                                 dados.getString(6), // CDD
                                 dados.getString(7) // CUTTER
                             );
                             materiais.add(material);
                         }
               
                         // Read the stored procedure output parameter that
                         // contains the total rows
                         int totalMateriais = procedimentoArmazenado.getInt(
                             indiceParametroTotalMateriais);
               
                         // Commit the transaction
                         conexao.commit();

             

                         return new ResultadoPesquisa(materiais, totalMateriais);
                     }
                     catch (NamingException ex)
                     {
                         // Doesn't happen at all. The connection is always found
                         // with JNDI
                         throw new BibliotecaException(ex);
                     }
                     catch (SQLException ex)
                     {
                         if (conexao != null)
                         {
                             try
                             {
                                 // Rollback the transaction in case of an error
                                 conexao.rollback();
                             }
                             catch (SQLException ex1)
                             {
                                 throw new BibliotecaException(ex1);
                             }
                         }
                         throw new BibliotecaException(ex);
                     }
                     finally
                     {
                         // Close resources
                         if (dados != null)
                         {
                             try
                             {
                                 dados.close();
                             }
                             catch (SQLException ex)
                             {
                             }
                         }
                         if (procedimentoArmazenado != null)
                         {
                             try
                             {
                                 procedimentoArmazenado.close();
                             }
                             catch (SQLException ex)
                             {
                             }
                         }
                         if (conexao != null)
                         {
                             try
                             {
                                 // Enable autocommit
                                 conexao.setAutoCommit(true);
                             }
                             catch (SQLException ex1)
                             {
                             }
                             try
                             {
                                 conexao.close();
                             }
                             catch (SQLException ex)
                             {
                             }
                         }
                     }
                 }

             

                 // Create the stored procedure
                 private CallableStatement criarProcedimentoArmazenado(
                     Connection conexao) throws SQLException
                 {
                     PesquisaComum pesquisaComum =
                         PesquisaComum.obterPesquisaComum(getDadosPesquisa());
               
                     String parametrosPesquisa =
                         UtilsString.repetir("?,",
                         pesquisaComum.totalParametrosProcedimentoArmazenado());
                     String parametroCombinacaoDados =
                         pesquisaComum.totalParametrosProcedimentoArmazenado() > 1 ?
                         ", ?" : "";
               
                      return conexao.prepareCall(
                          String.format("{call %s(%s ?, ?, ?, ?, ?, ?%s)}",
                          pesquisaComum.nomeProcedimentoArmazenado(),
                          parametrosPesquisa,
                          parametroCombinacaoDados));
                 }

             

                 // Set the stored procedure parameters
                 private int definirParametrosProcedimentoArmazenado(
                     CallableStatement procedimentoArmazenado) throws SQLException
                 {
                     PesquisaComum pesquisaComum =
                         PesquisaComum.obterPesquisaComum(getDadosPesquisa());

             

                     pesquisaComum.definirParametrosProcedimentoArmazenado(
                         procedimentoArmazenado, getDadosPesquisa());
                     int indiceParametro =
                         pesquisaComum.totalParametrosProcedimentoArmazenado() + 1;
                     procedimentoArmazenado.setShort(
                         indiceParametro++, getDadosPesquisa().getCodigoCampus());
                     String tipoMaterial;
                     if (getDadosPesquisa().getTipoMaterial() == null)
                     {
                         tipoMaterial = null;
                     }
                     else
                     {
                         tipoMaterial = TipoMaterial.converterDeTipoMaterialParaCodigo(
                             getDadosPesquisa().getTipoMaterial());
                     }
                     procedimentoArmazenado.setString(indiceParametro++, tipoMaterial);
                     procedimentoArmazenado.setString(indiceParametro++,
                         getDadosPesquisa().getOrdem().name());
                     if (pesquisaComum.totalParametrosProcedimentoArmazenado() > 1)
                     {
                         procedimentoArmazenado.setString(indiceParametro++,
                             getDadosPesquisa().getCombinacao().name());
                     }
                     procedimentoArmazenado.setInt(indiceParametro++, getNumeroPagina());
                     procedimentoArmazenado.setInt(
                         indiceParametro++, getTamanhoPagina());
                     procedimentoArmazenado.registerOutParameter(
                         indiceParametro, java.sql.Types.INTEGER);

                    return indiceParametro;
                 }

             

                 // Execute the stored procedure
                 private ResultSet executarProcedimentoArmazenado(
                     PreparedStatement procedimentoArmazenado) throws SQLException
                 {
                     // dadosEncontrados = dataFound
                     boolean dadosEncontrados = procedimentoArmazenado.execute();
                     // linhasAfetadas = affectedRows
                     int linhasAfetadas = 0;
                     while (dadosEncontrados || linhasAfetadas != -1)
                     {
                         if (dadosEncontrados)
                         {
                             return procedimentoArmazenado.getResultSet();
                         }
                         linhasAfetadas = procedimentoArmazenado.getUpdateCount();
                         dadosEncontrados = procedimentoArmazenado.getMoreResults();
                     }
                     // Should not reach here
                     throw new AssertionError("Dados não encontrados.");
                 }
            }

             

            // more code...
            }

             

            If you find any news about the bug, please tell me. I'll do the same. Good luck.

             

            Marcos

            • 18. Re: Error: No ManagedConnections available within configured blocking timeout
              watchamcb

              The cause of this issue in our production environment appears to be a result of the maxSession attribute on the MDB being greater than the max-pool-size in the DataSource descriptor. Under heavy load the MDB was unable to retrieve a connection from the pool and would try and roll-back the transaction which would then throw HeuristicMixedException. Resolved by increasing the connection pool size as explained in the replies above.

              • 19. Re: Error: No ManagedConnections available within configured blocking timeout
                juanedgar

                Hi Marcos.

                 

                In your former project, with the MS SQL Driver, Did the change of Driver (jtds instead of MSSQL) solve the problem??

                • 20. Re: Error: No ManagedConnections available within configured blocking timeout
                  bmreda

                  Hello,

                   

                  I have the same probleme : Jboss+Oracle

                  And connexions are not closed

                   

                  Did someone found any issue to this, or any logic answer?

                   

                  Regards,

                  Red.

                  • 21. Re: Error: No ManagedConnections available within configured blocking timeout
                    koolkhalid

                    Hi Marcos,

                     

                    You did a great job in digging this issue, verifying the connection leaks in code, moving to JTDS for testing but could you please confirm that moving to JTDS fixes the 'Managed Connection' issue. As per your reply JTDS makes less connection in comparison to JDBC. I am working with JBoss AS 4.0.1 sp1 with SQL Server 2005 and have the same issue. The net result is my application gets extremely slow and unable to process any request and ultimately i restarts the JBoss AS to get application available for end users and things move on. Recently, our number of users exceed and this issue occuring very frequently. DS details are below. Please suggest any solution to this.

                     

                    <?xml version="1.0" encoding="UTF-8"?>

                    <datasources>

                      <local-tx-datasource>

                      <jndi-name>MYDS</jndi-name>

                      <connection-url>

                      jdbc:sqlserver://serverurl:Port;DatabaseName=MYDB;SelectMethod=cursor

                      </connection-url>

                      <driver-class>

                      com.microsoft.sqlserver.jdbc.SQLServerDriver

                      </driver-class>

                      <user-name>***</user-name>

                      <password>***</password>

                      <min-pool-size>50</min-pool-size>

                      <max-pool-size>550</max-pool-size>

                      <query-timeout>60</query-timeout> 

                      <blocking-timeout-millis>60000</blocking-timeout-millis>

                      <idle-timeout-minutes>1</idle-timeout-minutes>

                      <metadata>

                      <type-mapping>MS SQLSERVER2000</type-mapping>

                      </metadata>

                      </local-tx-datasource>

                    </datasources>

                     

                    Khalid.

                    • 22. Re: Error: No ManagedConnections available within configured blocking timeout
                      egaray

                      Hello,

                       

                      I have the same probleme : Jboss+SQLServer

                       

                      regards

                      • 23. Re: Error: No ManagedConnections available within configured blocking timeout
                        wdfink

                        Often the application did not close the connection. You might look into the wikis how you configure the connection manager in your JBoss version to identify the leaks.

                        If you not find a solution please open a new thread with the informations about your JBoss version and configuration.

                        • 24. Re: Error: No ManagedConnections available within configured blocking timeout
                          koolkhalid

                          Hi,

                           

                          Thanks for your feedback. I am looking into a couple of things including performance tuning and installing java profiler/application monitor to identify the connection leaks if any. However as suggested by you open a new thread with the information about Jboss and Configuration. Looking forward for your reply on this.

                           

                          No ManagedConnections available within configured blocking timeout in Jboss 4.0.1 sp1

                          • 25. Re: Error: No ManagedConnections available within configured blocking timeout
                            loosehead

                            My problem was dopey. I was copying a project to a new one and forgot to put the Hibernate filter class in the web.xml. Hibernate worked, it just never did its housekeeping.

                            1 2 Previous Next