RAIDb2.java

/**
 * C-JDBC: Clustered JDBC.
 * Copyright (C) 2002-2004 French National Institute For Research In Computer
 * Science And Control (INRIA).
 * Contact: c-jdbc@objectweb.org
 * 
 * This library is free software; you can redistribute it and/or modify it
 * under the terms of the GNU Lesser General Public License as published by the
 * Free Software Foundation; either version 2.1 of the License, or any later
 * version.
 * 
 * This library is distributed in the hope that it will be useful, but WITHOUT
 * ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or
 * FITNESS FOR A PARTICULAR PURPOSE. See the GNU Lesser General Public License
 * for more details.
 * 
 * You should have received a copy of the GNU Lesser General Public License
 * along with this library; if not, write to the Free Software Foundation,
 * Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA.
 *
 * Initial developer(s): Emmanuel Cecchet. 
 * Contributor(s): ______________________.
 */

package org.objectweb.cjdbc.controller.loadbalancer.raidb2;

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

import org.objectweb.cjdbc.common.exceptions.BadConnectionException;
import org.objectweb.cjdbc.common.exceptions.NoTransactionStartWhenDisablingException;
import org.objectweb.cjdbc.common.exceptions.UnreachableBackendException;
import org.objectweb.cjdbc.common.i18n.Translate;
import org.objectweb.cjdbc.common.log.Trace;
import org.objectweb.cjdbc.common.sql.AbstractWriteRequest;
import org.objectweb.cjdbc.common.sql.ParsingGranularities;
import org.objectweb.cjdbc.common.sql.SelectRequest;
import org.objectweb.cjdbc.common.sql.StoredProcedure;
import org.objectweb.cjdbc.common.util.ReadPrioritaryFIFOWriteLock;
import org.objectweb.cjdbc.common.xml.DatabasesXmlTags;
import org.objectweb.cjdbc.controller.backend.DatabaseBackend;
import org.objectweb.cjdbc.controller.cache.metadata.MetadataCache;
import org.objectweb.cjdbc.controller.connection.AbstractConnectionManager;
import org.objectweb.cjdbc.controller.loadbalancer.AbstractLoadBalancer;
import org.objectweb.cjdbc.controller.loadbalancer.AllBackendsFailedException;
import org.objectweb.cjdbc.controller.loadbalancer.BackendWorkerThread;
import org.objectweb.cjdbc.controller.loadbalancer.policies.WaitForCompletionPolicy;
import org.objectweb.cjdbc.controller.loadbalancer.policies.createtable.CreateTableException;
import org.objectweb.cjdbc.controller.loadbalancer.policies.createtable.CreateTablePolicy;
import org.objectweb.cjdbc.controller.loadbalancer.policies.createtable.CreateTableRule;
import org.objectweb.cjdbc.controller.loadbalancer.tasks.AbstractTask;
import org.objectweb.cjdbc.controller.loadbalancer.tasks.CommitTask;
import org.objectweb.cjdbc.controller.loadbalancer.tasks.KillThreadTask;
import org.objectweb.cjdbc.controller.loadbalancer.tasks.ReadStoredProcedureTask;
import org.objectweb.cjdbc.controller.loadbalancer.tasks.RollbackTask;
import org.objectweb.cjdbc.controller.loadbalancer.tasks.WriteRequestTask;
import org.objectweb.cjdbc.controller.loadbalancer.tasks.WriteRequestWithKeysTask;
import org.objectweb.cjdbc.controller.loadbalancer.tasks.WriteStoredProcedureTask;
import org.objectweb.cjdbc.controller.requestmanager.RAIDbLevels;
import org.objectweb.cjdbc.controller.requestmanager.TransactionMarkerMetaData;
import org.objectweb.cjdbc.controller.virtualdatabase.ControllerResultSet;
import org.objectweb.cjdbc.controller.virtualdatabase.VirtualDatabase;

/**
 * RAIDb-2 load balancer.
 * <p>
 * This class is an abstract call because the read requests coming from the
 * Request Manager are NOT treated here but in the subclasses. Transaction
 * management and write requests are broadcasted to all backends owning the
 * written table.
 * 
 * @author <a href="mailto:Emmanuel.Cecchet@inria.fr">Emmanuel Cecchet </a>
 * @version 1.0
 */
public abstract class RAIDb2 extends AbstractLoadBalancer
{
  //
  // How the code is organized ?
  // 1. Member variables
  // 2. Constructor(s)
  // 3. Request handling
  // 4. Transaction handling
  // 5. Backend management
  //

  protected ArrayList                   backendBlockingThreads;
  protected ArrayList                   backendNonBlockingThreads;
  protected ReadPrioritaryFIFOWriteLock backendBlockingThreadsRWLock    = new ReadPrioritaryFIFOWriteLock();
  protected ReadPrioritaryFIFOWriteLock backendNonBlockingThreadsRWLock = new ReadPrioritaryFIFOWriteLock();
  // Should we wait for all backends to commit before returning ?
  protected WaitForCompletionPolicy     waitForCompletionPolicy;
  protected CreateTablePolicy           createTablePolicy;

  protected static Trace                logger                          = Trace
                                                                            .getLogger("org.objectweb.cjdbc.controller.loadbalancer.raidb2");

  /*
   * Constructors
   */

  /**
   * Creates a new RAIDb-1 Round Robin request load balancer. A new backend
   * worker thread is created for each backend.
   * 
   * @param vdb the virtual database this load balancer belongs to.
   * @param waitForCompletionPolicy how many backends must complete before
   *          returning the result ?
   * @param createTablePolicy the policy defining how 'create table' statements
   *          should be handled
   * @exception Exception if an error occurs
   */
  public RAIDb2(VirtualDatabase vdb,
      WaitForCompletionPolicy waitForCompletionPolicy,
      CreateTablePolicy createTablePolicy) throws Exception
  {
    super(vdb, RAIDbLevels.RAIDb2, ParsingGranularities.TABLE);

    this.waitForCompletionPolicy = waitForCompletionPolicy;
    backendBlockingThreads = new ArrayList();
    backendNonBlockingThreads = new ArrayList();
    this.createTablePolicy = createTablePolicy;
  }

