Motivation
Michael T. Nygard warns in “Release it” [1] about the lack of timeouts caused by misconfigured database drivers and connection pools. He argues that if a connection pool gets exhausted when none of its calls return than all others threads get blocked as soon as they require a connection too. His advice is:
Scrutinize Resource Pools
Safe resource pools always limit the time a thread can wait to check out a resource.
(Michael T. Nygard, “Release it”, P. 50)
So I wondered, whether the connection pool HikariCP as well as the JDBC-driver of IBM DB2 and MySQL already ships with default timeouts (and how to adjust them).
HikariCP v2.4.1
HikariCP offers the following parameters (see “HikariCP Configuration”):
- connectionTimeout
Default: 30000ms - validationTimeout
Default: 5000ms
IBM DB2 JDBC Driver v3.65.131 / v4.15.134
The IBM DB2 JDBC Driver offers the following parameters (see “Common IBM Data Server Driver for JDBC and SQLJ properties for all supported database products” and “DB2BaseDataSource class”):
- blockingReadConnectionTimeout
Default: 0 (no timeout). - commandTimeout
Default: 0 (no timeout). This property was introduced in v3.64 and v4.14. - connectionTimeout
Default: 0 (no timeout). This property was introduced in v3.64 and v4.14. - loginTimeout
Default: 0 (timeout is the default system timeout). - enableTimeoutForCursors (only LUW)
Default: 1 (controlled by the commandTimeout and queryTimeoutInterruptProcessingMode properties)
There are also several timeout related properties:
- queryTimeoutInterruptProcessingMode
- timerLevelForQueryTimeOut
Note that I concentrated on z/OS and LUW environments without rerouting nor clustering (so I skipped z/OS data sharing group, DB2 pureScale instance and IBM Informix high availability cluster). For those there are numerous additional parameters to set.
Also note that com.ibm.db2.jcc.DB2DataSource already contains a connection pool. So if you’re packing this datasource into the connection pool of your choice you will get a connection pool inside a connection pool. Which is … let’s say … inappropriate. Use com.ibm.db2.jcc.DB2SimpleDataSource instead.
MySQL Connector/J v5.1.36
MySQL Connector/J offers the following parameters (see “Driver/Datasource Class Names, URL Syntax and Configuration Properties for Connector/J”):
- connectTimeout
Default: 0 (no timeout) - socketTimeout
Default: 0 (no timeout) - netTimeoutForStreamingResults
Default: 600s.
There are also some timeout related properties:
- interactiveClient
- queryTimeoutKillsConnection
Note that I once again skipped properties for load balancing environments.
Conclusion (<tl:dr/>)
HikariCP already ships with default timeouts (but you can and should adjust them to your needs). IBM DB2 JDBC Driver and MySQL Connector/J are timeout-less by default, so to satisfy Nygard’s “Scrutinize Resource Pools” advice you have to turn them on explicitly.
Reference
- [1] . 2007. “Release It!: Design and Deploy Production-Ready Software”. O’Reilly UK Ltd.