Last time I have outlined the importance of timeouts. Without a carefully considered timeouts our application can become unresponsive easily. In this post I will focus on configuring various timeouts related to interaction with database. I am going to focus specifically on relational databases. The principles and practices however can be applied equally well to other types of databases.

Different kinds of timeouts

Asking a database for results of a query is one of the most common activities a back end application will do. Let us decompose this simple task into steps:

  1. Take the connection out of the pool
  2. Validate the acquired connection
  3. Send statement(s) to database
  4. Read query results

Establish database connection(s) in pool

Dealing with raw database connections is almost always done with the help of connection pool. Establishing a connection to database is very expensive compared to running a simple statement. The pool alleviates this cost by reusing connections for as long as needed.

class JdbcTimeoutTest { 
@Test
fun `mysql getConnection`() {
val mysqlDataSource = mysqlDataSource()
assertTimeoutPreemptively(Duration.ofMinutes(3)) {
useResource { mysqlDataSource.connection }
}
}
@Test
fun `postgresql getConnection`() {
val mysqlDataSource = postgreSQLDataSource()
assertTimeoutPreemptively(Duration.ofMinutes(3)) {
useResource { mysqlDataSource.connection }
}
}
fun useResource(resourceProvider: () -> AutoCloseable) {
val start = Instant.now()
try {
resourceProvider().use {
println("Completed in ${Duration.between(start, Instant.now())}")
}
} catch (e: Exception) {
println("Error $e after ${Duration.between(start, Instant.now())}")
}
}

fun mysqlDataSource(): MysqlDataSource {
return MysqlDataSource().apply {
this.setURL("jdbc:mysql://localhost:3306/database")
this.user = "user"
this.setPassword("password")
}
}
fun postgreSQLDataSource(): PGSimpleDataSource {
return PGSimpleDataSource().apply {
this.user = "user"
this.password = "password"
this.databaseName = "database"
this.serverName = "localhost"
}
}
}
nc -k -l 3306 # listen on MySQL port, PostgreSQL uses 5432 by default

Take the connection out of the pool

Reusing database connections gives the application great performance boost. However, writing an efficient and bug free database connection pool is no easy task thus we should all rely on proven solutions. In JVM world there are multiple choices when it comes to JDBC:

  • DBCP 2 A recently resurrected project which has a potential of being applicable to all resources pools with its commons-pool2 module.
  • Tomcat JDBC Connection Pool Commonly used with lots of configuration options. Came to be as a replacement of dbcp.

Send statement(s) to database and read query results

We have finally arrived at the most common usage. Every query that we send to a database should have a timeout configured either at the statement level or at the transaction level. When it comes to individual statements, there is setQueryTimeout available:


class CustomTimeoutsDataSource(val innerDataSource: DataSource, private val queryTimeoutProperties: DataSourceConfiguration.QueryTimeoutProperties) : DataSource by innerDataSource {
override fun getConnection(username: String?, password: String?) = configureTimeouts(innerDataSource.getConnection(username, password))
override fun getConnection() = configureTimeouts(innerDataSource.connection)
private fun configureTimeouts(connection: Connection):Connection = CustomTimeoutsConnection(connection, queryTimeoutProperties)private class CustomTimeoutsConnection(val innerConnection: Connection, private val queryTimeoutProperties: DataSourceConfiguration.QueryTimeoutProperties)
: Connection by innerConnection {
private fun <T : Statement> configure(prepareStatement: T): T {
//0 means no timeout
val desiredTimeout = queryTimeoutProperties.statementQueryTimeoutInSeconds ?: 0
prepareStatement.queryTimeout = desiredTimeout
LOG.trace("Configure timeout {} seconds for statement {}", desiredTimeout, prepareStatement)
return prepareStatement
}
override fun prepareStatement(sql: String?, autoGeneratedKeys: Int) = configure(innerConnection.prepareStatement(sql, autoGeneratedKeys))
override fun prepareStatement(sql: String?, resultSetType: Int, resultSetConcurrency: Int, resultSetHoldability: Int) = configure(innerConnection.prepareStatement(sql, resultSetType, resultSetConcurrency, resultSetHoldability))
override fun prepareStatement(sql: String?) = configure(innerConnection.prepareStatement(sql))
override fun prepareStatement(sql: String?, columnNames: Array<out String>?) = configure(innerConnection.prepareStatement(sql, columnNames))
override fun prepareStatement(sql: String?, resultSetType: Int, resultSetConcurrency: Int) = configure(innerConnection.prepareStatement(sql, resultSetType, resultSetConcurrency))
override fun prepareStatement(sql: String?, columnIndexes: IntArray?)= configure(innerConnection.prepareStatement(sql, columnIndexes))
override fun prepareCall(sql: String?) = configure(innerConnection.prepareCall(sql))
override fun prepareCall(sql: String?, resultSetType: Int, resultSetConcurrency: Int) = configure(innerConnection.prepareCall(sql, resultSetType, resultSetConcurrency))
override fun prepareCall(sql: String?, resultSetType: Int, resultSetConcurrency: Int, resultSetHoldability: Int) = configure(innerConnection.prepareCall(sql, resultSetType, resultSetConcurrency, resultSetHoldability))
override fun createStatement() = configure(innerConnection.createStatement())
override fun createStatement(resultSetType: Int, resultSetConcurrency: Int) = configure(innerConnection.createStatement(resultSetType, resultSetConcurrency))
override fun createStatement(resultSetType: Int, resultSetConcurrency: Int, resultSetHoldability: Int) = configure(innerConnection.createStatement(resultSetType, resultSetConcurrency, resultSetHoldability))
override fun toString(): String {
return "CustomTimeoutsConnection(innerConnection=$innerConnection)"
}
}
companion object {
private val LOG = LoggerFactory.getLogger(CustomTimeoutsDataSource::class.java)
}
}
```

Keep all timeouts short

The best timeout is a short one. It is often tempting to increase a query or wait timeout in face of performance or throughput problems. However, doing so will increase the amount of resources blocked on the server and is thus a rarely a good choice. Blocking more and more resources on server e.g. threads, may at some point, cause the entire server to collapse abruptly. I keep the timeouts as short as possible especially when certain API is called often. If you are looking for a good read on the topic I highly recommend Release It! by Michael T. Nygard. This books covers many resiliency related topics including timeouts and provides strategies to avoid increasing them.

Piotr Mionskowski

TDD fan eager to learn new things

Software Development Studio with expertise in mobile & web applications, Blockchain and IOT devices. https://brightinventions.pl