  /*
   * Request Handling
   */

  /**
   * Returns the number of nodes to wait for according to the defined
   * <code>waitForCompletion</code> policy.
   * 
   * @param nbOfThreads total number of threads
   * @return int number of threads to wait for
   */
  private int getNbToWait(int nbOfThreads)
  {
    int nbToWait;
    switch (waitForCompletionPolicy.getPolicy())
    {
      case WaitForCompletionPolicy.FIRST :
        nbToWait = 1;
        break;
      case WaitForCompletionPolicy.MAJORITY :
        nbToWait = nbOfThreads / 2 + 1;
        break;
      default :
        logger
            .warn(Translate.get("loadbalancer.waitforcompletion.unsupported"));
      case WaitForCompletionPolicy.ALL :
        nbToWait = nbOfThreads;
        break;
    }
    return nbToWait;
  }

  /**
   * Performs a write request. This request is broadcasted to all nodes that
   * owns the table to be written.
   * 
   * @param request an <code>AbstractWriteRequest</code>
   * @return number of rows affected by the request
   * @throws AllBackendsFailedException if all backends failed to execute the
   *           request
   * @exception SQLException if an error occurs
   */
  public int execWriteRequest(AbstractWriteRequest request)
      throws AllBackendsFailedException, SQLException
  {
    return ((WriteRequestTask) execWriteRequest(request, false, null))
        .getResult();
  }

  /**
   * Perform a write request and return the auto generated keys.
   * 
   * @param request the request to execute
   * @param metadataCache the metadataCache if any or null
   * @return auto generated keys.
   * @throws AllBackendsFailedException if all backends failed to execute the
   *           request
   * @exception SQLException if an error occurs
   */
  public ControllerResultSet execWriteRequestWithKeys(
      AbstractWriteRequest request, MetadataCache metadataCache)
      throws AllBackendsFailedException, SQLException
  {
    return ((WriteRequestWithKeysTask) execWriteRequest(request, true,
        metadataCache)).getResult();
  }

  /**
   * Common code for execWriteRequest(AbstractWriteRequest) and
   * execWriteRequestWithKeys(AbstractWriteRequest). The result is given back
   * using member variables execWriteRequestResult and
   * execWriteRequestWithKeysResult defined above.
   * 
   * @param request the request to execute
   * @param useKeys true if this must give an auto generated keys ResultSet
   * @param metadataCache the metadataCache if any or null
   * @throws AllBackendsFailedException if all backends failed to execute the
   *           request
   * @throws SQLException if an error occurs
   */
  private AbstractTask execWriteRequest(AbstractWriteRequest request,
      boolean useKeys, MetadataCache metadataCache)
      throws AllBackendsFailedException, SQLException
  {
    ArrayList backendThreads;
    ReadPrioritaryFIFOWriteLock lock;

    // Handle macros
    handleMacros(request);

    // Determine which list (blocking or not) to use
    if (request.mightBlock())
    { // Blocking
      backendThreads = backendBlockingThreads;
      lock = backendBlockingThreadsRWLock;
    }
    else
    { // Non-blocking
      backendThreads = backendNonBlockingThreads;
      lock = backendNonBlockingThreadsRWLock;
      if ((waitForCompletionPolicy.getPolicy() != WaitForCompletionPolicy.ALL)
          && (request.getTransactionId() != 0))
        waitForAllWritesToComplete(request.getTransactionId());
    }

    try
    {
      lock.acquireWrite();
    }
    catch (InterruptedException e)
    {
      String msg = Translate.get(
          "loadbalancer.backendlist.acquire.writelock.failed", e);
      logger.error(msg);
      throw new SQLException(msg);
    }

    int nbOfThreads = backendThreads.size();
    ArrayList writeList = new ArrayList();
    String tableName = request.getTableName();

    if (request.isCreate())
    { // Choose the backend according to the defined policy
      CreateTableRule rule = createTablePolicy.getTableRule(request
          .getTableName());
      if (rule == null)
        rule = createTablePolicy.getDefaultRule();

      // Ask the rule to pickup the backends
      ArrayList chosen;
      try
      {
        chosen = rule.getBackends(vdb.getBackends());
      }
      catch (CreateTableException e)
      {
        throw new SQLException(Translate.get(
            "loadbalancer.create.table.rule.failed", e.getMessage()));
      }

      // Build the thread list from the backend list
      if (chosen != null)
      {
        for (int i = 0; i < nbOfThreads; i++)
        {
          BackendWorkerThread thread = (BackendWorkerThread) backendThreads
              .get(i);
          if (chosen.contains(thread.getBackend()))
            writeList.add(thread);
        }
      }
    }
    else
    { // Build the list of backends that need to execute this request
      for (int i = 0; i < nbOfThreads; i++)
      {
        BackendWorkerThread thread = (BackendWorkerThread) backendThreads
            .get(i);
        if (thread.getBackend().hasTable(tableName))
          writeList.add(thread);
      }
    }

    nbOfThreads = writeList.size();
    if (nbOfThreads == 0)
    {
      lock.releaseWrite();
      String msg = Translate.get("loadbalancer.execute.no.backend.found",
          request.getSQLShortForm(vdb.getSQLShortFormLength()));
      logger.warn(msg);
      throw new SQLException(msg);
    }
    else
    {
      if (logger.isDebugEnabled())
        logger.debug(Translate.get("loadbalancer.execute.on.several",
            new String[]{String.valueOf(request.getId()),
                String.valueOf(nbOfThreads)}));
    }

    // Create the task
    AbstractTask task;
    if (useKeys)
      task = new WriteRequestWithKeysTask(getNbToWait(nbOfThreads),
          nbOfThreads, request, metadataCache);
    else
      task = new WriteRequestTask(getNbToWait(nbOfThreads), nbOfThreads,
          request);

    synchronized (task)
    {
      // We have to first post the request on each backend before letting the
      // first backend to execute the request. Therefore we have 2 phases:
      //   1. post the task in each thread queue
      //   2. notify each thread to execute the query

      // 1. Post the task
      if (request.isAutoCommit())
      {
        for (int i = 0; i < nbOfThreads; i++)
        {
          BackendWorkerThread thread = (BackendWorkerThread) writeList.get(i);
          synchronized (thread)
          {
            thread.addTask(task);
          }
        }
      }
      else
      {
        for (int i = 0; i < nbOfThreads; i++)
        {
          BackendWorkerThread thread = (BackendWorkerThread) writeList.get(i);
          synchronized (thread)
          {
            thread.addTask(task, request.getTransactionId());
          }
        }
      }

      // 2. Start the task execution on each backend
      for (int i = 0; i < nbOfThreads; i++)
      {
        BackendWorkerThread thread = (BackendWorkerThread) writeList.get(i);
        synchronized (thread)
        {
          thread.notify();
        }
      }

      lock.releaseWrite();

      // Wait for completion (notified by the task)
      try
      {
        // Wait on task
        long timeout = request.getTimeout() * 1000;
        if (timeout > 0)
        {
          long start = System.currentTimeMillis();
          task.wait(timeout);
          long end = System.currentTimeMillis();
          long remaining = timeout - (end - start);
          if (remaining <= 0)
          {
            if (task.setExpiredTimeout())
            { // Task will be ignored by all backends
              String msg = Translate.get("loadbalancer.request.timeout",
                  new String[]{String.valueOf(request.getId()),
                      String.valueOf(task.getSuccess()),
                      String.valueOf(task.getFailed())});
              logger.warn(msg);
              throw new SQLException(msg);
            }
            // else task execution already started, to late to cancel
          }
          // No need to update request timeout since the execution is finished
        }
        else
          task.wait();
      }
      catch (InterruptedException e)
      {
        if (task.setExpiredTimeout())
        { // Task will be ignored by all backends
          String msg = Translate.get("loadbalancer.request.timeout",
              new String[]{String.valueOf(request.getId()),
                  String.valueOf(task.getSuccess()),
                  String.valueOf(task.getFailed())});
          logger.warn(msg);
          throw new SQLException(msg);
        }
        // else task execution already started, to late to cancel
      }

      if (task.getSuccess() > 0)
        return task;
      else
      { // All tasks failed
        ArrayList exceptions = task.getExceptions();
        if (exceptions == null)
          throw new AllBackendsFailedException(Translate.get(
              "loadbalancer.request.failed.all", request.getId()));
        else
        {
          String errorMsg = Translate.get("loadbalancer.request.failed.stack",
              request.getId())
              + "\n";
          for (int i = 0; i < exceptions.size(); i++)
            errorMsg += ((SQLException) exceptions.get(i)).getMessage() + "\n";
          logger.error(errorMsg);
          throw new SQLException(errorMsg);
        }
      }
    }
  }

