Error: No ManagedConnections available within configured blocking timeout

[marcos_aps]

- JBoss 4.2.3.GA
- JDK 5 Update 22
- Microsoft SQL Server 2000
- Microsoft SQL Server 2005 JDBC Driver 1.2 (compatible with MSSQL Server 2000)
- Windows Server 2003

Hello, everybody!

I have always had this error in JBoss, but I always ignored it deciding not to investigate it any further because it didn't happen very often (once a month in the majority of cases, but less than this in some cases. However yesterday it happened twice), because it was just a matter of restarting the application server, and because it was a very strange error. But now I realize that this error is simply unacceptable bacause it makes an application very unstable and unreliable, even though you are sure that there's nothing wrong in your application to cause it. I'm talking about this error:

org.jboss.util.NestedSQLException: No ManagedConnections available within configured blocking timeout ( 30000 [ms] ); - nested throwable: (javax.resource.ResourceException: No ManagedConnections available within configured blocking timeout ( 30000 [ms] ))

I can assure you that I'm not leaking any open database connection in my application. JBoss is configured to check if the application let a connection open and I don't get any message from JBoss telling me this. I'm also closing all ResultSets and Statements. I'm following the rules contained here:

http://community.jboss.org/wiki/CanJBossTellMeWhenIDontCloseAConnection

This is the pattern I use to close resources:

Connection connection = null;
PreparedStatement statement = null;
ResultSet data = null;
try
{
    DataSource ds = get data source with JNDI
    connection = ds.getConnection();
    // ...
}
finally
{
    if (data != null)
    {
        try
        {
            data.close();
        }
        catch (SQLException ex)
        {
        }
    }
    if (statement != null)
    {
        try
        {
            statement.close();
        }
        catch (SQLException ex)
        {
        }
    }
    if (connection != null)
    {
        try
        {
            connection.close();
        }
        catch (SQLException ex)
        {
        }
    }
}

I really would like to have a solution to this problem.

Thank you in advance.

Marcos

[marcos_aps]

Assuming there's nothing wrong with my application as I showed you, is there any kind of configuration that I can apply to my datasource to get rid of this problem? I think that this can be a solution, but I don't know what kind of possible configuration I can do. If any of you had this problem and solved it I would like please to have your help to solve mine. I have no idea what do to. For me it is very strange that I have to restant the server to make my application run properly again after this error. If JBoss has a connection pool, why is it not releasing any connections anymore to the point to force you to restart the server? I can understand that some client can get this error when there's no connection available and there's a timeout, but not forever because connection will be available later. But why it seems that JBoss is retaining all the connections as if they were all being used? Again, is there a way to solve this?

Marcos

[sachin_java]

    You can specify the max and min connection pool size in the  -ds.xml file.

    <!--pooling parameters-->
    <min-pool-size>5</min-pool-size>
    <max-pool-size>100</max-pool-size>
    <blocking-timeout-millis>5000</blocking-timeout-millis>

    Add the folloing in the -ds.xml file so that it will throw exceptions if any statements or result sets left unclosed.

 <track-statements>true</track-statements>

[marcos_aps]

Hello, Sachin. Thanks for you interest.

As I said in the first post in this thread, JBoss is configured to check open connections and open statements, and it is not giving me any warn messages about this. Because of this I know my application is not leaking open resources.

Last week I changed the datasource from the default configuration that is this:

<min-pool-size>0</min-pool-size>
<max-pool-size>20</max-pool-size>
<idle-timeout-minutes>15</idle-timeout-minutes>

to this:

<min-pool-size>1</min-pool-size>
<max-pool-size>40</max-pool-size>
<idle-timeout-minutes>1</idle-timeout-minutes>

Now I'll see what happen.

I was so suspicious about this problem that I even changed my code from this:

@Stateless
@TransactionAttribute(TransactionAttributeType.NOT_SUPPORTED)
public class PesquisaAcervoBean implements IPesquisaAcervo
{
    // ...

    protected ResultadoPesquisa pesquisar()
    {
        Connection conexao = null;
        CallableStatement procedimentoArmazenado = null;
        ResultSet dados = null;
        try
        {
            DataSource fonteDados =
                (DataSource) new InitialContext().lookup("java:/BibliotecaDS");
            conexao = fonteDados.getConnection();

            // ...
   
            return new ResultadoPesquisa(materiais, totalMateriais);
        }
        catch (NamingException ex)
        {
            throw new BibliotecaException(ex);
        }
        catch (SQLException ex)
        {
            throw new BibliotecaException(ex);
        }
        finally
        {
            if (dados != null)
            {
                try
                {
                    dados.close();
                }
                catch (SQLException ex)
                {
                }
            }
            if (procedimentoArmazenado != null)
            {
                try
                {
                    procedimentoArmazenado.close();
                }
                catch (SQLException ex)
                {
                }
            }
            if (conexao != null)
            {
                try
                {
                    conexao.close();
                }
                catch (SQLException ex)
                {
                }
            }
        }
    }
}

to this:

@Stateless
@TransactionManagement(TransactionManagementType.BEAN)
public class PesquisaAcervoBean implements IPesquisaAcervo
{
    // ...

