com.zimbra.cs.db.DbPool.java Source code

Java tutorial

Introduction

Here is the source code for com.zimbra.cs.db.DbPool.java

Source

/*
 * ***** BEGIN LICENSE BLOCK *****
 * Zimbra Collaboration Suite Server
 * Copyright (C) 2004, 2005, 2006, 2007, 2008, 2009, 2010, 2011, 2012, 2013, 2014, 2016 Synacor, Inc.
 *
 * This program is free software: you can redistribute it and/or modify it under
 * the terms of the GNU General Public License as published by the Free Software Foundation,
 * version 2 of the License.
 *
 * This program 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 General Public License for more details.
 * You should have received a copy of the GNU General Public License along with this program.
 * If not, see <https://www.gnu.org/licenses/>.
 * ***** END LICENSE BLOCK *****
 */
package com.zimbra.cs.db;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Iterator;
import java.util.Properties;

import org.apache.commons.dbcp.ConnectionFactory;
import org.apache.commons.dbcp.PoolableConnectionFactory;
import org.apache.commons.dbcp.PoolingDataSource;
import org.apache.commons.pool.impl.GenericObjectPool;

import com.zimbra.common.localconfig.LC;
import com.zimbra.common.service.ServiceException;
import com.zimbra.common.util.SystemUtil;
import com.zimbra.common.util.ValueCounter;
import com.zimbra.common.util.ZimbraLog;
import com.zimbra.cs.account.Provisioning;
import com.zimbra.cs.mailbox.Mailbox;
import com.zimbra.cs.stats.ZimbraPerf;

/**
 * @since Apr 7, 2004
 */
public class DbPool {

    private static PoolingDataSource sPoolingDataSource;
    private static String sRootUrl;
    private static String sLoggerRootUrl;
    private static GenericObjectPool sConnectionPool;
    private static boolean sIsInitialized;

    private static boolean isShutdown;
    private static boolean isUsageWarningEnabled = true;

    static ValueCounter<String> sConnectionStackCounter = new ValueCounter<String>();

    public static class DbConnection {
        private final Connection connection;
        private Throwable mStackTrace;
        Integer mboxId;

        DbConnection(Connection conn) {
            connection = conn;
        }

        DbConnection(Connection conn, Integer mboxId) {
            this(conn);
            this.mboxId = mboxId;
        }

        public Connection getConnection() {
            return connection;
        }

        public void setTransactionIsolation(int level) throws ServiceException {
            try {
                connection.setTransactionIsolation(level);
            } catch (SQLException e) {
                throw ServiceException.FAILURE("setting database connection isolation level", e);
            }
        }

        /**
         * Disable foreign key constraint checking for this Connection.  Used by the mailbox restore code
         * so that it can do a LOAD DATA INFILE without hitting foreign key constraint troubles.
         */
        public void disableForeignKeyConstraints() throws ServiceException {
            PreparedStatement stmt = null;
            try {
                String sql = "SET FOREIGN_KEY_CHECKS=0";
                stmt = new StatTrackingPreparedStatement(connection.prepareStatement(sql), sql);
                stmt.execute();
            } catch (SQLException e) {
                throw ServiceException.FAILURE("disabling foreign key constraints", e);
            } finally {
                DbPool.closeStatement(stmt);
            }
        }

        public void enableForeignKeyConstraints() throws ServiceException {
            String sql = "SET FOREIGN_KEY_CHECKS=1";
            PreparedStatement stmt = null;
            try {
                stmt = new StatTrackingPreparedStatement(connection.prepareStatement(sql), sql);
                stmt.execute();
            } catch (SQLException e) {
                throw ServiceException.FAILURE("disabling foreign key constraints", e);
            } finally {
                DbPool.closeStatement(stmt);
            }
        }

        public PreparedStatement prepareStatement(String sql) throws SQLException {
            return new StatTrackingPreparedStatement(connection.prepareStatement(sql), sql);
        }

        public PreparedStatement prepareStatement(String sql, int autoGeneratedKeys) throws SQLException {
            return new StatTrackingPreparedStatement(connection.prepareStatement(sql, autoGeneratedKeys), sql);
        }

        public void rollback() throws ServiceException {
            try {
                connection.rollback();
            } catch (SQLException e) {
                throw ServiceException.FAILURE("rolling back database transaction", e);
            }
        }

        public void commit() throws ServiceException {
            try {
                connection.commit();
            } catch (SQLException e) {
                throw ServiceException.FAILURE("committing database transaction", e);
            }
        }