  /**
   * Implementation specific load balanced read execution.
   * 
   * @param request an <code>SelectRequest</code>
   * @param metadataCache the metadataCache if any or null
   * @return the corresponding <code>java.sql.ResultSet</code>
   * @exception SQLException if an error occurs
   */
  public abstract ControllerResultSet execReadRequest(SelectRequest request,
      MetadataCache metadataCache) throws SQLException;

  /**
   * Execute a read request on the selected backend.
   * 
   * @param request the request to execute
   * @param backend the backend that will execute the request
   * @param metadataCache a metadataCache if any or null
   * @return the ResultSet
   * @throws SQLException if an error occurs
   */
  protected ControllerResultSet executeRequestOnBackend(SelectRequest request,
      DatabaseBackend backend, MetadataCache metadataCache)
      throws SQLException, UnreachableBackendException
  {
    // Handle macros
    handleMacros(request);

    // Ok, we have a backend, let's execute the request
    AbstractConnectionManager cm = backend.getConnectionManager(request
        .getLogin());

    // Sanity check
    if (cm == null)
    {
      String msg = Translate.get("loadbalancer.connectionmanager.not.found",
          new String[]{request.getLogin(), backend.getName()});
      logger.error(msg);
      throw new SQLException(msg);
    }

    // Execute the query
    if (request.isAutoCommit())
    {
      if (waitForCompletionPolicy.getPolicy() != WaitForCompletionPolicy.ALL)
        // We could do something finer grain here by waiting
        // only for writes that depend on the tables we need
        // but is that really worth the overhead ?
        waitForAllWritesToComplete(backend);

      ControllerResultSet rs = null;
      boolean badConnection;
      do
      {
        badConnection = false;
        // Use a connection just for this request
        Connection c = null;
        try
        {
          c = cm.getConnection();
        }
        catch (UnreachableBackendException e1)
        {
          logger.error(Translate.get(
              "loadbalancer.backend.disabling.unreachable", backend.getName()));
          disableBackend(backend);
          throw new UnreachableBackendException(Translate.get(
              "loadbalancer.backend.unreacheable", backend.getName()));
        }

        // Sanity check
        if (c == null)
          throw new UnreachableBackendException(
              "No more connections on backend " + backend.getName());

        // Execute Query
        try
        {
          rs = executeSelectRequestOnBackend(request, backend, c, metadataCache);
          cm.releaseConnection(c);
        }
        catch (SQLException e)
        {
          cm.releaseConnection(c);
          throw new SQLException(Translate.get(
              "loadbalancer.request.failed.on.backend", new String[]{
                  request.getSQLShortForm(vdb.getSQLShortFormLength()),
                  backend.getName(), e.getMessage()}));
        }
        catch (BadConnectionException e)
        { // Get rid of the bad connection
          cm.deleteConnection(c);
          badConnection = true;
        }
      }
      while (badConnection);
      if (logger.isDebugEnabled())
        logger.debug(Translate.get("loadbalancer.execute.on", new String[]{
            String.valueOf(request.getId()), backend.getName()}));
      return rs;
    }
    else
    { // Inside a transaction
      Connection c;
      long tid = request.getTransactionId();
      Long lTid = new Long(tid);

      // Wait for previous writes to complete
      if (waitForCompletionPolicy.getPolicy() != WaitForCompletionPolicy.ALL)
        waitForAllWritesToComplete(backend, request.getTransactionId());

      try
      {
        c = backend.getConnectionForTransactionAndLazyBeginIfNeeded(lTid, cm);
      }
      catch (UnreachableBackendException e1)
      {
        logger.error(Translate.get(
            "loadbalancer.backend.disabling.unreachable", backend.getName()));
        disableBackend(backend);
        throw new SQLException(Translate.get(
            "loadbalancer.backend.unreacheable", backend.getName()));
      }
      catch (NoTransactionStartWhenDisablingException e)
      {
        String msg = Translate.get("loadbalancer.backend.is.disabling",
            new String[]{request.getSQLShortForm(vdb.getSQLShortFormLength()),
                backend.getName()});
        logger.error(msg);
        throw new SQLException(msg);
      }

      // Sanity check
      if (c == null)
        throw new SQLException(Translate.get(
            "loadbalancer.unable.retrieve.connection", new String[]{
                String.valueOf(tid), backend.getName()}));

      // Execute Query
      ControllerResultSet rs = null;
      try
      {
        rs = executeSelectRequestOnBackend(request, backend, c, metadataCache);
      }
      catch (SQLException e)
      {
        throw new SQLException(Translate.get(
            "loadbalancer.request.failed.on.backend", new String[]{
                request.getSQLShortForm(vdb.getSQLShortFormLength()),
                backend.getName(), e.getMessage()}));
      }
      catch (BadConnectionException e)
      { // Connection failed, so did the transaction
        // Disable the backend.
        cm.deleteConnection(tid);
        String msg = Translate.get(
            "loadbalancer.backend.disabling.connection.failure", backend
                .getName());
        logger.error(msg);
        disableBackend(backend);
        throw new SQLException(msg);
      }
      if (logger.isDebugEnabled())
        logger.debug(Translate.get("loadbalancer.execute.transaction.on",
            new String[]{String.valueOf(tid), String.valueOf(request.getId()),
                backend.getName()}));
      return rs;
    }
  }

