quartz 集群 事务 问题

问题描述

网络分区出现时，quartz集群quartz_triggers、quartz_cron_triggers、quartz_job_details，三张表数据不一致。

分析

基本一看就能猜到是事务问题。

查找问题与解决

1、使用配置

org.quartz.jobStore.class=org.quartz.impl.jdbcjobstore.JobStoreTX

是带有事务处理的，不知道为何事务未回滚
2、debugger源码

这里进行持久化操作

可以看到，Connection，但是奇怪没有看到开启事务和提交事务，按理storeJob和storeTrigger应该在同一个事务当中。

当程序执行完closeStatement(ps)之后，detail表中竟然有数据了，而另外两张表并无数据。那么可以说明这两个操作(storeJob和storeTrigger)根本不在同一个事务当中，或者根本没有开启事务。

这个方法获取Connection，可以看到conn.setAutoCommit(false)并未执行，说明事务自动提交了。

/**
     * Sets this connection's auto-commit mode to the given state.
     * If a connection is in auto-commit mode, then all its SQL
     * statements will be executed and committed as individual
     * transactions.  Otherwise, its SQL statements are grouped into
     * transactions that are terminated by a call to either
     * the method <code>commit</code> or the method <code>rollback</code>.
     * By default, new connections are in auto-commit
     * mode.
     * <P>
     * The commit occurs when the statement completes. The time when the statement
     * completes depends on the type of SQL Statement:
     * <ul>
     * <li>For DML statements, such as Insert, Update or Delete, and DDL statements,
     * the statement is complete as soon as it has finished executing.
     * <li>For Select statements, the statement is complete when the associated result
     * set is closed.
     * <li>For <code>CallableStatement</code> objects or for statements that return
     * multiple results, the statement is complete
     * when all of the associated result sets have been closed, and all update
     * counts and output parameters have been retrieved.
     *</ul>
     * <P>
     * <B>NOTE:</B>  If this method is called during a transaction and the
     * auto-commit mode is changed, the transaction is committed.  If
     * <code>setAutoCommit</code> is called and the auto-commit mode is
     * not changed, the call is a no-op.
     *
     * @param autoCommit <code>true</code> to enable auto-commit mode;
     *         <code>false</code> to disable it
     * @exception SQLException if a database access error occurs,
     *  setAutoCommit(true) is called while participating in a distributed transaction,
     * or this method is called on a closed connection
     * @see #getAutoCommit
     */
    void setAutoCommit(boolean autoCommit) throws SQLException;

public int insertJobDetail(Connection conn, JobDetail job)
        throws IOException, SQLException {
        try {
            insertResult = ps.executeUpdate();
        } finally {
            closeStatement(ps);
        }
        return insertResult;
    }

至此closeStatement(ps);自动提交了事务。
而两个存储(storeJob和storeTrigger)，各自提交各自的事务。所以出现连接超时，并不会全部回滚。
3、如何让他不自动提交

prop.put("org.quartz.jobStore.dontSetAutoCommitFalse", "false");

按理这个配置添加上quartz就不会自动提交了，debugger源码发现，该属性(org.quartz.jobStore.dontSetAutoCommitFalse)值依旧是true。

if (!isDontSetAutoCommitFalse()) {
	conn.setAutoCommit(false);
}

所以该代码依旧不执行。
继续debugger

public void setDontSetAutoCommitFalse(boolean b) {
    dontSetAutoCommitFalse = b;
}

发现这里先是false，后又变成了true

@Override
public void initialize(ClassLoadHelper loadHelper, SchedulerSignaler signaler) throws SchedulerConfigException {
	// Configure transactional connection settings for Quartz.
	setDataSource(TX_DATA_SOURCE_PREFIX + getInstanceName());
	setDontSetAutoCommitFalse(true);
}

发现在这里被设置成true，那么清晰了，配置里面给了false，然后又被这里的true覆盖了。为什么会走这里呢？查看调用栈
org.quartz.impl.StdSchedulerFactory#instantiate()里面调用了js的instantiate方法，代码太长不贴了

查看发现js对象竟然是LocalDataSourceJobStore类型，与我们配置JobStoreTX竟然不符。

怀疑配置未生效，debugger发现配置生效了。

