Advanced Java Connection and Transaction Demarcation and Propagation

Scoping = Demarcation

In this text I will sometimes use the terms "scoping" and "demarcation" interchangeably. By "scope" I mean the scope of a connections life span, or the scope of a transaction. A scope is demarcated using a start scope and end scope demarcation.

Typical Program Flow: Event - Control - Dao - Connection

The typical flow of control in an application that uses a database is a bit like this:

First an event occurs, for instance a user clicks a button in a desktop application, or an HTTP request arrives at your web application. Second a control processes the event. This control typically determines if it is necessary to access the database. If it is, the control accesses the DAO layer. The DAO layer accesses a standard JDBC database connection to carry out its work.

Control Method Scope

A control may either call one or more methods on the same DAO instance, or call methods on multiple DAO instances. This means that the scope of the connection is typically the same as the scope of the control method reacting to the event.

Imagine this call stack:

Event
  --> Control.execute()
      --> PersonDao.readPerson()
          --> Connection //read person via SQL.

      --> ProjectDao.readProject()
          --> Connection // read project via SQL.

      --> ProjectDao.assignPersonToProject()
          --> Connection // update project assignments via SQL.

Notice how two different DAO's were called from inside the control's execute() method. In total the database connection was accessed three times. You don't want every DAO method to open and close its own database connection, let alone run inside its own transaction. You want each DAO method to share the same connection and perhaps even the same transaction.

To make this happen you need to obtain a database connection (or equivalent object) inside the Control.execute() method, and pass this connection (or other object) to each DAO. For instance it could look like this sketchy pseudo code:

Control.execute() {
  Connection connection = null;

  try{
    getConnection();
    PersonDao  personDao  = new PersonDao(connection);
    ProjectDao projectDao = new ProjectDao(connection);

    Person  person  = personDao.readPerson(...);
    Project project = projectDao.readProject(...);

    projectDao.assignPersonToProject(person, project);
  } finally {
    if(connection != null) connection.close();
  }
}

Now, why is this annoying to have to do?

Well, because now the DAO layer does not hide the Connection anymore. In addition, you cannot easily inject the DAO's into the Control instance. Well, you could if you call a setConnection() method on them afterwards. But it's preferable to have fully configured DAO instances injected.

The DaoManager Solution

In the text Dao Manager I described a solution to the problem mentioned in the previous section, that the database connection leaks out of the DAO's.

The DaoManager is a class that you put in between the Control and the DAO's. The DaoManager is born with a single database connection (or the ability to obtain one lazily). From the DaoManager the Control can then access all the DAO's. Each DAO is created lazily and cached, and have the connection from the DaoManager injected into its constructor.

Here's a rough sketch of how it looks:

This way the DaoManager can be injected into the Control completely configured. If the DaoManager has a DataSource injected (or a PersistenceManager in Butterfly Persistence), the DaoManager can obtain a connection lazily, in case a connection is needed by any of the DAO's.

The DaoManager also has a method that can execute some code and close the connection afterwards, or commit the transaction. Here is how a call to that method looks from inside the Control:

public class Control {

  protected DaoManager daoManager = null;

  public Control(DaoManager daoManager){
    this.daoManager = daoManager;
  }

  public void execute(){

    this.daoManager.executeAndClose(new DaoCommand(){
        public Object execute(DaoManager manager){
          daoManager.getDaoA();
          daoManager.getDaoB();
          daoManager.getDaoC();
       }
    });
  }

}

Once the DaoManager.executeAndClose() method finishes the database connection inside the DaoManager is closed.

For more information on how the DaoManager works, see the text Dao Manager.

Even though the DaoManager solution seems to work fine in most cases, there are cases where it is not enough. I'll look at these cases in the next section.

DaoManager Limitations

As you can see from the code example in the previous section, the scope of the connection when managed by the DaoManager.executeAndClose() is the boundaries of the executeAndClose() method.

But, what if you have more than one Control that will respond to a given event, and you want to share the database connection between controls?

For instance, what if your controls were organized into a tree, like they can be if you are using Butterfly Web UI or Wicket? Like this:

Or maybe even harder, into a list of independent controls listening for the same event. This could be the case in a desktop application if each control is registered independently as listeners on e.g. a button. Here is how that would look:

In these cases it is hard (if not impossible) to use the DaoManager solution.

Think about it for a second or two. Imagine if there was a DaoManager between the Control and DAO's in the two diagrams above.

It is the DaoManager's executeAndClose() method that demarcates the life span of the underlying connection. If you call this method from each control's execute() method (or whatever the central execution method in your controls is called), each control will open and close a connection separately. That is exactly what we are trying to avoid. Or, at least be able to avoid whenever desired. There could of course be situations where you actually want the controls to use independent connections.

The ScopingDataSource Solution

The ScopingDataSource is a different solution to the problem of demarcating connection and transaction life spans (scopes). It is a solution I implemented for my persistence API, Mr Persister, which is now continued in the Butterfly Persistence API. The ScopingDataSource will be moved to Butterfly Persistence from around version 5.2.0 or 5.4.0 which will be released in 2009.

The ScopingDataSource is an implementation of the standard Java interface javax.sql.DataSource. It is a wrapper around a standard DataSource implementation. This means, that instead of calling directly to your DataSource instance to obtain a connection, you call the ScopingDataSource instead.

The ScopingDataSource has the following scope demarcation methods:

    beginConnectionScope();
    endConnectionScope();

    beginTransactionScope();
    endTransactionScope();
    abortTransactionScope();

These methods demarcate the beginning and end of connection and transaction life spans. The methods are explained in a little more detail in the following sections.

