Chaos Engineering – DB Connection Leak

All modern applications connect with storage, such as a database or cache. Database connection leak is commonly observed in modern applications, which can result in connection leaks that can lead to production outages.

In our series of chaos engineering articles, we have been learning how to simulate various performance problems. In this post, let’s discuss how to debug and identify the root causes of database connection issues.

Sample Program

Here is a sample program from the open-source BuggyApp application, which is leaking database connections and may eventually lead to out-of-connection exceptions.

The below code demonstrates a leak where connections to a SQL database perform a query, and fails to close the connection afterwards. This negligence can result in a SQL query leak and, ultimately, an ‘out of connections’ error.

A thread will enter into a BLOCKED state when it can’t acquire a lock on an object because another thread already holds the lock on the same object and doesn’t release it. Review the program carefully.

/**
	 * Opens a SQL connection and never closes it 
	 */
	public void leakConnection() {
		Connection connection = null;
		try {
			connection = getConnection();
			System.out.println("Leaking DB connection");
			// Perform database operations using the connection
			PreparedStatement statement = connection.prepareStatement("SELECT * FROM "+tableName);
			ResultSet resultSet = statement.executeQuery();
			resultSet.close();
			statement.close();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				//Connection  not closed
				// closeConnection(connection);
			} catch (Exception e) {
				e.printStackTrace();
			}
		}

Diagnosing DB Connection leaks

You can diagnose DB connection leaks through manual static review or by using readily available root cause analysis tools.

Manual Methods

The manual methods for detecting DB connection leaks can be found in two ways: 

1. Monitoring Network connections
2. Monitoring logs

Operating commands such as netstat can be used to display all open connections to the database, as shown below. If the number of connections keeps increasing, it indicates a potential growth in the connection count.

netstat -an | grep 3306 
tcp4       0      0  192.168.0.47.60671     54.215.79.61.3306      ESTABLISHED
tcp4       0      0  192.168.0.47.60670     54.215.79.61.3306      ESTABLISHED
tcp4       0      0  192.168.0.47.60669     54.215.79.61.3306      ESTABLISHED
tcp4       0      0  192.168.0.47.60667     54.215.79.61.3306      ESTABLISHED
tcp4       0      0  192.168.0.47.60666     54.215.79.61.3306      ESTABLISHED
tcp4       0      0  192.168.0.47.60665     54.215.79.61.3306      ESTABLISHED
tcp4       0      0  192.168.0.47.60664     54.215.79.61.3306      ESTABLISHED
tcp4       0      0  192.168.0.47.60663     54.215.79.61.3306      ESTABLISHED

Monitoring logging

When connections are leaking and reach the maximum allowed connections, errors in the logs can indicate a possible connection leak.

com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: Data source rejected the establishment of connection,  message from server: "Too many connections"
            at sun.reflect.GeneratedConstructorAccessor80.newInstance(Unknown Source)
            at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
            at java.lang.reflect.Constructor.newInstance(Constructor.java:423)
            at com.mysql.jdbc.Util.handleNewInstance(Util.java:425)
            at com.mysql.jdbc.Util.getInstance(Util.java:408)
            at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:919)

Automated Methods

You can use root cause analysis tools such as yCrash, which automatically captures a 360-degree view of the system. For DB connection leaks, it can help capture and alert on connection failures resulting from leaked connections, and eventually, it can identify ‘out-of-connection’ errors, providing the complete stack trace to pinpoint the potential sources of the connection leaks.

DB connection leaks can be proactively identified through network statistics. A high and growing number of connections to a database server can indicate a leak and require further investigation.

Summation

In this article, we discussed a common issue in modern applications: database connection leaks that can lead to system problems. We looked at a sample program that showed how failing to close connections can cause errors. To find these issues, you can monitor the number of connections manually or use tools like yCrash. Increasing connections can signal trouble. In a nutshell, taking care of database connections is vital to keep applications running smoothly. Keep an eye on connections to prevent problems that can disrupt your system.