Quartz locks SQL using select xxxx for update when useDBLocks is true. (https://github.com/quartz-scheduler/quartz/blob/master/quartz-core/src/main/java/org/quartz/impl/jdbcjobstore/StdRowLockSemaphore.java)

And dontSetAutoCommitFalse is default use false in JobStoreSupport class. (https://github.com/quartz-scheduler/quartz/blob/master/quartz-core/src/main/java/org/quartz/impl/jdbcjobstore/JobStoreSupport.java)

But Spring Framework invokes setDontSetAutoCommitFalse(true) with springTxDataSource.

https://github.com/spring-projects/spring-framework/blob/68757073b0cf69c41d5c17e8abdffbc388cdbabe/spring-context-support/src/main/java/org/springframework/scheduling/quartz/LocalDataSourceJobStore.java#L101-L103

and Quartz code is:

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

(code in https://github.com/quartz-scheduler/quartz/blob/master/quartz-core/src/main/java/org/quartz/impl/jdbcjobstore/JobStoreSupport.java, line 803)

the executeInLock method code is 

    @Override
    protected Object executeInLock(
            String lockName, 
            TransactionCallback txCallback) throws JobPersistenceException {
        boolean transOwner = false;
        Connection conn = null;
        try {
            if (lockName != null) {
                // If we aren't using db locks, then delay getting DB connection 
                // until after acquiring the lock since it isn't needed.
                if (getLockHandler().requiresConnection()) {
                    conn = getConnection();
                }

                transOwner = getLockHandler().obtainLock(conn, lockName);
            }

            if (conn == null) {
                conn = getConnection();
            }

            return txCallback.execute(conn);
        } finally {
            try {
                releaseLock(lockName, transOwner);
            } finally {
                cleanupConnection(conn);
            }
        }
    }

(code in https://github.com/quartz-scheduler/quartz/blob/master/quartz-core/src/main/java/org/quartz/impl/jdbcjobstore/JobStoreCMT.java, line 225)

In MySQL, select for update need run in a transaction; see: https://dev.mysql.com/doc/refman/5.7/en/innodb-locking-reads.html

but now is not begin transaction for run TransactionCallback.

So spring framework need remove this code or change to the following:

// Configure transactional connection settings for Quartz.
        setDataSource(TX_DATA_SOURCE_PREFIX + getInstanceName());
        setDontSetAutoCommitFalse(false);//bug fix need changeto false for transactional connection 

        // Register transactional ConnectionProvider for Quartz.
        DBConnectionManager.getInstance().addConnectionProvider(
                TX_DATA_SOURCE_PREFIX + getInstanceName(),
                new ConnectionProvider() {
                    @Override
                    public Connection getConnection() throws SQLException {
                        // Return a transactional Connection, if any.
                        return DataSourceUtils.doGetConnection(dataSource);
                    }
                    @Override
                    public void shutdown() {
                        // Do nothing - a Spring-managed DataSource has its own lifecycle.
                    }
                    @Override
                    public void initialize() {
                        // Do nothing - a Spring-managed DataSource has its own lifecycle.
                    }
                }
        );

        // Non-transactional DataSource is optional: fall back to default
        // DataSource if not explicitly specified.
        DataSource nonTxDataSource = SchedulerFactoryBean.getConfigTimeNonTransactionalDataSource();
        final DataSource nonTxDataSourceToUse = (nonTxDataSource != null ? nonTxDataSource : this.dataSource);

        // Configure non-transactional connection settings for Quartz.
        setNonManagedTXDataSource(NON_TX_DATA_SOURCE_PREFIX + getInstanceName());
if(nonTxDataSource != null  ){
        setDontSetAutoCommitFalse(true);// bug fix need changeto true for none transactional connectio
}

Comment From: sbrannen

I've edited your comment to improve the formatting. You might want to check out this Mastering Markdown guide for future reference.

Comment From: sbrannen

So spring framework need remove this code or change to the following:

You pasted quite a large amount of code which makes it difficult to understand what you are trying to convey.

Please explicitly state what changes you think are required.

In addition, please provide a sample application (or test) which demonstrates the failure you are describing.

Comment From: lizongbo

Sorry, not proficient enough in English. Thank you for your help in optimizing the typography format, after submitting Issue yesterday, I found that the typography was not neat, and after trying to adjust it, I found that the effect was still not very good. I wrote the complete demo project, please refer to the attachment file springquartzdemo.zip, import the gradle project, and then configure it as a mysql server that can be connected in application.properties, and import quartz's /org/quartz/impl/jdbcjobstore/tables_mysql_innodb.sql in the database. Then run the class: com.lizongbo.springdemo.SpringQuartzBugTest with the main method, and then look at the console log and you can see that some of the methods are not running in the transaction. Since most of the scenarios are triggered by executeInNonManagedTXLock, this method calls getNonManagedTXConnection to get the connection, and disables autoCommit through isDontSetNonManagedTXConnectionAutoCommitFalse judgment, so the actual business will hardly trigger bugs.

bug001 bug002 bug003

Comment From: dmytrobr

setDontSetAutoCommitFalse(true); this line in LocalDataSourceJobStore tells Quartz to leave autoCommit property of JdbcConnection as is (which is "true" by default). And thus all the connections acquired from LocalDataSourceJobStore don't participate in Spring-managed transactions, as each SQL statement will be it's own transaction (because autoCommit == true) Tagging @sbrannen as you have asked for more leaner explanation. I'm not author of this issue, but was debugging Spring/Quartz issue in my software and found the issue was already raised here.

Comment From: lizongbo

setDontSetAutoCommitFalse(true); this line in LocalDataSourceJobStore tells Quartz to leave autoCommit property of JdbcConnection as is (which is "true" by default). And thus all the connections acquired from LocalDataSourceJobStore don't participate in Spring-managed transactions, as each SQL statement will be it's own transaction (because autoCommit == true) Tagging @sbrannen as you have asked for more leaner explanation. I'm not author of this issue, but was debugging Spring/Quartz issue in my software and found the issue was already raised here.

Thank you for your support, due to my lack of experience with Issues, I was unable to make the developers of Spring understand the cause of the bug. I customized the class inheritance of LocalDataSourceJobStore in my project to circumvent this bug.

Comment From: ffpy

I also encountered the same issue as you. In the end, I found that enabling Spring's transaction at the point where QuartzScheduler is invoked resolved the issue. For example:

@Transactional(rollbackFor = Exception.class)
public void addTrigger() {
    scheduler.scheduleJob(trigger);
}

This way, when obtaining the connection, the autoCommit value will be set to false. You can find a detailed explanation in this article: https://juejin.cn/post/6844904131174334472

Comment From: jhoeller

Indeed, LocalDataSourceJobStore is meant to be used within a Spring-managed transaction which is why we disable the manual transaction management in Quartz there. If anyone prefers to use standard Quartz behavior instead, feel free to explicitly configure the regular JobStoreCMT class.