{"id":142,"date":"2020-06-04T00:12:12","date_gmt":"2020-06-04T07:12:12","guid":{"rendered":"https:\/\/devblogs.microsoft.com\/azure-sql\/?p=142"},"modified":"2020-06-29T02:34:48","modified_gmt":"2020-06-29T09:34:48","slug":"improve-jdbc-application-reliability-with-azure-sql-and-connection-pooling","status":"publish","type":"post","link":"https:\/\/devblogs.microsoft.com\/azure-sql\/improve-jdbc-application-reliability-with-azure-sql-and-connection-pooling\/","title":{"rendered":"Improve JDBC application reliability with Azure SQL and connection pooling"},"content":{"rendered":"

Introduction<\/span>\u00a0<\/span><\/h2>\n

A growing number of<\/span>\u00a0Azure SQL Database<\/span>\u00a0customers are developin<\/span>g new applications in Java<\/span>, or porting existing ones, using a JDBC driver from Microsoft or a 3<\/span>rd<\/span>\u00a0party.<\/span>\u00a0<\/span><\/p>\n

While\u00a0<\/span>these drivers are\u00a0<\/span>providing quite extensive support\u00a0<\/span>and\u00a0<\/span>covering most of database service capabilities and performance expectations,\u00a0<\/span>there are a couple of areas that worth\u00a0<\/span>some\u00a0<\/span>special\u00a0<\/span>attention<\/span>.<\/span>\u00a0<\/span><\/p>\n

An important distinction<\/span>\u00a0in cloud environments<\/span>\u00a0<\/span>compared to<\/span>\u00a0traditional on-premises enterprise\u00a0<\/span>solutions<\/span>\u00a0is that network interruptions between application\u00a0<\/span>tier and database service can happen more frequently, due for example to\u00a0<\/span>planned<\/span><\/a>\u00a0maintenance activities,\u00a0<\/span>unplanned failures or\u00a0<\/span>other network connectivity\u00a0<\/span>glitches<\/span>.<\/span>\u00a0<\/span>As explained in th<\/span>at document, these interruptions are generally self-resolved in few seconds from a service perspective,\u00a0<\/span>and applications with\u00a0<\/span>proper\u00a0<\/span>retry logic<\/span><\/a>\u00a0in place are\u00a0<\/span>usually not even affected.<\/span>\u00a0<\/span>\u00a0<\/span><\/p>\n

Some\u00a0<\/span>client libraries like\u00a0<\/span>SqlClient<\/span><\/a>,\u00a0<\/span>ODBC<\/span><\/a>\u00a0driver<\/span>\u00a0or\u00a0<\/span>EntityFramework<\/span><\/a>\u00a0are even providing some\u00a0<\/span>embedded\u00a0<\/span>connection retry capabilities<\/span>,<\/span>\u00a0<\/span>and<\/span>\u00a0customers can simply configure through connection strings without<\/span>\u00a0modifying application code<\/span>.\u00a0<\/span>These will usually help in deal with\u00a0<\/span>transient issues happening when connections are sitting idle in connection pools<\/span>, making sure that pure application logic will remain unchanged.<\/span>\u00a0<\/span><\/p>\n

Retry logic frameworks<\/span>\u00a0<\/span><\/h2>\n

A more sophisticated use case is when these transient errors are happening while the application is\u00a0<\/span>actually executing<\/span>\u00a0some command against the database.<\/span>\u00a0<\/span>For this<\/span>\u00a0scenario<\/span>,\u00a0<\/span>command retry strategies that can be defined in your code to<\/span>\u00a0repeat failed commands after a fixed or exponential time interval<\/span>, letting developers to implement proper logic to avoid duplicate executions and potential inconsistent state<\/span>\u00a0in the database<\/span>.<\/span>\u00a0<\/span><\/p>\n

As of today<\/span>, unfortunately,\u00a0<\/span>Microsoft JDBC Driver<\/span>\u00a0for SQL Server<\/span><\/a>\u00a0doesn\u2019t provide\u00a0<\/span>idle connection\u00a0<\/span>retry<\/span>\u00a0capabilities<\/span>,<\/span>\u00a0<\/span>mainly\u00a0<\/span>because\u00a0<\/span>some\u00a0<\/span>challenges residing<\/span>\u00a0in\u00a0<\/span>underlying Java classes responsible for managing TCP sockets<\/span>\u00a0<\/span>are preventing a reliable and performant way of detecting network interruptions\u00a0<\/span>without executing a roundtrip with the server which would introduce performance overhead.<\/span>\u00a0<\/span>\u00a0<\/span><\/p>\n

This<\/span>,<\/span>\u00a0of course<\/span>,<\/span>\u00a0doesn\u2019t\u00a0<\/span>necessarily reduce the possibilities of creating reliable and efficient Java apps working with Azure SQL Database but<\/span>,<\/span>\u00a0rather<\/span>,<\/span>\u00a0tha<\/span>t<\/span>\u00a0some\u00a0<\/span>attentions from application developers are needed to prevent\u00a0<\/span>potential issues in some circumstances.<\/span>\u00a0For example, in the Java<\/span>-sphere, a number of frameworks exists that can be used to define<\/span>\u00a0sophisticated<\/span>\u00a0<\/span>retry logic strategies to detect transient errors and repeat specific operations or entire code blocks.\u00a0<\/span>Some examples are\u00a0<\/span>Retry4j<\/span><\/a>,\u00a0<\/span>FailSafe<\/span><\/a>,\u00a0<\/span>and even\u00a0<\/span>Spring-retry<\/span><\/a>, all quite straightforward to use<\/span>.<\/span>\u00a0<\/span><\/p>\n

Importance of d<\/span>efault connection properties<\/span>\u00a0<\/span><\/h2>\n

Connection properties<\/span><\/a>\u00a0<\/span>are playing an important role on how\u00a0<\/span>interaction between applications and backend database services will behave.<\/span>\u00a0These can be specified as part of\u00a0<\/span>the connection string, or\u00a0<\/span>through specific methods of\u00a0<\/span>DriverManager<\/span>\u00a0or\u00a0<\/span>DataSource<\/span>\u00a0classes in application code<\/span>, and s<\/span>ome of these properties are\u00a0<\/span>more<\/span>\u00a0relevant<\/span>\u00a0than others<\/span>\u00a0<\/span>in<\/span>\u00a0mak<\/span>ing<\/span>\u00a0sure that application can manage transient\u00a0<\/span>conditions properly.<\/span>\u00a0<\/span><\/p>\n

loginTimeout<\/span><\/i>\u00a0<\/span><\/i>defines t<\/span>he number of seconds the driver should wait before timing out a failed connection.\u00a0<\/span>If we don\u2019t specify it,\u00a0<\/span>timeout\u00a0<\/span>will be<\/span>\u00a0the default system timeout, which is 15\u00a0<\/span>seconds.\u00a0<\/span>In most cases, this\u00a0<\/span>alone will cover a transient connection blip due to a reconfiguration\u00a0<\/span>or a failover, and the application will just experience a\u00a0<\/span>relatively\u00a0<\/span>short delay.<\/span>\u00a0<\/span><\/p>\n

If<\/span>\u00a0<\/span>interruptions are longer,\u00a0<\/span>in general just increasing the timeout\u00a0<\/span>may or may not be a good idea, and that depends a lot on\u00a0<\/span>specific workload characteristics. For interactive workloads,<\/span>\u00a0for example,<\/span>\u00a0usually\u00a0<\/span>keeping timeout relatively low, report the issue to the end user and eventually ask to retry the operation\u00a0<\/span>is a good approach.<\/span>\u00a0<\/span>Even for batch approaches can be valuable to just<\/span>\u00a0catch the exception due to a transient issue,\u00a0<\/span>log it somewhere and retry, rather than just let the application hang for longer time.<\/span>\u00a0<\/span><\/p>\n

queryTimeout<\/span><\/i>\u00a0<\/span><\/i>and\u00a0<\/span>socketTimeout<\/span><\/i>\u00a0instead are more relevant for<\/span>\u00a0service interruptions that may happen\u00a0<\/span>while<\/span>\u00a0a command<\/span>\u00a0is<\/span>\u00a0<\/span>getting executed<\/span>\u00a0or is returning results to the client<\/span>.<\/span>\u00a0<\/span>\u00a0<\/span><\/p>\n

B<\/span>ehind the scenes,\u00a0<\/span>when\u00a0<\/span>JDBC driver<\/span>\u00a0is<\/span>\u00a0executing a command and waiting for a response from the server, it<\/span>\u00a0<\/span>uses\u00a0<\/span>Read<\/span><\/a>() method<\/span>\u00a0<\/span>Java\u2019s\u00a0<\/span>InputStream<\/span>\u00a0class,\u00a0<\/span>and\u00a0<\/span>the method blocks until the requested number of bytes return.\u00a0<\/span>TCP is a synchronous communication scheme that requires “handshaking” from both sides of the connection as packets are exchanged (SYN and ACK packets).<\/span>\u00a0<\/span>If the data flow is interrupted\u00a0<\/span>abruptly\u00a0<\/span>due to connectivity issues the application effectively hangs<\/span>, and even if connection will get re-established,\u00a0<\/span>application will stay in that state until it will get restarted.<\/span>\u00a0<\/span><\/p>\n

As explained in this\u00a0<\/span>quite\u00a0<\/span>extensive\u00a0<\/span>article<\/span><\/a>\u00a0on this subject, r<\/span>ole of\u00a0<\/span>timeouts\u00a0<\/span>is to deal with statement<\/span>\u00a0executions that are taking longer than expected, but also to deal with so called \u201cdead connections\u201d to wait indefinitely\u00a0<\/span>and impact application availability.<\/span>\u00a0<\/span><\/p>\n

This is because both timeouts by default are set to -1, which means\u00a0<\/span>that\u00a0<\/span>t<\/span>he application will wait forever<\/span>\u00a0and not react to what will happen on the service side.<\/span>\u00a0So, why those values are not set to meaningful numbers by default in the JDBC driver?<\/span>\u00a0The answer is that safe values will depend again a lot on specific workloads, so a best practice is actually that\u00a0<\/span>developers and system engineers will set them based\u00a0<\/span>on what \u201cregular\u201d and expected\u00a0<\/span>query execution times will be.<\/span>\u00a0<\/span><\/p>\n

If\u00a0<\/span>max execution time under regular conditions is<\/span>, let\u2019s say, 2 seconds\u00a0<\/span>a reasonable\u00a0<\/span>queryTimeout<\/span><\/i>\u00a0could be<\/span>\u00a0for example 15 seconds (to make sure\u00a0<\/span>we cover temporarily slow query execution times without necessarily failing) whole<\/span>\u00a0an healthy<\/span>\u00a0<\/span>socketTimeout<\/span><\/i>\u00a0<\/span>could be 30 seconds<\/span>\u00a0so that, worst case, the application will be able to<\/span>\u00a0react to the exception and apply proper retry logic as previously described.<\/span>\u00a0<\/span><\/p>\n

Connection pooling<\/span>\u00a0libraries<\/span>\u00a0<\/span><\/h2>\n

For\u00a0<\/span>the reasons\u00a0<\/span>mentioned above,\u00a0<\/span>connection pooling\u00a0<\/span>can play a role\u00a0<\/span>in overall application reliability<\/span>.\u00a0<\/span>They all offer an implicit<\/span>\u00a0(e.g. supporting JDBC4\u00a0<\/span>Connection.isValid<\/span>() method)<\/span>\u00a0or explicit\u00a0<\/span>way of<\/span>\u00a0specify a test query to<\/span>\u00a0valid<\/span>ate<\/span>\u00a0connections\u00a0<\/span>when\u00a0<\/span>application is requesting them, or to setup idle timeout to\u00a0<\/span>evict connections from the pool after a given timeout when they are not used.<\/span>\u00a0<\/span>\u00a0<\/span><\/p>\n

HikariCP<\/span> is <\/span>battle-tested<\/span><\/a>\u00a0<\/span>on\u00a0<\/span>transient<\/span>\u00a0connectivity errors, but it is important to re-iterate the importance of\u00a0<\/span>proper configur<\/span>ation\u00a0<\/span>on both connection pooling\u00a0<\/span>layer<\/span><\/a>\u00a0and on\u00a0<\/span>specific JDBC driver connection properties like the\u00a0<\/span>socketTimeout<\/span><\/i>\u00a0emphasized earlier.<\/span>\u00a0<\/span>This one remain critical to signal to the connection pooling infrastructure that the\u00a0<\/span>underlying connection with database is not responding properly and then it should eliminated from the pool<\/span>\u00a0instead of remaining there forever in a stuck state.<\/span>\u00a0<\/span><\/p>\n

On<\/span>\u00a0connection pooling<\/span>\u00a0specifics<\/span>, in addition to obvious\u00a0<\/span>properties determining pool size (<\/span>minumumIdle<\/span><\/i>\u00a0and\u00a0<\/span>maximumPoolSize<\/span><\/i>, default 10 each but likely dependent on\u00a0<\/span>specific workload<\/span>)<\/span>, there are\u00a0<\/span>some additional<\/span>\u00a0properties that should be carefully considered<\/span>\u00a0for application availability.<\/span>\u00a0<\/span><\/p>\n

connectionTimeout<\/span><\/i>\u00a0controls the amount of time that the app will wait for getting a connection from the pool.\u00a0<\/span>Default is 30 sec<\/span>,\u00a0<\/span>and it makes sense to keep it slightly higher than<\/span>\u00a0JDBC driver\u00a0<\/span>loginTimeout<\/span><\/i>\u00a0in case\u00a0<\/span>all connections in the pool are active and a new one needs to be created.<\/span>\u00a0<\/span><\/p>\n

maxLifetime<\/span><\/i>\u00a0controls<\/span>\u00a0maximum lifetime of a connection\u00a0<\/span>sitting in the pool doing nothing.\u00a0<\/span>It is recommended to set this value<\/span>\u00a0slightly<\/span>\u00a0shorter than any other<\/span>\u00a0<\/span>connection\u00a0<\/span>timeout limit<\/span>.<\/span>\u00a0<\/span><\/p>\n

validationTimeout<\/span><\/i>\u00a0<\/span>basically<\/span>\u00a0contro<\/span>ls how long validation test wil<\/span>l wait before declaring a connection \u201cdead\u201d<\/span>\u00a0and evicted from the pool<\/span>\u00a0if backend will not respond.\u00a0<\/span>Default is 5 sec<\/span>\u00a0and should work well in most\u00a0<\/span>scenarios but<\/span>\u00a0can be decreased if you<\/span>\u2019re looking for faster reaction times in case of transient errors.<\/span>\u00a0<\/span><\/p>\n

Summary<\/span>\u00a0<\/span><\/h2>\n

What this article<\/span>\u00a0and accompanied\u00a0<\/span>code sample<\/span><\/a>\u00a0are demonstrating is that, with<\/span>\u00a0few specific configuration options and additional code libraries<\/span>, customers can implement Java applications for Azure SQL Database\u00a0<\/span>that are more reliable and less impacted by transient service reconfigurations and network disconnections<\/span>.<\/span>\u00a0<\/span><\/p>\n","protected":false},"excerpt":{"rendered":"

A growing number of\u00a0Azure SQL Database\u00a0customers are developing new applications in Java using a JDBC driver from Microsoft or a 3rd\u00a0party.\u00a0These drivers are\u00a0providing quite extensive support\u00a0and\u00a0covering most of database service capabilities and performance expectations but\u00a0there are a couple of areas that deserve some attention.<\/p>\n","protected":false},"author":32624,"featured_media":81,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[1],"tags":[30,168,167],"class_list":["post-142","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-azure-sql","tag-developers","tag-jdbc","tag-reliability"],"acf":[],"blog_post_summary":"

A growing number of\u00a0Azure SQL Database\u00a0customers are developing new applications in Java using a JDBC driver from Microsoft or a 3rd\u00a0party.\u00a0These drivers are\u00a0providing quite extensive support\u00a0and\u00a0covering most of database service capabilities and performance expectations but\u00a0there are a couple of areas that deserve some attention.<\/p>\n","_links":{"self":[{"href":"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-json\/wp\/v2\/posts\/142","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-json\/wp\/v2\/users\/32624"}],"replies":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-json\/wp\/v2\/comments?post=142"}],"version-history":[{"count":0,"href":"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-json\/wp\/v2\/posts\/142\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-json\/wp\/v2\/media\/81"}],"wp:attachment":[{"href":"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-json\/wp\/v2\/media?parent=142"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-json\/wp\/v2\/categories?post=142"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-json\/wp\/v2\/tags?post=142"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}

Connection pooling<\/span>\u00a0is a technique to\u00a0<\/span>maintain a cache of idle database connections that can be\u00a0<\/span>reused for future requests to mitigate the cost of\u00a0<\/span>opening new connections and manage resources efficiently.<\/span>\u00a0In\u00a0<\/span>client libraries like\u00a0<\/span>SqlClient<\/span>\u00a0or ODBC<\/span>, connection pooling capabilities are natively implemented, while generally JDBC drivers are\u00a0<\/span>just implementing required interfaces for\u00a0<\/span>3<\/span>rd<\/span>\u00a0party components<\/span>\u00a0<\/span>to provide these capabilities.<\/span>\u00a0<\/span>Historically speaking, s<\/span>ome\u00a0<\/span>of these\u00a0<\/span>libraries<\/span>\u00a0like\u00a0<\/span>HikariCP<\/span><\/a>,\u00a0<\/span>C3P0<\/span><\/a>,\u00a0<\/span>Dbcp2<\/span><\/a>\u00a0<\/span>or<\/span>\u00a0<\/span>Vibur<\/span><\/a>\u00a0emerged\u00a0<\/span>as\u00a0<\/span>widely used within Java development community.<\/span>\u00a0<\/span><\/p>\n