        public void close() throws ServiceException {
            // first, do any pre-closing ops
            try {
                Db.getInstance().preClose(this);
            } catch (SQLException e) {
                ZimbraLog.sqltrace.warn("DB connection pre-close processing caught exception", e);
            }

            // then actually close the connection
            try {
                connection.close();
            } catch (SQLException e) {
                throw ServiceException.FAILURE("closing database connection", e);
            } finally {
                // Connection is being returned to the pool.  Decrement its stack
                // trace counter.  Null check is required for the stack trace in
                // case this is a maintenance/logger connection, or if dbconn
                // debug logging was turned on between the getConnection() and
                // close() calls.
                if (mStackTrace != null && ZimbraLog.dbconn.isDebugEnabled()) {
                    String stackTrace = SystemUtil.getStackTrace(mStackTrace);
                    synchronized (sConnectionStackCounter) {
                        sConnectionStackCounter.decrement(stackTrace);
                    }
                }
            }
        }

        /** Sets the stack trace used for detecting connection leaks. */
        void setStackTrace(Throwable t) {
            mStackTrace = t;
        }

        public void closeQuietly() {
            try {
                if (!connection.isClosed()) {
                    close(); //need to do all the pre/post closing ops
                }
            } catch (ServiceException e) {
                ZimbraLog.sqltrace.warn("ServiceException ignored", e);
            } catch (SQLException e) {
                ZimbraLog.sqltrace.warn("SQLException ignored", e);
            }
        }

        public void closeQuietly(Statement stmt) {
            if (stmt == null) {
                return;
            }
            try {
                stmt.close();
            } catch (SQLException e) {
                ZimbraLog.sqltrace.warn("SQL error ignored", e);
            }
        }

        public void closeQuietly(ResultSet rs) {
            if (rs == null) {
                return;
            }
            try {
                rs.close();
            } catch (SQLException e) {
                ZimbraLog.sqltrace.warn("SQL error ignored", e);
            }
        }
    }

    static abstract class PoolConfig {
        String mDriverClassName;
        int mPoolSize;
        String mRootUrl;
        String mConnectionUrl;
        String mLoggerUrl;
        boolean mSupportsStatsCallback;
        Properties mDatabaseProperties;
        byte whenExhaustedAction = GenericObjectPool.WHEN_EXHAUSTED_BLOCK;
    }

    /**
     * Initializes the connection pool.  Applications that access the
     * database must call this method before calling {@link DbPool#getConnection}.
     */
    public synchronized static void startup() {
        if (isInitialized()) {
            return;
        }
        PoolConfig pconfig = Db.getInstance().getPoolConfig();

        String drivers = System.getProperty("jdbc.drivers");
        if (drivers == null)
            System.setProperty("jdbc.drivers", pconfig.mDriverClassName);

        sRootUrl = pconfig.mRootUrl;
        sLoggerRootUrl = pconfig.mLoggerUrl;
        sIsInitialized = true;
        waitForDatabase();
    }

    private static void waitForDatabase() {
        DbConnection conn = null;
        final int RETRY_SECONDS = 5;

        while (conn == null) {
            try {
                conn = DbPool.getConnection();
            } catch (ServiceException e) {
                ZimbraLog.misc.warn("Could not establish a connection to the database.  Retrying in %d seconds.",
                        RETRY_SECONDS, e);
                try {
                    Thread.sleep(RETRY_SECONDS * 1000);
                } catch (InterruptedException e2) {
                }
            }
        }

        DbPool.quietClose(conn);
    }

    private static boolean isInitialized() {
        return sIsInitialized;
    }

    /**
     * Updates cached settings, based on the latest LDAP values.
     */
    public static void loadSettings() {
        try {
            long slowThreshold = Provisioning.getInstance().getLocalServer().getDatabaseSlowSqlThreshold();
            DebugPreparedStatement.setSlowSqlThreshold(slowThreshold);
        } catch (ServiceException e) {
            ZimbraLog.system.warn("Unable to set slow SQL threshold.", e);
        }
    }