  /**
   * Execute a stored procedure on the selected backend.
   * 
   * @param proc the stored procedure to execute
   * @param backend the backend that will execute the request
   * @param metadataCache the metadataCache if any or null
   * @return the ResultSet
   * @throws SQLException if an error occurs
   */
  protected ControllerResultSet executeStoredProcedureOnBackend(
      StoredProcedure proc, DatabaseBackend backend, MetadataCache metadataCache)
      throws SQLException, UnreachableBackendException
  {
    // Handle macros
    handleMacros(proc);

    // Ok, we have a backend, let's execute the request
    AbstractConnectionManager cm = backend
        .getConnectionManager(proc.getLogin());

    // Sanity check
    if (cm == null)
    {
      String msg = Translate.get("loadbalancer.connectionmanager.not.found",
          new String[]{proc.getLogin(), backend.getName()});
      logger.error(msg);
      throw new SQLException(msg);
    }

    // Execute the query
    if (proc.isAutoCommit())
    {
      if (waitForCompletionPolicy.getPolicy() != WaitForCompletionPolicy.ALL)
        // We could do something finer grain here by waiting
        // only for writes that depend on the tables we need
        // but is that really worth the overhead ?
        waitForAllWritesToComplete(backend);

      // Use a connection just for this request
      Connection c = null;
      try
      {
        c = cm.getConnection();
      }
      catch (UnreachableBackendException e1)
      {
        logger.error(Translate.get(
            "loadbalancer.backend.disabling.unreachable", backend.getName()));
        disableBackend(backend);
        throw new UnreachableBackendException(Translate.get(
            "loadbalancer.backend.unreacheable", backend.getName()));
      }

      // Sanity check
      if (c == null)
        throw new SQLException(Translate.get(
            "loadbalancer.backend.no.connection", backend.getName()));

      // Execute Query
      ControllerResultSet rs = null;
      try
      {
        rs = AbstractLoadBalancer.executeReadStoredProcedureOnBackend(proc,
            backend, c, metadataCache);
      }
      catch (Exception e)
      {
        throw new SQLException(Translate.get(
            "loadbalancer.storedprocedure.failed.on.backend", new String[]{
                proc.getSQLShortForm(vdb.getSQLShortFormLength()),
                backend.getName(), e.getMessage()}));
      }
      finally
      {
        cm.releaseConnection(c);
      }
      if (logger.isDebugEnabled())
        logger.debug(Translate.get("loadbalancer.storedprocedure.on",
            new String[]{String.valueOf(proc.getId()), backend.getName()}));
      return rs;
    }
    else
    { // Inside a transaction
      Connection c;
      long tid = proc.getTransactionId();
      Long lTid = new Long(tid);

      // Wait for previous writes to complete
      if (waitForCompletionPolicy.getPolicy() != WaitForCompletionPolicy.ALL)
        waitForAllWritesToComplete(backend, proc.getTransactionId());

      try
      {
        c = backend.getConnectionForTransactionAndLazyBeginIfNeeded(lTid, cm);
      }
      catch (UnreachableBackendException e1)
      {
        logger.error(Translate.get(
            "loadbalancer.backend.disabling.unreachable", backend.getName()));
        disableBackend(backend);
        throw new SQLException(Translate.get(
            "loadbalancer.backend.unreacheable", backend.getName()));
      }
      catch (NoTransactionStartWhenDisablingException e)
      {
        String msg = Translate.get("loadbalancer.backend.is.disabling",
            new String[]{proc.getSQLShortForm(vdb.getSQLShortFormLength()),
                backend.getName()});
        logger.error(msg);
        throw new SQLException(msg);
      }

      // Sanity check
      if (c == null)
        throw new SQLException(Translate.get(
            "loadbalancer.unable.retrieve.connection", new String[]{
                String.valueOf(tid), backend.getName()}));

      // Execute Query
      ControllerResultSet rs;
      try
      {
        rs = AbstractLoadBalancer.executeReadStoredProcedureOnBackend(proc,
            backend, c, metadataCache);
      }
      catch (Exception e)
      {
        throw new SQLException(Translate.get(
            "loadbalancer.storedprocedure.failed.on.backend", new String[]{
                proc.getSQLShortForm(vdb.getSQLShortFormLength()),
                backend.getName(), e.getMessage()}));
      }
      if (logger.isDebugEnabled())
        logger.debug(Translate.get("loadbalancer.execute.transaction.on",
            new String[]{String.valueOf(tid), String.valueOf(proc.getId()),
                backend.getName()}));
      return rs;
    }
  }

