困扰我两年的Quartz的unscheduleJob和deleteJob问题

说来羞愧，这个问题困扰我2年时间，系统要通过UI设置定时任务，定时任务持久化到数据库，保证系统重启后定时任务仍可以执行，当然也需要随时取消定时任务，一旦取消任务，UI被锁住（其实是后台的处理程序被锁住了），需要3-5分钟才能恢复，一般情况下，后台会报数据库的错误。

 

问题出现后，我反复测试也没办法解决，反正锁住之后，一旦恢复，再操作，处理起来就很快了，先不管了。

 

去年新人要开发另外的系统，需要相同的组件，当然也就遇到了相同的问题，他也解决不了，因此他改用直接删数据的办法解决，但是工期紧，没太在意。现在我接手升级系统，发现问题依旧，还是绕不开这问题，没办法，按照正规的方法修正这个问题吧，要不不知要流毒多久

 

1.反复实验多次后，新人的做法无法解决问题，

2.重新改回用API的unscheduleJob和deleteJob处理，无效

3.怀疑是quartz的版本问题，原版本是1.6.0，下载了两个版本1.6.6，1.8.3，1.8.3版本太高，spring不认，换成1.6.6吧，问题依旧

4.没办法，修改log4j的配置文件，搞清quartz的原理再解决吧，日志如下

 写道

