Lesson Learned #168: Connection is not available error message using Hikari connection pooling

This post has been republished via RSS; it originally appeared at: Azure Database Support Blog articles.

Today, I worked on a very interesting case that our customer is facing the following error message connecting to Azure SQL DB and/or Azure Managed Instance "Connection is not available, request timed out after ..". 

 

Following, I would like to share with you my lessons learned about why and how this error message is fired in two different situations:

 

  • All the connections in the Hikari pooling are in use. 
  • The session limit for the database has been reached. 

 

In order to review these two things, I included the Hikari connection pooling based on this article: Improve Java application reliability with Azure SQL Database using JDBC and connection pooling. - Microsoft Tech Community,  and modifying the current Java code that you could find in this GitHub

 

My database is a standard 1 database that we have a session limit in 900. The first thing to probe my theory about the connection pooling is to configure setmaxiumpoolsize parameter to 50 and setCloseConnection to false to open this number of connections without closing any of them. In this situation, once I reached the connection number 51 I got the error message "Connection is not available"

 

 

public static void main(String[] args) throws Exception{ System.out.println("Testing connection JAVA! (Hikari)"); ErrorClientHikari oErrorClient = new ErrorClientHikari(); oErrorClient.setCloseConnection(false); oErrorClient.setReadingSQLData(false); oErrorClient.setTotalIteractions(30000); oErrorClient.setSQLReadToExecute("SELECT count(*) Id FROM PerformanceVarcharNVarchar where TextToSearch =N'Value'"); oErrorClient.setSQLCommandTimeout(30000); oErrorClient.setServerName("servername.database.windows.net"); oErrorClient.setDatabaseName("dbname"); oErrorClient.setUserName("username"); oErrorClient.setPassword("password"); oErrorClient.setMaximumPoolSize(50); oErrorClient.setConnectionTimeout(5000); oErrorClient.LoadData();

 

 

In this similar situation, I'm going to modify the setmaxiumpoolsize parameter to 1000 to know what happen when I reached more connections that the session limit of my Azure SQL Database Standard 1. At this time, I got the same error message: "Connection is not available, request timed out after.." so, that means, that both situations will report the same error message.

 

 

public static void main(String[] args) throws Exception{ System.out.println("Testing connection JAVA! (Hikari)"); ErrorClientHikari oErrorClient = new ErrorClientHikari(); oErrorClient.setCloseConnection(false); oErrorClient.setReadingSQLData(false); oErrorClient.setTotalIteractions(30000); oErrorClient.setSQLReadToExecute("SELECT count(*) Id FROM PerformanceVarcharNVarchar where TextToSearch =N'Value'"); oErrorClient.setSQLCommandTimeout(30000); oErrorClient.setServerName("servername.database.windows.net"); oErrorClient.setDatabaseName("dbname"); oErrorClient.setUserName("username"); oErrorClient.setPassword("password"); oErrorClient.setMaximumPoolSize(1000); oErrorClient.setConnectionTimeout(5000); oErrorClient.LoadData(); }

 

 

Enjoy!

 

 

REMEMBER: these articles are REPUBLISHED. Your best bet to get a reply is to follow the link at the top of the post to the ORIGINAL post! BUT you're more than welcome to start discussions here:

This site uses Akismet to reduce spam. Learn how your comment data is processed.