CODEWITHSUNDEEP
javadatabasejdbc
Stanislav Codes
Stanislav Codes

Reputation: 191

Database connection object behavior with limited memory

I've learned that database connections should be closed without any doubt and I even got to see why:

com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: Data source rejected establishment of connection,  message from server: "Too many connections"

Here's the code throwing the aforementioned exception (on my machine):

public static void main(String[] args) {
    try {
        for (int i = 0; i < 1000; i++) {
            Connection connection = getConnection();
            Statement st = connection.createStatement();
            st.execute("CREATE TABLE IF NOT EXISTS clients (id INT NOT NULL AUTO_INCREMENT, firstname VARCHAR(50), lastname VARCHAR(50), PRIMARY KEY (id))");
            st.execute("DROP TABLE clients");
        }

    } catch (SQLException e) {
        e.printStackTrace();
    }
}

private static Connection getConnection() throws SQLException {
    return DriverManager.getConnection(bundle.getString("jdbc.url"),
            bundle.getString("jdbc.username"), bundle.getString("jdbc.password"));
}

Eventually I had to decrease heap size and the code above started working. What's happening under these circumstances and how does it enable creating connections continuously without any exceptions?

Upvotes: 0

Views: 104

Answers (1)

Kayaman
Kayaman

Reputation: 73548

My educated guess would be that the underlying connection class has implemented the finalize() method which cleans up the connection when the object is GC'd.

With a larger heap the GC isn't working so hard, so connections aren't getting collected. With a smaller heap the GC needs collect them more often and the limit of active connections doesn't reach the maximum.

The proper way to go is to use a connection pool with a limited amount of connections that are always kept open, just borrowed from and returned to the pool. Creating connections is expensive after all.

Upvotes: 2

Related Questions