private void initSchedulerFactory(StdSchedulerFactory schedulerFactory) throws SchedulerException, IOException {
		CollectionUtils.mergePropertiesIntoMap(this.quartzProperties, mergedProps);
		if (this.dataSource != null) {
			mergedProps.put(StdSchedulerFactory.PROP_JOB_STORE_CLASS, LocalDataSourceJobStore.class.getName());
		}
		schedulerFactory.initialize(mergedProps);
	}

发现在这里org.quartz.jobStore.class配置被修改为了LocalDataSourceJobStore.class.getName()，原因是我们给quartz注入了spring管理的dataSource。
有个疑问，为何不能用spring管理的datasource？为何用了spring的datasource他会强制自动提交事务？这个设计出于什么考虑？

factory.setDataSource(dataSource);

修改配置
发现项目无法启动。。。、

ConnectionProvider class 'org.quartz.utils.C3p0PoolingConnectionProvider' could not be instantiated

debugger找到这里，设置连接池，再向上寻找添加配置，设置连接池为项目使用的hikaricp

prop.put("org.quartz.dataSource.mmfDB.provider", "hikaricp");

至此项目启动成功，debugger发现JobStore也成功设置为JobStoreTX。
再次debugger发现
程序不再因closeStatement而自动提交事务

closeStatement(ps);

转而走conn.commit();统一提交事务。

    /**
     * Commit the supplied connection
     *
     * @param conn (Optional)
     * @throws JobPersistenceException thrown if a SQLException occurs when the
     * connection is committed
     */
    protected void commitConnection(Connection conn)
        throws JobPersistenceException {

        if (conn != null) {
            try {
                conn.commit();
            } catch (SQLException e) {
                throw new JobPersistenceException(
                    "Couldn't commit jdbc connection. "+e.getMessage(), e);
            }
        }
    }

附上全部配置

@Configuration
public class QuartzConfig {

    @Bean
    public SchedulerFactoryBean schedulerFactoryBean() {
        Properties prop = new Properties();
        prop.put("org.quartz.scheduler.instanceName", "productionQuartzCluster");
        prop.put("org.quartz.scheduler.instanceId", "AUTO");

        prop.put("org.quartz.threadPool.class", "org.quartz.simpl.SimpleThreadPool");
        prop.put("org.quartz.threadPool.threadCount", "10");
        prop.put("org.quartz.threadPool.threadPriority", "5");

        prop.put("org.quartz.jobStore.class", "org.quartz.impl.jdbcjobstore.JobStoreTX");
        prop.put("org.quartz.jobStore.dontSetAutoCommitFalse", "false");
        prop.put("org.quartz.jobStore.isClustered", "true");
        prop.put("org.quartz.jobStore.clusterCheckinInterval", "5000");
        prop.put("org.quartz.jobStore.maxMisfiresToHandleAtATime", "1");

        prop.put("org.quartz.jobStore.dataSource", "mmfDB");

        prop.put("org.quartz.dataSource.mmfDB.driver", "com.microsoft.sqlserver.jdbc.SQLServerDriver");
        prop.put("org.quartz.dataSource.mmfDB.URL", "jdbc:sqlserver://1.1.1.1:1433;DatabaseName=mmf_dev");
        prop.put("org.quartz.dataSource.mmfDB.user", "dev");
        prop.put("org.quartz.dataSource.mmfDB.password", "password");
        prop.put("org.quartz.dataSource.mmfDB.maxConnections", "10");
        prop.put("org.quartz.dataSource.mmfDB.provider", "hikaricp");

        prop.put("org.quartz.jobStore.misfireThreshold", "12000");
        prop.put("org.quartz.jobStore.tablePrefix", "QRTZ_");
        prop.put("org.quartz.jobStore.selectWithLockSQL", "SELECT * FROM {0}LOCKS UPDLOCK WHERE LOCK_NAME = ?");

        prop.put("org.quartz.jobStore.driverDelegateClass", "org.quartz.impl.jdbcjobstore.MSSQLDelegate");

        SchedulerFactoryBean factory = new SchedulerFactoryBean();

        factory.setQuartzProperties(prop);

        factory.setSchedulerName("productionQuartzCluster");
        factory.setStartupDelay(30);
        factory.setApplicationContextSchedulerContextKey("applicationContextKey");
        factory.setOverwriteExistingJobs(true);
        factory.setAutoStartup(true);

        return factory;
    }
}