  /**
   * @see org.objectweb.cjdbc.controller.loadbalancer.AbstractLoadBalancer#execReadStoredProcedure(StoredProcedure,
   *      MetadataCache)
   */
  public ControllerResultSet execReadStoredProcedure(StoredProcedure proc,
      MetadataCache metadataCache) throws SQLException
  {
    ReadStoredProcedureTask task = (ReadStoredProcedureTask) callStoredProcedure(
        proc, true, metadataCache);
    return task.getResult();
  }

  /**
   * @see org.objectweb.cjdbc.controller.loadbalancer.AbstractLoadBalancer#execWriteStoredProcedure(StoredProcedure)
   */
  public int execWriteStoredProcedure(StoredProcedure proc) throws SQLException
  {
    WriteStoredProcedureTask task = (WriteStoredProcedureTask) callStoredProcedure(
        proc, false, null);
    return task.getResult();
  }

  /**
   * Post the stored procedure call in the threads task list.
   * 
   * @param proc the stored procedure to call
   * @param isRead true if the call returns a ResultSet
   * @param metadataCache the metadataCache if any or null
   * @return the task that has been executed (caller can get the result by
   *         calling getResult())
   * @throws SQLException if an error occurs
   */
  private AbstractTask callStoredProcedure(StoredProcedure proc,
      boolean isRead, MetadataCache metadataCache) throws SQLException
  {
    ArrayList backendThreads = backendBlockingThreads;
    ReadPrioritaryFIFOWriteLock lock = backendBlockingThreadsRWLock;

    try
    {
      // Note that a read stored procedure here is supposed to also execute
      // writes and as the scheduler cannot block atomically on multiple tables
      // for a writes, we have to lock as a write even for a read stored
      // procedure. A read-only stored procedure will execute in a separate
      // method call (see executeReadOnlyStoredProcedure).
      lock.acquireWrite();
    }
    catch (InterruptedException e)
    {
      String msg;
      msg = Translate.get("loadbalancer.backendlist.acquire.writelock.failed",
          e);
      logger.error(msg);
      throw new SQLException(msg);
    }

    int nbOfThreads = backendThreads.size();

    // Create the task
    AbstractTask task;
    if (isRead)
      task = new ReadStoredProcedureTask(getNbToWait(nbOfThreads), nbOfThreads,
          proc, metadataCache);
    else
      task = new WriteStoredProcedureTask(getNbToWait(nbOfThreads),
          nbOfThreads, proc);

    synchronized (task)
    {
      int nbOfBackends = 0;

      // Post the task in each backendThread tasklist and wakeup the threads
      for (int i = 0; i < nbOfThreads; i++)
      {
        BackendWorkerThread thread = (BackendWorkerThread) backendThreads
            .get(i);
        if (thread.getBackend().hasStoredProcedure(proc.getProcedureName()))
        {
          nbOfBackends++;
          synchronized (thread)
          {
            if (proc.isAutoCommit())
              thread.addTask(task);
            else
              thread.addTask(task, proc.getTransactionId());
            thread.notify();
          }
        }
      }

      lock.releaseWrite();

      if (nbOfBackends == 0)
      {
        throw new SQLException(Translate.get(
            "loadbalancer.backend.no.required.storedprocedure", proc
                .getProcedureName()));
      }
      else
        task.setTotalNb(nbOfBackends);

      // Wait for completion (notified by the task)
      try
      {
        // Wait on task
        long timeout = proc.getTimeout() * 1000;
        if (timeout > 0)
        {
          long start = System.currentTimeMillis();
          task.wait(timeout);
          long end = System.currentTimeMillis();
          long remaining = timeout - (end - start);
          if (remaining <= 0)
          {
            if (task.setExpiredTimeout())
            { // Task will be ignored by all backends
              String msg = Translate.get(
                  "loadbalancer.storedprocedure.timeout", new String[]{
                      String.valueOf(proc.getId()),
                      String.valueOf(task.getSuccess()),
                      String.valueOf(task.getFailed())});
              logger.warn(msg);
              throw new SQLException(msg);
            }
            // else task execution already started, to late to cancel
          }
          // No need to update request timeout since the execution is finished
        }
        else
          task.wait();
      }
      catch (InterruptedException e)
      {
        if (task.setExpiredTimeout())
        { // Task will be ignored by all backends
          String msg = Translate.get("loadbalancer.storedprocedure.timeout",
              new String[]{String.valueOf(proc.getId()),
                  String.valueOf(task.getSuccess()),
                  String.valueOf(task.getFailed())});
          logger.warn(msg);
          throw new SQLException(msg);
        }
        // else task execution already started, to late to cancel
      }

      if (task.getSuccess() > 0)
        return task;
      else
      { // All tasks failed
        ArrayList exceptions = task.getExceptions();
        if (exceptions == null)
          throw new SQLException(Translate.get(
              "loadbalancer.storedprocedure.all.failed", proc.getId()));
        else
        {
          String errorMsg = Translate.get(
              "loadbalancer.storedprocedure.failed.stack", proc.getId())
              + "\n";
          for (int i = 0; i < exceptions.size(); i++)
            errorMsg += ((SQLException) exceptions.get(i)).getMessage() + "\n";
          logger.error(errorMsg);
          throw new SQLException(errorMsg);
        }
      }
    }
  }