    protected ResultadoPesquisa pesquisar()
    {
        Connection conexao = null;
        CallableStatement procedimentoArmazenado = null;
        ResultSet dados = null;
        try
        {
            DataSource fonteDados =
                (DataSource) new InitialContext().lookup("java:/BibliotecaDS");
            conexao = fonteDados.getConnection();
            conexao.setAutoCommit(false);

            // ...
   
            conexao.commit();

            return new ResultadoPesquisa(materiais, totalMateriais);
        }
        catch (NamingException ex)
        {
            throw new BibliotecaException(ex);
        }
        catch (SQLException ex)
        {
            if (conexao != null)
            {
                try
                {
                    conexao.rollback();
                }
                catch (SQLException ex1)
                {
                    throw new BibliotecaException(ex1);
                }
            }
            throw new BibliotecaException(ex);
        }
        finally
        {
            if (dados != null)
            {
                try
                {
                    dados.close();
                }
                catch (SQLException ex)
                {
                }
            }
            if (procedimentoArmazenado != null)
            {
                try
                {
                    procedimentoArmazenado.close();
                }
                catch (SQLException ex)
                {
                }
            }
            if (conexao != null)
            {
                try
                {
                    conexao.setAutoCommit(true);
                }
                catch (SQLException ex1)
                {
                }
                try
                {
                    conexao.close();
                }
                catch (SQLException ex)
                {
                }
            }
        }
    }
}

to control the transaction explicitly. I don't know if this will help. It is just a guess.

If you do some search on the internet about this error you will see that it is a very common error. From what I have seen until now, the JBoss connection pool is very greedy about connections. It seems that it likes creating connections unnecessarily, even if the number of clients accessing your application is very small.

Marcos

[marcos_aps]

Some time ago I changed my datasource configuration to this:

<min-pool-size>0</min-pool-size>
<max-pool-size>30</max-pool-size>
<idle-timeout-minutes>3</idle-timeout-minutes>

with this configuration I noticed that things got a little better, but yesterday and last week when I accessed the application I got the 'No ManagedConnections available' error again. Ok, I can understand that this error should inevitable happen given that no connections are really not available, but what surprises me is the statistics that applications like jmx console and web console show me when the error happens.

When the error pops up, jmx console shows me, in the ManagedConnectionPool, that all connections for the application are in use (ConnectionCount = 30, InUseConnectionCount = 30). But the thing that surprises me and I can't understand is that when I looked in the web console, it told me that the number of active sessions for that application is just 6. So I ask: why JBoss has to use (grab and make unavailable for a long time) all 30 connections when there are only 6 active sessions? There's no need for 6 sessions to use all 30 connections. My application, by no means, makes so heavy use of connections. It's just a simple library application for users to find books in the library. It is basically a form that the user fills in and a button to make the search. When the search is done all resources (ResultSets, Statements and Connections) are closed. I know that they are being closed because JBoss is configured with all configuration to warn me if there's some kind of resource leak involving these resources, and I haven't got any warning.

There's something very strange the way JBoss operates with its datasources. Something that needs to be clarified. It's clear that my application with this problem happen with only 6-7 active sessions has no real use. JBoss is being very greedy about connections. It is making the connections unavailable without even using them. There's a complete lack in the JBoss documentation about this 'No ManagedConnections available' error. This is a very common error, yet with no solution. Just make an ordinary search on Google to see this. In my case my application can't scale to more then 6-7 connections because JBoss don't allow this. And I know if I increase the max pool size won't solve the problem either, because it will eat all connections again and the active session number will only get a little better. I think that the JBoss team or some members of the community should come up with a good article/documentation/wike about this problem and its possible solutions (it there's any). This problem is just unacceptable and silence won't make it any better.

Marcos

[eldontc]

Marcos Antonio,

Pelo seu código parece que você também é brasileiro, certo? Estamos com este mesmo problema aqui no Tribunal de Contas da União em Brasília. Você tem alguma novidade sobre este assunto? Temos suporte oficial da Red Hat / JBoss e abri um chamado sobre isso lá. Mas não obtive nenhum sucesso, até agora o suporte deles não deu nenhuma luz.

If you not are brazilian, sorry, tell me and I will write for you in english.

Eldon

[marcos_aps]

Olá, Eldon!

Sou brasileiro, sim. Como você mesmo pode constatar, O JBoss abre muitas conexões desnecessariamente e as mantêm ativas por um longo tempo, independente da configuração de <idle-timeout-minutes> que você escolha. Ele parece simplesmente ignorar as suas configurações, agindo de uma forma totalmente imprevisível. Acho uma vergonha para a Red Hat/JBoss não ter uma boa documentação sobre este problema que, como sabemos,
é bastante comum. Se você ler meus posts anteriores vai ver que a minha aplicação não apresenta problema algum. Ela não está deixando nenhum recurso aberto após o uso e inclusive o JBoss está configurado para alertar-me se isso acontecer, o que nunca ocorreu.

Infelizmente não tenho nenhuma novidade sobre este problema. Mas eu estou portando uma aplicação que estou desenvolvendo do MS SQL Server 2000 para PostgreSQL 8.4 e a parte de consulta da aplicação vai ser portada para usar somente JPA, ao invés de JDBC puro. Também já comecei o porte para o Java EE 6, usando o JBoss 6. Quem sabe se talvéz com isso esse problema não desaparece de forma mágica?

Agora, se eu tivesse suporte oficial da Red Hat, eu não deixaria por menos, não. Eles teriam que me dar uma solução para o meu problema. Ou mostrando onde estava o erro na minha aplicação ou me dizendo o que fazer para me livrar do erro, caso minha aplicação não apresentasse nenhum problema.

Qualquer novidade que eu tiver sobre esse assunto, eu te falo. Se você puder fazer o mesmo ficaria bastante agradecido.

Até mais e boa sorte, Eldon.

Marcos

P.S.: Da próxima vez vou te escrever em inglês. Dessa forma as outras pessoas podem se beneficiar.

[jpviragine]

Hi Guys,

Marcos: have you been using MS SQL jdbc driver? If yes, please try to use jtds http://jtds.sourceforge.net/

Eldon: have you been using MS SQL Server too? Have you been using CallableStatement?

Please, post your "-ds.xm" files.

Cheers,
João Paulo Viragine

[marcos_aps]

Yes, I'm using the official Microsoft SQL Server 2005 JDBC Driver 1.2 (which is compatible with Microsoft SQL Server 2000). I think the driver I'm using has nothing to do with this problem. Changing the driver won't solve the problem. The problem is in JBoss itself.

Marcos

[jpviragine]

I bet the problem is not with JBoss AS ;-)