    /** Initializes the connection pool. */
    private static synchronized PoolingDataSource getPool() {
        if (isShutdown)
            throw new RuntimeException("DbPool permanently shutdown");

        if (sPoolingDataSource != null)
            return sPoolingDataSource;

        PoolConfig pconfig = Db.getInstance().getPoolConfig();
        sConnectionPool = new GenericObjectPool(null, pconfig.mPoolSize, pconfig.whenExhaustedAction, -1,
                pconfig.mPoolSize);
        ConnectionFactory cfac = ZimbraConnectionFactory.getConnectionFactory(pconfig);

        boolean defAutoCommit = false, defReadOnly = false;
        new PoolableConnectionFactory(cfac, sConnectionPool, null, null, defReadOnly, defAutoCommit);

        try {
            Class.forName(pconfig.mDriverClassName).newInstance(); //derby requires the .newInstance() call
            Class.forName("org.apache.commons.dbcp.PoolingDriver");
        } catch (Exception e) {
            ZimbraLog.system.fatal("can't instantiate DB driver/pool class", e);
            System.exit(1);
        }

        try {
            PoolingDataSource pds = new PoolingDataSource(sConnectionPool);
            pds.setAccessToUnderlyingConnectionAllowed(true);

            Db.getInstance().startup(pds, pconfig.mPoolSize);

            sPoolingDataSource = pds;
        } catch (SQLException e) {
            ZimbraLog.system.fatal("can't initialize connection pool", e);
            System.exit(1);
        }

        if (pconfig.mSupportsStatsCallback)
            ZimbraPerf.addStatsCallback(new DbStats());

        return sPoolingDataSource;
    }

    /**
     * return a connection to use for the zimbra database.
     * This must not be called while thread also owns an open connection to a mailbox db.
     * @param
     * @return
     * @throws ServiceException
     */
    public static DbConnection getConnection() throws ServiceException {
        return getConnection(null);
    }

