Connection handling in Taskana

Preliminary Observations

In order to access a database a java.sql.Connections is required. These connections are retrieved from a data source via the dataSource.getConnection() call and they are returned back to the data source via a connection.close()-call.

A data source can be pooled or not:

• If a data source is not pooled, each getConnection() call will open a new connection with a new database-access-path. Each connection.close() call will terminate the current connection to the database. These are very 'expensive' operations.
• If a data source is pooled, the dataSource.getConnection() call checks whether an opened connection exists in the pool that satisfies appropriate selection criteria and returns that connection, or the data source will create a new connection. The connection.close() call returns the connection into the pool leaving it open for subsequent work.

The environment can be managed or not:

• In a managed environment, connection pools and global transactions are used.
  After a connection has been opened for the first time via a dataSource.getConnection() call, it is enlisted with the surrounding transaction. A connection.close() call simply returns that connection back to the pool, leaving it open. Subsequent dataSource.getConnection() calls will return that specific connection from the pool that participates in the active transaction. Commit on the connection will occur when the surrounding transaction is committed. As part of commit processing, the connection is delisted from the transaction and subsequently can be used for work in another transaction.
• In a non managed environment, there is no surrounding transaction present and therefore participation in a transaction can not be used as selection criteria.
  MyBatis' PooledDataSource, for example, implements a connection pool from which any idle connection is returned by the getConnection() call. Before it is returned to the data source, MyBatis rolls back any work that has been done previously on that connection since the last commit.

Each library, that provides API calls that access a database must deal with database connections. Usually this is done by acquiring a database connection at the begin of each API call and 'closing' that connection by the end of the API call.

Taskana

Environment and requirements

Taskana uses MyBatis 3 for JDBC calls. With MyBatis, database connections are wrapped by the SQLSession class. A connection is retrieved from the data source via call SqlSessionManager.startManagedSession() and it is returned to the pool via call SQLSessionManager.close().

→ Since Taskana shall be able to work in managed environments like Spring, JBoss or WebSphere as well as in non-managed environments, it must be able to deal with different environments.

To handle these requirements, the client can choose between 3 connection management modes for Taskana:

These modes are set by the client via the Api call TaskanaEngine.setConnectionManagementMode( mode ).

Per default, Taskana assumes to run in a managed environment that controls global transactions in which Taskana participates. Therefore, the default mode is PARTICIPATE.

In mode PARTICIPATE, Taskana

• retrieves for each API call a connection from the data source via SqlSessionManager.startManagedSession(), which calls dataSource.getConnection().
• returns that connection to the data source by the end of the API call via SqlSessionManager.close(), which issues the connection.close() call.

Taskana relies on the data source to return the correct connection that participates in the surrounding transaction as well as on the infrastructure to trigger commit processing on the connection as part of the transaction's commit processing.
In order to retrieve an appropriate data source, Taskana should be able to lookup data sources from jndi. In addition, it must be possible to inject data sources into Taskana.

For non-managed environments, Taskana implements the modes AUTOCOMMIT and EXPLICIT.
In mode AUTOCOMMIT, Taskana will commit each API call separately.

In mode EXPLICIT, the client will be able to commit several Api calls together. To do this,

• the client has to obtain a java.sql.Connection object from a data source and pass this object to Taskana via a TaskanaEngine.setConnection() call.
• Then the client can call several API methods. Each of these methods will use the connection that was set by the client for the currently used database access.
• The client can control commit processing by calling Connection.commit() or Connection.rollback().
• The client can either call TaskanaEngine.setConnection(null) or TaskanaEngine.closeConnection() to close the connection. Both calls got the same effect: Taskana closes the connection that was specified by the client and switches into mode PARTICIPATE.

Implementation notes

• TaskanaEngineImpl uses class SqlSessionManager which implements SqlSessionFactory as well as a SqlSession.

• dataSource.getConnection() is called under the covers by method SqlSessionManager.startManagedSession)=, which is called in method TaskanaEngineImpl.initSqlSession().

• connection.close() is called under the covers by TaskanaEngineImpl.closeConnection() (in mode EXPLICIT) and TaskanaEngineImpl.returnConnection() (in modes PARTICIPATE and AUTOCOMMIT)

In several situations, Taskana will call TaskanaEngine.setConnectionManagementMode() implicitly. In these situations, the client does not need to call this method.

• If the client doesn't call TaskanaEngine.setConnectionManagementMode(), Taskana works in mode PARTICIPATE.
• If TaskanaEngine.setConnection(connection) is called, Taskana will switch automatically into the EXPLICIT mode.
• If TaskanaEngine.setConnection(null) or TaskanaEngine.closeConnection() is called, Taskana will close the client's connection and switch automatically into the PARTICIPATE mode.
• If Taskana is in mode EXPLICIT, a connection was set by the client and TaskanaEngine.setConnectionManagementMode(AUTOCOMMIT) or TaskanaEngine.setConnectionManagementMode(PARTICIPATE) is called, Taskana will close the connection that was set by the client.

The session stack

Each external API method is wrapped into

try {
	TaskanaEngineImpl.openConnection();
	// ... Do some work ...
} finally {
   TaskanaEngine.returnConnection();
}

If an API method calls another API method, the code in the inner method is nested between two openConnection ... returnConnection calls.

In order to avoid duplicate opening / closing of sessions, TaskanaEngineImpl uses a threadlocal sessionStack in the following way:

• Each time, a TaskanaEngineImpl.openConnection() call is received, we push the current sessionManager onto the stack.
• On the first call to TaskanaEngineImpl.openconnection(), we call sessionManager.startManagedSession() to open a connection.
• On each call to TaskanaEngineImpl.returnConnection() we pop one instance of sessionManager from the stack.
• When the stack becomes empty, we close the sqlSession by calling sessionManager.close()