  /*
   * Transaction management
   */

  /**
   * Begins a new transaction.
   * 
   * @param tm the transaction marker metadata
   * @exception SQLException if an error occurs
   */
  public final void begin(TransactionMarkerMetaData tm) throws SQLException
  {
  }

  /**
   * Commits a transaction.
   * 
   * @param tm the transaction marker metadata
   * @exception SQLException if an error occurs
   */
  public void commit(TransactionMarkerMetaData tm) throws SQLException
  {
    long tid = tm.getTransactionId();
    if (waitForCompletionPolicy.getPolicy() != WaitForCompletionPolicy.ALL)
      waitForAllWritesToComplete(tid);

    try
    {
      backendNonBlockingThreadsRWLock.acquireWrite();
    }
    catch (InterruptedException e)
    {
      String msg = Translate.get(
          "loadbalancer.backendlist.acquire.writelock.failed", e);
      logger.error(msg);
      throw new SQLException(msg);
    }

    int nbOfThreads = backendNonBlockingThreads.size();
    ArrayList commitList = new ArrayList();
    Long iTid = new Long(tid);

    // Build the list of backend that need to commit this transaction
    for (int i = 0; i < nbOfThreads; i++)
    {
      BackendWorkerThread thread = (BackendWorkerThread) backendNonBlockingThreads
          .get(i);
      if (thread.getBackend().isStartedTransaction(iTid))
        commitList.add(thread);
    }

    nbOfThreads = commitList.size();
    if (nbOfThreads == 0)
    {
      backendNonBlockingThreadsRWLock.releaseWrite();
      return;
    }

    // Create the task
    CommitTask task = new CommitTask(getNbToWait(nbOfThreads), nbOfThreads, tm
        .getTimeout(), tm.getLogin(), tid);

    synchronized (task)
    {
      // Post the task in each backendThread tasklist and wakeup the threads
      for (int i = 0; i < nbOfThreads; i++)
      {
        BackendWorkerThread thread = (BackendWorkerThread) commitList.get(i);
        synchronized (thread)
        {
          thread.addTask(task, tid);
          thread.notify();
        }
      }

      backendNonBlockingThreadsRWLock.releaseWrite();

      // Wait for completion (notified by the task)
      try
      {
        // Wait on task
        long timeout = tm.getTimeout();
        if (timeout > 0)
        {
          long start = System.currentTimeMillis();
          task.wait(timeout);
          long end = System.currentTimeMillis();
          long remaining = timeout - (end - start);
          if (remaining <= 0)
          {
            if (task.setExpiredTimeout())
            { // Task will be ignored by all backends
              String msg = Translate.get("loadbalancer.commit.timeout",
                  new String[]{String.valueOf(tid),
                      String.valueOf(task.getSuccess()),
                      String.valueOf(task.getFailed())});
              logger.warn(msg);
              throw new SQLException(msg);
            }
            // else task execution already started, to late to cancel
          }
        }
        else
          task.wait();
      }
      catch (InterruptedException e)
      {
        if (task.setExpiredTimeout())
        { // Task will be ignored by all backends
          String msg = Translate.get("loadbalancer.commit.timeout",
              new String[]{String.valueOf(tid),
                  String.valueOf(task.getSuccess()),
                  String.valueOf(task.getFailed())});
          logger.warn(msg);
          throw new SQLException(msg);
        }
        // else task execution already started, to late to cancel
      }

      if (task.getSuccess() > 0)
        return;
      else
      { // All tasks failed
        ArrayList exceptions = task.getExceptions();
        if (exceptions == null)
          throw new SQLException(Translate.get(
              "loadbalancer.commit.all.failed", tid));
        else
        {
          String errorMsg = Translate.get("loadbalancer.commit.failed.stack",
              tid)
              + "\n";
          for (int i = 0; i < exceptions.size(); i++)
            errorMsg += ((SQLException) exceptions.get(i)).getMessage() + "\n";
          logger.error(errorMsg);
          throw new SQLException(errorMsg);
        }
      }
    }
  }