    public static DbConnection getConnection(Mailbox mbox) throws ServiceException {
        if (!isInitialized()) {
            throw ServiceException.FAILURE("Database connection pool not initialized.", null);
        }
        Integer mboxId = mbox != null ? mbox.getId() : -1; //-1 == zimbra db and/or initialization where mbox isn't known yet
        try {
            Db.getInstance().preOpen(mboxId);
            long start = ZimbraPerf.STOPWATCH_DB_CONN.start();

            // If the connection pool is overutilized, warn about potential leaks
            PoolingDataSource pool = getPool();
            checkPoolUsage();

            Connection dbconn = null;
            DbConnection conn = null;
            try {
                dbconn = pool.getConnection();

                if (dbconn.getAutoCommit() != false)
                    dbconn.setAutoCommit(false);

                // We want READ COMMITTED transaction isolation level for duplicate
                // handling code in BucketBlobStore.newBlobInfo().
                if (Db.supports(Db.Capability.READ_COMMITTED_ISOLATION))
                    dbconn.setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED);

                conn = new DbConnection(dbconn, mboxId);
                Db.getInstance().postOpen(conn);
            } catch (SQLException e) {
                try {
                    if (dbconn != null && !dbconn.isClosed())
                        dbconn.close();
                } catch (SQLException e2) {
                    ZimbraLog.sqltrace.warn("DB connection close caught exception", e);
                }
                throw ServiceException.FAILURE("getting database connection", e);
            }

            // If we're debugging, update the counter with the current stack trace
            if (ZimbraLog.dbconn.isDebugEnabled()) {
                Throwable t = new Throwable();
                conn.setStackTrace(t);

                String stackTrace = SystemUtil.getStackTrace(t);
                synchronized (sConnectionStackCounter) {
                    sConnectionStackCounter.increment(stackTrace);
                }
            }
            if (mbox != null)
                Db.registerDatabaseInterest(conn, mbox);

            ZimbraPerf.STOPWATCH_DB_CONN.stop(start);
            return conn;
        } catch (ServiceException se) {
            //if connection open fails unlock
            Db.getInstance().abortOpen(mboxId);
            throw se;
        }
    }

    private static void checkPoolUsage() {
        int numActive = sConnectionPool.getNumActive();
        int maxActive = sConnectionPool.getMaxActive();

        if (numActive <= maxActive * 0.75)
            return;

        String stackTraceMsg = "Turn on debug logging for zimbra.dbconn to see stack traces of connections not returned to the pool.";
        if (ZimbraLog.dbconn.isDebugEnabled()) {
            StringBuilder buf = new StringBuilder();
            synchronized (sConnectionStackCounter) {
                Iterator<String> i = sConnectionStackCounter.iterator();
                while (i.hasNext()) {
                    String stackTrace = i.next();
                    int count = sConnectionStackCounter.getCount(stackTrace);
                    if (count == 0) {
                        i.remove();
                    } else {
                        buf.append(count + " connections allocated at " + stackTrace + "\n");
                    }
                }
            }
            stackTraceMsg = buf.toString();
        }
        String logMsg = "Connection pool is 75%% utilized (%d connections out of a maximum of %d in use).  %s";
        if (isUsageWarningEnabled) {
            ZimbraLog.dbconn.warn(logMsg, numActive, maxActive, stackTraceMsg);
        } else {
            ZimbraLog.dbconn.debug(logMsg, numActive, maxActive, stackTraceMsg);
        }
    }

    /**
     * Returns a new database connection for maintenance operations, such as
     * restore. Does not specify the name of the default database. This
     * connection is created outside the context of the database connection
     * pool.
     */
    public static DbConnection getMaintenanceConnection() throws ServiceException {
        try {
            String user = LC.zimbra_mysql_user.value();
            String pwd = LC.zimbra_mysql_password.value();
            Connection conn = DriverManager.getConnection(sRootUrl + "?user=" + user + "&password=" + pwd);
            conn.setAutoCommit(false);
            return new DbConnection(conn);
        } catch (SQLException e) {
            throw ServiceException.FAILURE("getting database maintenance connection", e);
        }
    }

    /**
     * Returns a new database connection for logger use.
     * Does not specify the name of the default database. This
     * connection is created outside the context of the database connection
     * pool.
     */
    public static DbConnection getLoggerConnection() throws ServiceException {
        try {
            String user = LC.zimbra_mysql_user.value();
            String pwd = null;
            Connection conn = DriverManager.getConnection(sLoggerRootUrl + "?user=" + user + "&password=" + pwd);
            return new DbConnection(conn);
        } catch (SQLException e) {
            throw ServiceException.FAILURE("getting database logger connection", e);
        }
    }

    /**
     * Closes the specified connection (if not <code>null</code>), catches any
     * exceptions, and logs them.
     */
    public static void quietClose(DbConnection conn) {
        if (conn != null) {
            try {
                if (conn.getConnection() != null && !conn.getConnection().isClosed())
                    conn.close();
            } catch (SQLException e) {
                ZimbraLog.sqltrace.warn("quietClose caught exception", e);
            } catch (ServiceException e) {
                ZimbraLog.sqltrace.warn("quietClose caught exception", e);
            }
        }
    }

    /**
     * Does a rollback on the specified connection (if not <code>null</code>),
     * catches any exceptions, and logs them.
     */
    public static void quietRollback(DbConnection conn) {
        if (conn != null) {
            try {
                if (conn.getConnection() != null && !conn.getConnection().isClosed())
                    conn.rollback();
            } catch (SQLException e) {
                ZimbraLog.sqltrace.warn("quietRollback caught exception", e);
            } catch (ServiceException e) {
                ZimbraLog.sqltrace.warn("quietRollback caught exception", e);
            }
        }
    }

    /**
     * Closes a statement and wraps any resulting exception in a ServiceException.
     * @param stmt
     * @throws ServiceException
     */
    public static void closeStatement(Statement stmt) throws ServiceException {
        if (stmt != null) {
            try {
                stmt.close();
            } catch (SQLException e) {
                throw ServiceException.FAILURE("closing statement", e);
            }
        }
    }

    public static void quietCloseStatement(Statement stmt) {
        try {
            if (stmt != null)
                stmt.close();
        } catch (SQLException e) {
        }
    }

    /**
     * Closes a ResultSet and wraps any resulting exception in a ServiceException.
     * @param rs
     * @throws ServiceException
     */
    public static void closeResults(ResultSet rs) throws ServiceException {
        if (rs != null) {
            try {
                rs.close();
            } catch (SQLException e) {
                throw ServiceException.FAILURE("closing statement", e);
            }
        }
    }

    /**
     * Returns the number of connections currently in use.
     */
    public static int getSize() {
        return sConnectionPool.getNumActive();
    }

    /**
     * This is only to be used by DbOfflineMigration to completely close connection to Derby.
     * Note that this doesn't permanently shutdown.  A new getPool() call will restart connections.
     *
     * @throws Exception
     */
    static synchronized void close() throws Exception {
        if (sConnectionPool != null) {
            sConnectionPool.close();
            sConnectionPool = null;
        }
        sPoolingDataSource = null;
        Db.getInstance().shutdown();
    }

    public static synchronized void shutdown() throws Exception {
        isShutdown = true;
        close();
    }

    public static void disableUsageWarning() {
        isUsageWarningEnabled = false;
    }
}