2010-07-22 13:37:40,796 (http-8888-Processor24) [ DBSemaphore.java:106:DEBUG] Lock 'TRIGGER_ACCESS' is desired by: http-8888-Processor24
2010-07-22 13:37:40,796 (http-8888-Processor24) [ DBSemaphore.java:106:DEBUG] Lock 'TRIGGER_ACCESS' is desired by: http-8888-Processor24
2010-07-22 13:37:40,796 (http-8888-Processor24) [StdRowLockSemaphore.java:89 :DEBUG] Lock 'TRIGGER_ACCESS' is being obtained: http-8888-Processor24
2010-07-22 13:37:40,796 (http-8888-Processor24) [StdRowLockSemaphore.java:89 :DEBUG] Lock 'TRIGGER_ACCESS' is being obtained: http-8888-Processor24
2010-07-22 13:37:40,796 (http-8888-Processor24) [ DBSemaphore.java:115:DEBUG] Lock 'TRIGGER_ACCESS' given to: http-8888-Processor24
2010-07-22 13:37:40,796 (http-8888-Processor24) [ DBSemaphore.java:115:DEBUG] Lock 'TRIGGER_ACCESS' given to: http-8888-Processor24
2010-07-22 13:37:40,828 (http-8888-Processor24) [ DBSemaphore.java:142:DEBUG] Lock 'TRIGGER_ACCESS' returned by: http-8888-Processor24
2010-07-22 13:37:40,828 (http-8888-Processor24) [ DBSemaphore.java:142:DEBUG] Lock 'TRIGGER_ACCESS' returned by: http-8888-Processor24
2010-07-22 13:37:53,406 (http-8888-Processor24) [ DBSemaphore.java:106:DEBUG] Lock 'TRIGGER_ACCESS' is desired by: http-8888-Processor24
2010-07-22 13:37:53,406 (http-8888-Processor24) [ DBSemaphore.java:106:DEBUG] Lock 'TRIGGER_ACCESS' is desired by: http-8888-Processor24
2010-07-22 13:37:53,406 (http-8888-Processor24) [StdRowLockSemaphore.java:89 :DEBUG] Lock 'TRIGGER_ACCESS' is being obtained: http-8888-Processor24
2010-07-22 13:37:53,406 (http-8888-Processor24) [StdRowLockSemaphore.java:89 :DEBUG] Lock 'TRIGGER_ACCESS' is being obtained: http-8888-Processor24
2010-07-22 13:37:53,406 (http-8888-Processor24) [ DBSemaphore.java:115:DEBUG] Lock 'TRIGGER_ACCESS' given to: http-8888-Processor24
2010-07-22 13:37:53,406 (http-8888-Processor24) [ DBSemaphore.java:115:DEBUG] Lock 'TRIGGER_ACCESS' given to: http-8888-Processor24
2010-07-22 13:37:53,421 (http-8888-Processor24) [ DBSemaphore.java:142:DEBUG] Lock 'TRIGGER_ACCESS' returned by: http-8888-Processor24
2010-07-22 13:37:53,421 (http-8888-Processor24) [ DBSemaphore.java:142:DEBUG] Lock 'TRIGGER_ACCESS' returned by: http-8888-Processor24
$$ unschedualResult = false
2010-07-22 13:37:53,421 (http-8888-Processor24) [ DBSemaphore.java:106:DEBUG] Lock 'TRIGGER_ACCESS' is desired by: http-8888-Processor24
2010-07-22 13:37:53,421 (http-8888-Processor24) [ DBSemaphore.java:106:DEBUG] Lock 'TRIGGER_ACCESS' is desired by: http-8888-Processor24
2010-07-22 13:37:53,421 (http-8888-Processor24) [StdRowLockSemaphore.java:89 :DEBUG] Lock 'TRIGGER_ACCESS' is being obtained: http-8888-Processor24
2010-07-22 13:37:53,421 (http-8888-Processor24) [StdRowLockSemaphore.java:89 :DEBUG] Lock 'TRIGGER_ACCESS' is being obtained: http-8888-Processor24
2010-07-22 13:37:53,421 (http-8888-Processor24) [ DBSemaphore.java:115:DEBUG] Lock 'TRIGGER_ACCESS' given to: http-8888-Processor24
2010-07-22 13:37:53,421 (http-8888-Processor24) [ DBSemaphore.java:115:DEBUG] Lock 'TRIGGER_ACCESS' given to: http-8888-Processor24
2010-07-22 13:38:07,796 (http-8888-Processor24) [ ExceptionHelper.java:97 :DEBUG] Detected JDK support for nested exceptions.
2010-07-22 13:38:07,796 (http-8888-Processor24) [ ExceptionHelper.java:97 :DEBUG] Detected JDK support for nested exceptions.
2010-07-22 13:38:07,796 (http-8888-Processor24) [ DBSemaphore.java:142:DEBUG] Lock 'TRIGGER_ACCESS' returned by: http-8888-Processor24
2010-07-22 13:38:07,796 (http-8888-Processor24) [ DBSemaphore.java:142:DEBUG] Lock 'TRIGGER_ACCESS' returned by: http-8888-Processor24
org.quartz.JobPersistenceException: Couldn't remove job: ORA-02292: 违反完整约束条件 (SYS_C0016583) - 已找到子记录
[See nested exception: java.sql.SQLException: ORA-02292: 违反完整约束条件 (SYS_C0016583) - 已找到子记录
]
at org.quartz.impl.jdbcjobstore.JobStoreSupport.removeJob(JobStoreSupport.java:1313)
at org.quartz.impl.jdbcjobstore.JobStoreSupport$6.execute(JobStoreSupport.java:1293)
at org.quartz.impl.jdbcjobstore.JobStoreCMT.executeInLock(JobStoreCMT.java:244)
at org.quartz.impl.jdbcjobstore.JobStoreSupport.removeJob(JobStoreSupport.java:1289)
at org.quartz.core.QuartzScheduler.deleteJob(QuartzScheduler.java:835)
at org.quartz.impl.StdScheduler.deleteJob(StdScheduler.java:300)

 

记得源代码里有StdRowLockSemaphore，而且怪怪的，在系统的代码里面，检查quartz的代码，也有这个类，比较吧

package org.quartz.impl.jdbcjobstore;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

/**
 * Internal database based lock handler for providing thread/resource locking 
 * in order to protect resources from being altered by multiple threads at the 
 * same time.
 * 
 * @author jhouse
 */