  /**
   * Rollbacks a transaction.
   * 
   * @param tm the transaction marker metadata
   * @exception SQLException if an error occurs
   */
  public void rollback(TransactionMarkerMetaData tm) throws SQLException
  {
    long tid = tm.getTransactionId();
    if (waitForCompletionPolicy.getPolicy() != WaitForCompletionPolicy.ALL)
      waitForAllWritesToComplete(tid);

    try
    {
      backendNonBlockingThreadsRWLock.acquireWrite();
    }
    catch (InterruptedException e)
    {
      String msg = Translate.get(
          "loadbalancer.backendlist.acquire.writelock.failed", e);
      logger.error(msg);
      throw new SQLException(msg);
    }
    int nbOfThreads = backendNonBlockingThreads.size();
    ArrayList rollbackList = new ArrayList();
    Long iTid = new Long(tid);

    // Build the list of backend that need to rollback this transaction
    for (int i = 0; i < nbOfThreads; i++)
    {
      BackendWorkerThread thread = (BackendWorkerThread) backendNonBlockingThreads
          .get(i);
      if (thread.getBackend().isStartedTransaction(iTid))
        rollbackList.add(thread);
    }

    nbOfThreads = rollbackList.size();
    if (nbOfThreads == 0)
    {
      backendNonBlockingThreadsRWLock.releaseWrite();
      return;
    }

    // Create the task
    RollbackTask task = new RollbackTask(getNbToWait(nbOfThreads), nbOfThreads,
        tm.getTimeout(), tm.getLogin(), tid);

    synchronized (task)
    {
      // Post the task in each backendThread tasklist and wakeup the threads
      for (int i = 0; i < nbOfThreads; i++)
      {
        BackendWorkerThread thread = (BackendWorkerThread) rollbackList.get(i);
        synchronized (thread)
        {
          thread.addTask(task, tid);
          thread.notify();
        }
      }

      backendNonBlockingThreadsRWLock.releaseWrite();

      // Wait for completion (notified by the task)
      try
      {
        // Wait on task
        long timeout = tm.getTimeout();
        if (timeout > 0)
        {
          long start = System.currentTimeMillis();
          task.wait(timeout);
          long end = System.currentTimeMillis();
          long remaining = timeout - (end - start);
          if (remaining <= 0)
          {
            if (task.setExpiredTimeout())
            { // Task will be ignored by all backends
              String msg = Translate.get("loadbalancer.rollback.timeout",
                  new String[]{String.valueOf(tid),
                      String.valueOf(task.getSuccess()),
                      String.valueOf(task.getFailed())});
              logger.warn(msg);
              throw new SQLException(msg);
            }
            // else task execution already started, to late to cancel
          }
        }
        else
          task.wait();
      }
      catch (InterruptedException e)
      {
        if (task.setExpiredTimeout())
        { // Task will be ignored by all backends
          String msg = Translate.get("loadbalancer.rollback.timeout",
              new String[]{String.valueOf(tid),
                  String.valueOf(task.getSuccess()),
                  String.valueOf(task.getFailed())});
          logger.warn(msg);
          throw new SQLException(msg);
        }
        // else task execution already started, to late to cancel
      }

      if (task.getSuccess() > 0)
        return;
      else
      { // All tasks failed
        ArrayList exceptions = task.getExceptions();
        if (exceptions == null)
          throw new SQLException(Translate.get(
              "loadbalancer.rollback.all.failed", tid));
        else
        {
          String errorMsg = Translate.get("loadbalancer.rollback.failed.stack",
              tid)
              + "\n";
          for (int i = 0; i < exceptions.size(); i++)
            errorMsg += ((SQLException) exceptions.get(i)).getMessage() + "\n";
          logger.error(errorMsg);
          throw new SQLException(errorMsg);
        }
      }
    }
  }

  /**
   * Waits for all writes of the given transaction in the blocking thread queue
   * to complete before being able to complete the transaction.
   */
  protected void waitForAllWritesToComplete(long transactionId)
      throws SQLException
  {
    try
    {
      backendBlockingThreadsRWLock.acquireWrite();
      // Lock in write to ensure that all writes are posted and we wait in the
      // queue, else a read lock has the priority with the implementation we are
      // using.
    }
    catch (InterruptedException e)
    {
      String msg = Translate.get(
          "loadbalancer.backendlist.acquire.writelock.failed", e);
      logger.error(msg);
      throw new SQLException(msg);
    }

    int nbOfThreads = backendBlockingThreads.size();

    for (int i = 0; i < nbOfThreads; i++)
    {
      BackendWorkerThread thread = (BackendWorkerThread) backendBlockingThreads
          .get(i);
      thread.waitForAllTasksToComplete(transactionId);
    }

    backendBlockingThreadsRWLock.releaseWrite();
  }

  /**
   * Waits for all writes of the given transaction in the blocking thread queue
   * of the given backend to complete before being able to complete the
   * transaction.
   * 
   * @see #executeRequestOnBackend
   */
  protected void waitForAllWritesToComplete(DatabaseBackend backend,
      long transactionId) throws SQLException
  {
    try
    {
      backendBlockingThreadsRWLock.acquireWrite();
      // Lock in write to ensure that all writes are posted and we wait in the
      // queue, else a read lock has the priority with the implementation we are
      // using.
    }
    catch (InterruptedException e)
    {
      String msg = Translate.get(
          "loadbalancer.backendlist.acquire.writelock.failed", e);
      logger.error(msg);
      throw new SQLException(msg);
    }

    int nbOfThreads = backendBlockingThreads.size();

    for (int i = 0; i < nbOfThreads; i++)
    {
      BackendWorkerThread thread = (BackendWorkerThread) backendBlockingThreads
          .get(i);
      if (thread.getBackend() == backend)
        thread.waitForAllTasksToComplete(transactionId);
    }

    backendBlockingThreadsRWLock.releaseWrite();
  }

  /**
   * Waits for all writes in the blocking thread queue of the given backend to
   * complete.
   * 
   * @see #executeRequestOnBackend
   */
  protected void waitForAllWritesToComplete(DatabaseBackend backend)
      throws SQLException
  {
    try
    {
      backendBlockingThreadsRWLock.acquireWrite();
      // Lock in write to ensure that all writes are posted and we wait in the
      // queue, else a read lock has the priority with the implementation we are
      // using.
    }
    catch (InterruptedException e)
    {
      String msg = Translate.get(
          "loadbalancer.backendlist.acquire.writelock.failed", e);
      logger.error(msg);
      throw new SQLException(msg);
    }

    int nbOfThreads = backendBlockingThreads.size();

    for (int i = 0; i < nbOfThreads; i++)
    {
      BackendWorkerThread thread = (BackendWorkerThread) backendBlockingThreads
          .get(i);
      if (thread.getBackend() == backend)
        thread.waitForAllTasksToComplete();
    }

    backendBlockingThreadsRWLock.releaseWrite();
  }