Connection Scoping

Your control start out by calling the ScopingDataSource.beginConnectionScope(). Once this method is called, whenever the tread that called this method calls the ScopingDataSource.getConnection() method, the same connection instance is returned.

The Connection that is returned by the ScopingDataSource is also a wrapper around the real Connection instance. This ScopingConnection ignores all calls to the close() method, so the underlying connection can be reused.

When you are ready to close the connection your control calls the ScopingDataSource.endConnectionScope(), and the currently open connection (if any) is closed. From here on the ScopingDataSource behaves just like a regular DataSource, returning a new Connection for every call to getConnection().

The principle is illustrated here:

The calls to beginConnectionScope() and endConnectionScope() do not have to be located within the same method, nor within the same class. They just have to be located within the same thread of execution.

The advantage of the ScopingDataSource solution is that your DAO's don't need to know anything about it. Your DAO's can take DataSource in their constructor, and obtain a connection from that. It doesn't matter whether your DAO's obtain a single connection internally in the DAO and share it between methods, or obtain a new connection and close it again inside each method. Your DAO's can also take a Connection in their contructor, and still not have to know anything about how the scoping works. In short, you get a lot of freedom in your DAO design.

The only time the wrapped connection can cause problems is if you are using an API that requires the original connection. Oracle's Advanced Queue (AQ) API has this problem (or at least had it in 2005). The API did not work with connection wrappers. Only with Oracle's own Connection implementation. Dead annoying!

Here is a code sketch showing how the ScopingDataSource is used:

public class DBControlBase {

 protected ScopingDataSource scopingDataSource = null;

 public DBControlBase(ScopingDataSource dataSource){
   this.scopingDataSource = dataSource;
 }

 public void execute(){
   Exception error = null;
   try{
     this.scopingDataSource.beginConnectionScope();
     doExecute();

   } catch(Exception e){
      error = e;
   } finally {
      this.scopingDataSource.endConnectionScope(e);
   }
 }

 public void doExecute() {
   PersonDao  personDao  = new PersonDao (this.scopingDataSource);
   ProjectDao projectDao = new ProjectDao(this.scopingDataSource);

   //do DAO work here...
 }

}

You can just extend the DBControlBase and override the doExecute() method, then all connection scoping is done for you.

But wait, isn't the effect pretty much the same as what you got with the DaoManager? Yes, in the code sketch above it is. But you have more options now. The connection scope demarcation method calls do not have to be embedded inside your control. They can be called outside the Control.execute() method too, or inside a parent control. Here is how that could look:

Once you start playing around with this idea, there are lots of possibilities.

Another option is to use method interception, like Spring does. If your Control class implements an interface, you can implement a Dynamic Proxy which implements the same interface. Your control is then wrapped in this dynamic proxy. When the execute() method is called on the control interface, this dynamic proxy will call the beginConnectionScope(), then call your controls execute() method, and finally the endConnectionScope(). Here is a code sketch of a dynamic proxy:

public class ConnectionScopeProxy implements InvocationHandler{

  protected ScopingDataSource scopingDataSource = null;
  protected Object            wrappedTarget     = null;

  public ConnectionScopeProxy(
        ScopingDataSource dataSource,
        Object            target) {

    this.scopingDataSource = dataSource;
    this.wrappedTarget     = target;
  }


  public Object invoke(Object proxy, Method method, Object[] args)
  throws Throwable {

    Exception error = null;

    try{
      this.scopingDataSource.beginConnectionScope();
      method.invoke(this.wrappedTarget, args);
    } catch(Exception e){
      error = e;
    } finally {
      this.scopingDataSource.endConnectionScope(error);
    }

  }
}

Transaction Scoping

Transaction scoping is done in pretty much the same way as connection scoping. The only difference is that you call beginTransactionScope() and endTransactionScope() instead.

When a connection is obtained from the ScopingDataSource while inside a transaction scope, connection.setAutoCommit(false) is called. That puts the connection into transactional state.

When a transaction scope ends the transaction is committed and the connection closed. If an error occurs during the commit, the transaction is automatically aborted. If an exception is thrown before the endTransactionScope() method is called, you should catch that exception and call abortTransactionScope(Exception) with that exception.

Here is a code sketch:

Exception error = null;
try{
  scopingDataSource.beginTransactionScope();

  //do DAO work inside transaction
} catch(Exception e){
  error = e;
} finally{
  if(error == null){
    scopingDataSource.endTransactionScope();
  } else {
    scopingDataSource.abortTransactionScope(e);
  }
}

Transaction scopes can be nested inside connection scopes. If a transaction scope is nested inside a connection scope, ending the transaction scope will not close the connection. That way you can nest multiple transaction scopes within a single connection scope, resulting in multiple transactions being committed on the same connection.

Closing this text I will show a transaction scope dynamic proxy code sketch:

public class TransactionScopeProxy implements InvocationHandler{

  protected ScopingDataSource scopingDataSource = null;
  protected Object            wrappedTarget     = null;

  public TransactionScopeProxy(
        ScopingDataSource dataSource,
        Object            target) {

    this.scopingDataSource = dataSource;
    this.wrappedTarget     = target;
  }


  public Object invoke(Object proxy, Method method, Object[] args)
  throws Throwable {

    Exception error = null;

    try{
      this.scopingDataSource.beginTransactionScope();
      method.invoke(this.wrappedTarget, args);
    } catch(Exception e){
      error = e;
    } finally {
      if(error == null){
        this.scopingDataSource.endTransactionScope();
      } else {
        this.scopingDataSource.abortTransactionScope(error);
      }
    }
  }
}