The problem is related to a combination of: jdbc driver/CallableStatement/configurations

Please, try jTDS Driver.
Please, show me your datasource configuration.

Cheers,

João Paulo

[rferreir]

Hi everyone,

João Paulo is right ... it's a issue about the DRIVER. I had the same problem a couple years ago, and after trying everything (even changing the code) I've tried the open-source driver for MS-SQL Server. It worked in the same hour. Marcos, make this test at you application and give us feedback about it.

Regarding the Red Hat / JBoss technical support, I'm going to ask our JBoss enginner to call you today to assist you on that. I'm sure that with a nicely conversation, you can solve the problem, maybe given more details about the classpath App or server configuration.

Cheers,

[marcos_aps]

Ok, with two favorable posts suspecting that the problem is in the driver, I think I'll give a chance to it and test the open source driver. But I would like you to elaborate a little more why you think that official MS SQL Server driver has a problem with CallableStatements. Do you have any evidences about this? I really would like to hear from you about that. Anyway this is my datasource configuration file:

<?xml version="1.0" encoding="UTF-8"?>
<datasources>
  <local-tx-datasource>
    <jndi-name>BibliotecaDS</jndi-name>
    <connection-url>jdbc:sqlserver://server;databaseName=DBTESEUS</connection-url>
    <driver-class>com.microsoft.sqlserver.jdbc.SQLServerDriver</driver-class>
    <user-name>user-name</user-name>
    <password>password</password>
    <min-pool-size>0</min-pool-size>
    <max-pool-size>25</max-pool-size>
    <idle-timeout-minutes>3</idle-timeout-minutes>
      <new-connection-sql>SELECT 1</new-connection-sql>
      <check-valid-connection-sql>SELECT 1</check-valid-connection-sql>
      <!-- corresponding type-mapping in the standardjbosscmp-jdbc.xml (optional) -->
      <metadata>
         <type-mapping>MS SQLSERVER2000</type-mapping>
      </metadata>
  </local-tx-datasource>
</datasources>

Marcos

[jpviragine]

Hi Marcos,

MS SQL Server jdbc driver has a lot of bugs/performance issues.
Everybody replaces this driver by jTDS.
I've many cases that just replacing the driver solves a lot of problems (As Ricardo said).

Anyway, please, try to remove:

<new-connection-sql>SELECT 1</new-connection-sql>
<check-valid-connection-sql>SELECT 1</check-valid-connection-sql>

and try to use selectMethod=cursor in connection URL

Also, take a look at this article from my friend Bruno: http://brmachado.blogspot.com/2008/08/detectando-connection-leaks-no-jboss.html

Notice the use of listInUseConnections() of  CachedConnectionManager MBean

And: http://community.jboss.org/wiki/SetUpAMssqlDatasource

João Paulo

[marcos_aps]

João Paulo, I use <new-connection-sql> e <check-valid-connection-sql> because this solved another issue that I was having a long time ago. I just don't remember right now what it was. Thank you for the references, but as I said, I'm very much confident that I'm not having leaks in JBoss with my application, but I'll have a look in the references, specially the one about MS SQL Server datasources. Now I'm using the jTDS open source JDBC driver in production. I replaced the MS SQL Server driver. Now only time will tell if the problem was in the JDBC driver. I hope so. I'll give you feedback about the results. It will be an amazing thing to me if I finally get rid of this error. Thank you all very much.

Marcos

[eldontc]

Hi Marcos,

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

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).

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.

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.

We are  observing the behavior of applications and parallel to this, the  occurrence of lock on the database. Until  now, we're not having the overflow pool.

Cheers