Use Spring AOP and JMX to monitor and cancel JDBC statements

The current unsatisfying situation – Long running database statements

Recently, I was working on my client’s application which was giving me some painful headache. The general purpose of the application is to simply automate some activities on a database. To ensure this, a lot of JDBC calls were put into sequence, be it mere simple UPDATE or procedure calls performing some complex data processing. Well, nothing unusual so far.

But once in a while some of those statements took an awful amount of processing time, even worse they simply would not finish causing even more headache and frustration.

Those long running statements/calls were naturally consuming the available worker threads of my server side application. The current working solution was to kill the database session on the server so the corresponding JDBC client statement would finish.

Since the database statements/calls are initiated by the (client) application it would make sense to cancel those statements on the client side via some administrative means. A closer look at the java.sql.Statement interface would be helpful here. More precisely, the cancel() method may become very handy in this occation.

Application architecture – Spring managed beans etc.

The application architecture itself is pretty straight-forward. Spring as a DI container is used so all database related objects are Spring managed beans. Specifically, a few data source beans are configured to access database resources.

The following figure outlines the runtime dependencies of JDBC related objects in the system:

statementsmonitor-jdbcobjectsGiven those spring managed data source beans and Springs AOP facilities the basic idea is to introduce aspects over those objects. Being a huge framework as it has become by now Spring offers various techniques for AOP, let’s quickly review those which are to be applied for my case.

Spring AOP concepts

