3

It seems that JDBC Spec does not elaborate the accurate meanings of alive or idle connections in the datasource connection pool. Is it just implementation specific? How does DBCP2 or HikariCP actually inspect the connection status?

Will a connection without active transactions be marked idle below? 

// assuming auto commit disabled already

Connection connection = dataSource.getConnection(); // idle?
/* issue some JDBC SQL statements */ // active?
connection.commit(); /* or rollback */ //idle again?
sof
  • 9,113
  • 16
  • 57
  • 83
  • 2
    JDBC doesn't talk about idle, because idle is not relevant for JDBC. However usually (but not always), idle is used to refer to connections in the pool (which means that they are not checked out). – Mark Rotteveel Sep 28 '15 at 13:16

2 Answers2

3

You should distinct between idle / borrowed pooled connections (as discussed by Nathan) and valid / invalid connections.

Those attributes are orthogonal, but typically there is a property e.g. testOnBorrow for Tomcat Connection Pool which when set can enforce that the borrowed connection is valid (i.e. is able to execute the validation query).

Also a time limit can be set to remove connection that are idle for a long time from the connection pool (e.g. minEvictableIdleTimeMillis). Similar is valid for removal of abandoned connections, i.e those that were "forgotten" to be returned to the pool by the application. (removeAbandonedTimeout).

You are right it's all implementation specific.

Marmite Bomber
  • 19,886
  • 4
  • 26
  • 53
2

"Idle" means the connection isn't currently being loaned out. (As Mark Rotteveel says in the comments, this isn't relevant to the JDBC spec, since JDBC doesn't specifically deal with connection pools.) "Active" means the connection is valid and can be used. It's easy to tell if something is idle, since the pool is explicitly keeping track of that, and harder to know if it's valid, because it's a network connection and could fail anytime.

A connection pool checks the status of connections by running some test sql on the connection, where the sql is usually supplied through a configuration property, something trivial like "select 1 from dual".

Nathan Hughes
  • 94,330
  • 19
  • 181
  • 276