Geek Answers Handbook

Leak repair

We have an application that seems to have a connection leak (SQL Server says the maximum pool size has been reached). I am alone on my dev machine (obviously), and just by going into the application I run this error. SQL Server Activity Monitor shows a large number of processes using my database.

I want to find which files to open connections, but not to use. I thought of using something like grep for each file, counting the number of ".Open ()" and the number of ".Close ()" and I get a file for which the numbers are not equal. Is this realistic?

Bonus question: Do the processes found in the SQL Server activity monitor match the connections? If not, how do I know how many connections are open in my database?

The application is in asp.net (vb) 3.5 with SQL Server 2005. We currently do not use linq (yet) or anything like that.

thank

+5
source share
2 answers

When you look at the code from SQL Server, you can run the following query to get an idea of ​​which queries were last executed on sleep connections. (open connections that do nothing)

SELECT ec.session_id, last_read, last_write, text, client_net_address, program_name, host_process_id, login_name
FROM sys.dm_exec_connections  ec
JOIN sys.dm_exec_sessions es
  ON ec.session_id = es.session_id
CROSS APPLY sys.dm_exec_sql_text(ec.most_recent_sql_handle) AS dest
where es.status = 'sleeping'

From the application side, you can debug the sos.dll file as described in the following articles:

If you need more information on how to use windbg, these articles are a good introduction:

+9

- .

, , .

@BeforeClass
public static void initConnectionLeakUtility() {
    if ( enableConnectionLeakDetection ) {
        connectionLeakUtil = new ConnectionLeakUtil();
    }
}

@AfterClass
public static void assertNoLeaks() {
    if ( enableConnectionLeakDetection ) {
        connectionLeakUtil.assertNoLeaks();
    }
}

ConnectionLeakUtil :

public class ConnectionLeakUtil {

    private JdbcProperties jdbcProperties = JdbcProperties.INSTANCE;

    private List idleConnectionCounters = 
        Arrays.asList(
            H2IdleConnectionCounter.INSTANCE,
            OracleIdleConnectionCounter.INSTANCE,
            PostgreSQLIdleConnectionCounter.INSTANCE,
            MySQLIdleConnectionCounter.INSTANCE
    );

    private IdleConnectionCounter connectionCounter;

    private int connectionLeakCount;

    public ConnectionLeakUtil() {
        for ( IdleConnectionCounter connectionCounter : 
            idleConnectionCounters ) {
            if ( connectionCounter.appliesTo( 
                Dialect.getDialect().getClass() ) ) {
                this.connectionCounter = connectionCounter;
                break;
            }
        }
        if ( connectionCounter != null ) {
            connectionLeakCount = countConnectionLeaks();
        }
    }

    public void assertNoLeaks() {
        if ( connectionCounter != null ) {
            int currentConnectionLeakCount = countConnectionLeaks();
            int diff = currentConnectionLeakCount - connectionLeakCount;
            if ( diff > 0 ) {
                throw new ConnectionLeakException( 
                    String.format(
                        "%d connection(s) have been leaked! Previous leak count: %d, Current leak count: %d",
                        diff,
                        connectionLeakCount,
                        currentConnectionLeakCount
                    ) 
                );
            }
        }
    }

    private int countConnectionLeaks() {
        try ( Connection connection = newConnection() ) {
            return connectionCounter.count( connection );
        }
        catch ( SQLException e ) {
            throw new IllegalStateException( e );
        }
    }

    private Connection newConnection() {
        try {
            return DriverManager.getConnection(
                jdbcProperties.getUrl(),
                jdbcProperties.getUser(),
                jdbcProperties.getPassword()
            );
        }
        catch ( SQLException e ) {
            throw new IllegalStateException( e );
        }
    }
}

IdleConnectionCounter MySQL :

public class MySQLIdleConnectionCounter implements IdleConnectionCounter {

    public static final IdleConnectionCounter INSTANCE = 
        new MySQLIdleConnectionCounter();

    @Override
    public boolean appliesTo(Class<? extends Dialect> dialect) {
        return MySQL5Dialect.class.isAssignableFrom( dialect );
    }

    @Override
    public int count(Connection connection) {
        try ( Statement statement = connection.createStatement() ) {
            try ( ResultSet resultSet = statement.executeQuery(
                    "SHOW PROCESSLIST" ) ) {
                int count = 0;
                while ( resultSet.next() ) {
                    String state = resultSet.getString( "command" );
                    if ( "sleep".equalsIgnoreCase( state ) ) {
                        count++;
                    }
                }
                return count;
            }
        }
        catch ( SQLException e ) {
            throw new IllegalStateException( e );
        }
    }
}

, , :

:hibernate-core:test

org.hibernate.jpa.test.EntityManagerFactoryClosedTest > classMethod FAILED
    org.hibernate.testing.jdbc.leak.ConnectionLeakException
+1

More articles:


All Articles