  /*
   * Backends management
   */

  /**
   * Enables a Backend that was previously disabled.
   * <p>
   * Ask the corresponding connection manager to initialize the connections if
   * needed.
   * <p>
   * No sanity checks are performed by this function.
   * 
   * @param db The database backend to enable
   * @param writeEnabled True if the backend must be enabled for writes
   * @throws SQLException if an error occurs
   */
  public void enableBackend(DatabaseBackend db, boolean writeEnabled)
      throws SQLException
  {
    if (writeEnabled)
    {
      // Create 2 worker threads
      BackendWorkerThread blockingThread = new BackendWorkerThread(db, this);
      BackendWorkerThread nonBlockingThread = new BackendWorkerThread(db, this);

      // Add first to the blocking thread list
      try
      {
        backendBlockingThreadsRWLock.acquireWrite();
      }
      catch (InterruptedException e)
      {
        String msg = Translate.get(
            "loadbalancer.backendlist.acquire.writelock.failed", e);
        logger.error(msg);
        throw new SQLException(msg);
      }
      backendBlockingThreads.add(blockingThread);
      backendBlockingThreadsRWLock.releaseWrite();
      blockingThread.start();
      logger.info(Translate.get(
          "loadbalancer.backend.workerthread.blocking.add", db.getName()));

      // Then add to the non-blocking thread list
      try
      {
        backendNonBlockingThreadsRWLock.acquireWrite();
      }
      catch (InterruptedException e)
      {
        String msg = Translate.get(
            "loadbalancer.backendlist.acquire.writelock.failed", e);
        logger.error(msg);
        throw new SQLException(msg);
      }
      backendNonBlockingThreads.add(nonBlockingThread);
      backendNonBlockingThreadsRWLock.releaseWrite();
      nonBlockingThread.start();
      logger.info(Translate.get(
          "loadbalancer.backend.workerthread.non.blocking.add", db.getName()));
      db.enableWrite();
    }

    if (!db.isInitialized())
      db.initializeConnections();
    db.enableRead();
  }

  /**
   * Disables a backend that was previously enabled.
   * <p>
   * Ask the corresponding connection manager to finalize the connections if
   * needed.
   * <p>
   * No sanity checks are performed by this function.
   * 
   * @param db the database backend to disable
   * @throws SQLException if an error occurs
   */
  public synchronized void disableBackend(DatabaseBackend db)
      throws SQLException
  {
    if (db.isWriteEnabled())
    {
      // Start with the backendBlockingThread list
      try
      {
        backendBlockingThreadsRWLock.acquireWrite();
      }
      catch (InterruptedException e)
      {
        String msg = Translate.get(
            "loadbalancer.backendlist.acquire.writelock.failed", e);
        logger.error(msg);
        throw new SQLException(msg);
      }

      int nbOfThreads = backendBlockingThreads.size();

      // Find the right blocking thread
      for (int i = 0; i < nbOfThreads; i++)
      {
        BackendWorkerThread thread = (BackendWorkerThread) backendBlockingThreads
            .get(i);
        if (thread.getBackend().equals(db))
        {
          logger.info(Translate
              .get("loadbalancer.backend.workerthread.blocking.remove", db
                  .getName()));

          backendBlockingThreads.remove(thread);

          synchronized (thread)
          {
            // Kill the thread
            thread.addPriorityTask(new KillThreadTask(1, 1));
            thread.notify();
          }
          break;
        }
      }

      backendBlockingThreadsRWLock.releaseWrite();

      // Continue with the backendNonBlockingThread list

      try
      {
        backendNonBlockingThreadsRWLock.acquireWrite();
      }
      catch (InterruptedException e)
      {
        String msg = Translate.get(
            "loadbalancer.backendlist.acquire.writelock.failed", e);
        logger.error(msg);
        throw new SQLException(msg);
      }

      // Find the right non-blocking thread
      nbOfThreads = backendNonBlockingThreads.size();
      for (int i = 0; i < nbOfThreads; i++)
      {
        BackendWorkerThread thread = (BackendWorkerThread) backendNonBlockingThreads
            .get(i);
        if (thread.getBackend().equals(db))
        {
          logger.info(Translate.get(
              "loadbalancer.backend.workerthread.non.blocking.remove", db
                  .getName()));

          backendNonBlockingThreads.remove(thread);

          synchronized (thread)
          {
            // Kill the thread
            thread.addPriorityTask(new KillThreadTask(1, 1));
            thread.notify();
          }
          break;
        }
      }

      backendNonBlockingThreadsRWLock.releaseWrite();
    }

    db.disable();
    if (db.isInitialized())
      db.finalizeConnections();
  }

  /**
   * @see org.objectweb.cjdbc.controller.loadbalancer.AbstractLoadBalancer#getXmlImpl
   */
  public String getXmlImpl()
  {
    StringBuffer info = new StringBuffer();
    info.append("<" + DatabasesXmlTags.ELT_RAIDb_2 + ">");
    if (createTablePolicy != null)
      info.append(createTablePolicy.getXml());
    if (waitForCompletionPolicy != null)
      info.append(waitForCompletionPolicy.getXml());
    if (macroHandler != null)
      info.append(macroHandler.getXml());
    this.getRaidb2Xml();
    info.append("</" + DatabasesXmlTags.ELT_RAIDb_2 + ">");
    return info.toString();
  }

  /**
   * return xml formatted information about this raidb2 load balancer
   * 
   * @return xml formatted string
   */
  public abstract String getRaidb2Xml();

}

---