There is already very good and detailed documentation about Spring AOP concepts out there so I will just briefly sum up the relevant concepts needed for this case (for good reading I recommend http://static.springsource.org/spring/docs/3.2.x/spring-framework-reference/html/aop.html and http://static.springsource.org/spring/docs/3.2.x/spring-framework-reference/html/aop-api.html ).

Aspect

An aspect is a modularization of a concern that cuts across multiple classes, for instance transcations, caching, logging, etc. Aspects may be implemented using regular classes regular classes annotated with the @Aspect annotation (the @AspectJ style).

AOP proxy

An object created by the AOP framework in order to implement the aspect contracts (advise method executions and so on). In the Spring Framework, an AOP proxy will be a JDK dynamic proxy or a CGLIB proxy.

Advice

An advise is an action taken by an aspect at a particular join point. There are different types of advices, some of them are the following

  • Before advice: Advice that executes before a join point, but which does not have the ability to prevent execution flow proceeding to the join point (unless it throws an exception).
  • After returning advice: Advice to be executed after a join point completes normally: for example, if a method returns without throwing an exception.
  • Around advice: Advice that surrounds a join point such as a method invocation. This is the most powerful kind of advice. Around advice can perform custom behavior before and after the method invocation. It is also responsible for choosing whether to proceed to the join point or to shortcut the advised method execution by returning its own return value or throwing an exception.

There are declarative and programmatic approaches to AOP. Both approaches are used in the following to introduce the necessary aspects to the system.

Build a StatementsMonitor
As I pointed out earlier what we eventually aim for is a simple way to cancel active JDBC statements via some sort of administrative activity or tooling. For this purpose, we create the following StatementsMonitor.
package org.goktay.stmtmonitor.stmtmonitor;

import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import com.google.common.collect.ArrayListMultimap;
import com.google.common.collect.ListMultimap;
import com.google.common.collect.Multimaps;

public class StatementsMonitor {
 private ListMultimap<Connection, StatementHolder> statements = Multimaps.synchronizedListMultimap(ArrayListMultimap
 .<Connection, StatementHolder> create());

public void add(final Statement stmt, final String sql) throws SQLException {
 statements.put(stmt.getConnection(), new StatementHolder(sql, stmt));
 }

public void remove(final Connection con) {
 this.statements.removeAll(con);
 }
}

The StatementsMonitor responsibility is basically to hold references to running JDBC statements and their owning connection objects. It offers two methods to simply register newly created statement objects and deregister connection specific statements (see add– and remove-method). A multi-map implementation provided by Google Guava is used for this purpose since multiple statements may be created from a single connection object.

Build a few aspects

Additionally, the following DataSourceMonitorAspect is used to proxy all java.sql.DataSource objects in the application.

package org.goktay.stmtmonitor;

import java.sql.Connection;
import java.util.List;

import javax.inject.Inject;

import org.aspectj.lang.ProceedingJoinPoint;
import org.aspectj.lang.annotation.Around;
import org.aspectj.lang.annotation.Aspect;
import org.aspectj.lang.annotation.Pointcut;
import org.springframework.aop.Advisor;
import org.springframework.aop.framework.ProxyFactory;

@Aspect
public class DataSourceMonitorAspect {

@Inject
 @org.springframework.beans.factory.annotation.Qualifier("connectionQualifier")
 private List advisors;

@Around(value = "anyDataSource()")
 public Object onNewConnection(final ProceedingJoinPoint pjp) throws Throwable {
 Connection retVal = (Connection) pjp.proceed(pjp.getArgs());
 ProxyFactory proxyFactory = new ProxyFactory(retVal);
 for (Advisor adv : this.advisors) {
 proxyFactory.addAdvisor(adv);
 }
 retVal = (Connection) proxyFactory.getProxy();
 return retVal;
 }

@Pointcut("execution (* javax.sql.DataSource.getConnection())")
 private void anyDataSource() {
 }
}

The DataSourceMonitorAspect itself is developed in AspectJ style. The given @Pointcut expression declares the desired execution points at which the DataSourceMonitorAspect will intercept the regular execution flow. So, every time the “getConnection” method is invoked on a java.sql.DataSource object by any other component in the application the DataSourceMonitorAspect’s @Around advice – implemented in “onNewConnection” will be processed.

onNewConnection” first delegates execution to the join point so that we receive a java.sql.Connection object we are mostly interested in.

Via Spring’s classic ProxyFactory class we programmatically introduce a new aspect over java.sql.Connection objects.

The calling clients of java.sql.DataSource will not see any difference since they are returned proxied java.sql.Connection objects. The new aspect – let’s call it ConnectionMonitorAspect – is given some advisors implementing its overall aspect behaviour.

The following two advisors – StatementsRegisteringAdvice and StatementsDeregisteringAdvice – are used for the ConnectionMonitorAspect

package org.goktay.stmtmonitor;

import java.lang.reflect.Method;
import java.sql.Statement;

import javax.inject.Inject;

import org.springframework.aop.AfterReturningAdvice;

public class StatementsRegisteringAdvice implements AfterReturningAdvice {

@Inject
 private StatementsMonitor statementsMonitor;

@Override
 public void afterReturning(final Object returnValue, final Method method, final Object[] args, final Object target)
 throws Throwable {
 if (returnValue instanceof Statement) {
   Statement stmt = Statement.class.cast(returnValue);
   this.statementMonitor.add(stmt, args != null && args.length > 0 ? args[0].toString() : null);
 }
 }
}

 

package org.goktay.stmtmonitor;

import java.lang.reflect.Method;
import java.sql.Connection;

import javax.inject.Inject;

import org.springframework.aop.MethodBeforeAdvice;

public class StatementsDeregisteringAdvice implements MethodBeforeAdvice {

@Inject
 private StatementsMonitor statementsMonitor;

@Override
 public void before(final Method method, final Object[] args, final Object target) throws Throwable {
 if (method.getName().equals("close")) {
   statementMonitor.remove(Connection.class.cast(target));
 }
 }
}

The two advisors are declared as Spring beans in the application context

<aop:aspectj-autoproxy />

<bean id="statementsMonitor" class="org.goktay.StatementsMonitor"/>

<bean id="statementsRegisteringAdvisor" class="org.springframework.aop.aspectj.AspectJExpressionPointcutAdvisor" >
   <qualifier value="connectionQualifier"/>
   <property name="expression" value="execution (java.sql.* java.sql.Connection.*(..))"/>
   <property name="advice" ><bean class="org.goktay.StatementsRegisteringAdvice"/></property>
   <property name="order" value="100"/>
</bean>

<bean id="statementsDeregisteringeAdvisor" class="org.springframework.aop.aspectj.AspectJExpressionPointcutAdvisor">
   <qualifier value="connectionQualifier"/>
   <property name="expression" value="execution (* java.sql.Connection.close(..))"/>
   <property name="advice" ><bean class="org.goktay.StatementsDeregisteringAdvice"/></property>
   <property name="order" value="10"/>
</bean>
<bean id="dataSourceMonitorAspect" class="org.goktay.DataSourceMonitorAspect"/>

There are a few things to mention here. StatementsRegisteringAdvisor is an afterAdvice associated with every public method of java.sql.Connection. If you have a look at the implementation class you will notice we simply check for java.sql.Statement as return type. If that is given a new Statement object has been created and qualifies for registering to the Statementsmonitor.

StatementsDeregisteringAdvisor is a beforeAdvice and is associated with Connection.close() method only. So any time a connection gets closed we simply deregister it from the StatementsMonitor. A final note to add here: a qualifier “connectionQualifier” is used for both advisors so they get all injected into the DataSourceMonitorAspect.

The following figure outlines the previously said:

statementsmonitor-jdbcobjects-advised

  1. DataSourceMonitorAspect declared as regular AspectJ style Spring aspect
  2. The ConnectionMonitorAspect is a mere logical aspect consisting of two advisors which are created on the fly each time a new connection object is requested.
  3. Each time a new statement is created the statement is registered to the StatementsMonitor along with its owning connection. When the connection is closed it becomes deregistered again from the StatementsMonitor eventually deregestering its associated statements as well.

Expose StatementsMonitor as a JMX bean

Now that we have integrated a single component which is able to access all active JDBC statement objects we might want to add additional helper methods to the StatementsMonitor helping us to access and – in case it becomes necessary – cancel them.

package org.goktay.stmtmonitor.stmtmonitor;

import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import org.springframework.jmx.export.annotation.ManagedOperation;
import com.google.common.collect.ArrayListMultimap;
import com.google.common.collect.ListMultimap;
import com.google.common.collect.Multimaps;

public class StatementsMonitor {
 private ListMultimap<Connection, StatementHolder> statements = Multimaps.synchronizedListMultimap(ArrayListMultimap
 .<Connection, StatementHolder> create());

public void add(final Statement stmt, final String sql) throws SQLException {
 statements.put(stmt.getConnection(), new StatementHolder(sql, stmt));
 }

public void remove(final Connection con) {
 this.statements.removeAll(con);
 }
}

@ManagedOperation(description = "Current active JDBC statements.")
 public List<String> getCurrentStatements() {
 List<String> retVal = new ArrayList<String>(100);
 synchronized (this.statements) {
 for (StatementHolder stmt : statements.values()) {
 retVal.add(stmt.toString());
 }
 }

return retVal;
 }

@ManagedOperation(description = "Cancel a statement by its ID")
 public void cancel(final int holderIN) throws SQLException {
 StatementHolder holderToFind = new StatementHolder(holderIN);
 StatementHolder foundHolder = null;
 synchronized (this.statements) {
   for (StatementHolder holder : this.statements.values()) {
    if (holder.equals(holderToFind)) {
     foundHolder = holder;
     break;
    }
   }
 }
 if (foundHolder != null) {
  if (foundHolder.getStmtRef().get() != null) {
   foundHolder.getStmtRef().get().cancel();
  }
 } else {
  if (LOG.isWarnEnabled()) {
   LOG.warn("No statement '" + holderIN + "' to cancel. Possibly already been removed.");
  }
 }
}

Two methods have been added which integrate to the underlying JMX service layer. getCurrentStatements() will simply iterate over the active JDBC statements and will return their descriptive toString() values. The cancel method helps to signal a cancel event to the active JDBC statement. Depending on the underlying JDBC driver and database this may or may not be supported.

Finally, the StatementsMonitor bean can be exposed as a JMX bean via Spring’s MBeanExporter so that it becomes accessible from any JMX compliant tool (e.g. jvisualvm).

What difference did it made…

By now we have solved some of our database issues as well. As a big benefit we are now able to monitor active JDBC statements and ther corresponding SQLs. So we pretty know exactly what queries or procedure calls are being processed. In case, we have a statement which won’t return we simply send a cancel signal via our JMX tool. No more calls to the DBA are necessary.

Cheers !!!