public class StdRowLockSemaphore extends DBSemaphore {

    /*
     * ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
     * 
     * Constants.
     * 
     * ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
     */

    public static final String SELECT_FOR_LOCK = "SELECT * FROM "
            + TABLE_PREFIX_SUBST + TABLE_LOCKS + " WHERE " + COL_LOCK_NAME
            + " = ? FOR UPDATE";

    /*
     * ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
     * 
     * Constructors.
     * 
     * ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
     */

    /**
     * This constructor is for using the <code>StdRowLockSemaphore</code> as
     * a bean.
     */
    public StdRowLockSemaphore() {
        super(DEFAULT_TABLE_PREFIX, null, SELECT_FOR_LOCK);
    }
    
    public StdRowLockSemaphore(String tablePrefix, String selectWithLockSQL) {
        super(tablePrefix, selectWithLockSQL, SELECT_FOR_LOCK);
    }

    /*
     * ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
     * 
     * Interface.
     * 
     * ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
     */

    /**
     * Execute the SQL select for update that will lock the proper database row.
     */
    protected void executeSQL(Connection conn, String lockName, String expandedSQL) throws LockException {
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
        	conn.setAutoCommit(false);
            ps = conn.prepareStatement(expandedSQL);
            ps.setString(1, lockName);
            
            if (getLog().isDebugEnabled()) {
                getLog().debug(
                    "Lock '" + lockName + "' is being obtained: " + 
                    Thread.currentThread().getName());
            }
            rs = ps.executeQuery();
            if (!rs.next()) {
                throw new SQLException(Util.rtp(
                    "No row exists in table " + TABLE_PREFIX_SUBST + 
                    TABLE_LOCKS + " for lock named: " + lockName, getTablePrefix()));
            }
            conn.commit();
        } catch (SQLException sqle) {
            //Exception src =
            // (Exception)getThreadLocksObtainer().get(lockName);
            //if(src != null)
            //  src.printStackTrace();
            //else
            //  System.err.println("--- ***************** NO OBTAINER!");

            if (getLog().isDebugEnabled()) {
                getLog().debug(
                    "Lock '" + lockName + "' was not obtained by: " + 
                    Thread.currentThread().getName());
            }
            
            throw new LockException("Failure obtaining db row lock: "
                    + sqle.getMessage(), sqle);
        } finally {
            if (rs != null) { 
                try {
                    rs.close();
                } catch (Exception ignore) {
                }
            }
            if (ps != null) {
                try {
                    ps.close();
                } catch (Exception ignore) {
                }
            }
        }
    }

    protected String getSelectWithLockSQL() {
        return getSQL();
    }

    public void setSelectWithLockSQL(String selectWithLockSQL) {
        setSQL(selectWithLockSQL);
    }
}

 

发现我们的代码里面加入了这两行

conn.setAutoCommit(false);

conn.commit();

 

这代码是系统的创建者留下的，当时是基于什么考虑，也不可考了（代码里面没有注释）。他这么做肯定是欠妥的。

 

另外的问题，这么个问题延绵两年时间才搞定。。。

 

 

 

评论

4 楼 xubaolin9 2010-11-18  

public void execute(JobExecutionContext ctx)

ctx里面你看看吧，应该是有的

3 楼 hhww0101 2010-11-10  

hhww0101 写道

我想问下楼主，知道定时任务执行时，如何取到任务的信心吗？比如triggerName 和 groupName

是信息，不是信心，打错字了！

2 楼 hhww0101 2010-11-10  

我想问下楼主，知道定时任务执行时，如何取到任务的信心吗？比如triggerName 和 groupName

1 楼 renwanly 2010-07-23  

conn.setAutoCommit(false); //关闭自动提交，目的是为了使用数据库事务提交；
conn.commit(); //提交数据库事务，如果其中出现任何异常则不会执行到这里；
conn.rollback(); //如果出现任何异常，可以回滚此事务；