We help IT Professionals succeed at work.

We've partnered with Certified Experts, Carl Webster and Richard Faulkner, to bring you two Citrix podcasts. Learn about 2020 trends and get answers to your biggest Citrix questions!Listen Now

x

Failure obtaining db row lock exception while integrating spring with Quartz scheduler

chaitu chaitu
on
Medium Priority
5,435 Views
Last Modified: 2008-01-09
i am trying to run jobs using quartz(i am using 1.5.1 version) ;quartz scheduler integrated with Spring framework.i am getting exception

org.quartz.impl.jdbcjobstore.LockException: Failure obtaining db row lock: ORA-00942: table or view does not exist
 [See nested exception: java.sql.SQLException: ORA-00942: table or view does not exist
]




<?xml version="1.0" encoding="UTF-8"?><!DOCTYPE beans PUBLIC "-//SPRING//DTD BEAN//EN" "http://www.springframework.org/dtd/spring-beans.dtd"><!--
  - Application context definition for "springapp" DispatcherServlet.
  -->

<beans>

<bean id="myDataSourceTarget" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close">
<property name="driverClassName"><value>oracle.jdbc.driver.OracleDriver</value></property>
<property name="url"><value>jdbc:oracle:thin:@xxxxxxx:xxx</value></property>
<property name="username"><value>xxx</value></property>
<property name="password"><value>xxx</value></property>
</bean>
<!--  Often you just need to invoke a method on a specific object. Using the MethodInvokingJobDetailFactoryBean you can do exactly this
-->
<bean id="exampleJob" class="org.springframework.scheduling.quartz.MethodInvokingJobDetailFactoryBean">
<property name="targetObject" ref="exampleBusinessObject"/>
<property name="targetMethod" value="doIt"/>
</bean>

<bean id="exampleBusinessObject" class="bus.ExampleJob"/>



<!--
<bean name="exampleJob" class="org.springframework.scheduling.quartz.JobDetailBean">
  <property name="jobClass" value="bus.ExampleJob"/>
  <property name="jobDataAsMap">
    <map>
      <entry key="timeout" value="5"/>
    </map>
  </property>
</bean>
-->
<bean class="org.springframework.scheduling.quartz.SchedulerFactoryBean">
<property name="autoStartup">
      <value>false</value>
    </property>
<property name="dataSource">
                  <ref bean="myDataSourceTarget" />
</property>
<property name="triggers">
    <list>
      <ref bean="cronTrigger"/>
    </list>
  </property>
<property name="applicationContextSchedulerContextKey"><value>applicationContext</value></property>
<property name="waitForJobsToCompleteOnShutdown"><value>true</value></property>
<property name="quartzProperties">
<props>
<prop key="org.quartz.threadPool.threadCount">10</prop>
<prop key="org.quartz.jobStore.class">org.quartz.simpl.RAMJobStore</prop>
<prop key="org.quartz.jobStore.driverDelegateClass">org.quartz.impl.jdbcjobstore.OracleDelegate</prop>
<prop key="org.quartz.jobStore.selectWithLockSQL"> SELECT * FROM {0}LOCKS UPDLOCK WHERE LOCK_NAME = ?</prop>
</props>
</property>

 
</bean>

<bean id="simpleTrigger" class="org.springframework.scheduling.quartz.SimpleTriggerBean">
  <!-- see the example of method invoking job above -->
  <property name="jobDetail" ref="exampleJob"/>
      <!-- 10 seconds -->
  <property name="startDelay" value="10000"/>
      <!-- repeat every 50 seconds -->
  <property name="repeatInterval" value="50000"/>
</bean>


<bean id="cronTrigger" class="org.springframework.scheduling.quartz.CronTriggerBean">
  <property name="jobDetail" ref="exampleJob"/>
      <!-- run every morning at 6 AM -->
  <property name="cronExpression" value="0 0 6 * * ?"/>
</bean>



</beans>
Comment
Watch Question

CERTIFIED EXPERT
Top Expert 2016

Commented:
>>SELECT * FROM {0}LOCKS UPDLOCK WHERE LOCK_NAME = ?

Make sure the query produced selects a valid table or view and that it's accessible under the account from which it's being run

Author

Commented:
what's this query actually do
CERTIFIED EXPERT
Top Expert 2016

Commented:
No idea - i'd guess it's to do with synching jobs.
Mick BarryJava Developer
CERTIFIED EXPERT
Top Expert 2010

Commented:
you don't have a table named QRTZ_LOCKS in your database
Mick BarryJava Developer
CERTIFIED EXPERT
Top Expert 2010

Commented:
do you have perms for that data store to create tables

Author

Commented:
i created all these tables;

qrtz_job_listeners;                                          
qrtz_trigger_listeners;                                      
qrtz_fired_triggers;                                          
qrtz_simple_triggers;                                        
qrtz_cron_triggers;                                          
qrtz_blob_triggers;                                          
qrtz_triggers;                                                
qrtz_job_details;                                            
qrtz_calendars;                                              
qrtz_paused_trigger_grps;                                    
qrtz_locks;                                                  
qrtz_scheduler_state;              

do i need this one in configuartion file;                          

<prop key="org.quartz.jobStore.selectWithLockSQL"> SELECT * FROM {0}LOCKS UPDLOCK WHERE LOCK_NAME = ?</prop>

Author

Commented:
after creating above table do i need this query( SELECT * FROM {0}LOCKS UPDLOCK WHERE LOCK_NAME = ?)
Mick BarryJava Developer
CERTIFIED EXPERT
Top Expert 2010

Commented:
> do i need this one in configuartion file;                          

Sorry I'm not an oracle person, the default query is:

SELECT * FROM {0}LOCKS WHERE LOCK_NAME = ? FOR UPDATE

Author

Commented:
now i am getting this exception

Initialization of bean failed; nested exception is org.quartz.JobPersistenceException: Couldn't store job: org.springframework.scheduling.quartz.MethodInvokingJobDetailFactoryBean
org.quartz.JobPersistenceException: Couldn't store job: org.springframework.scheduling.quartz.MethodInvokingJobDetailFactoryBean [See nested exception: java.io.NotSerializableException: org.springframework.scheduling.quartz.MethodInvokingJobDetailFactoryBean]
CERTIFIED EXPERT
Top Expert 2016

Commented:
>>MethodInvokingJobDetailFactoryBean

Please post the above

Author

Commented:
2006-03-25 15:47:10,882 ERROR [org.springframework.web.servlet.DispatcherServlet] Context initialization failed
org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'org.springframework.scheduling.quartz.SchedulerFactoryBean' defined in ServletContext resource [/WEB-INF/springapp-servlet.xml]: Initialization of bean failed; nested exception is org.quartz.JobPersistenceException: Couldn't store job: org.springframework.scheduling.quartz.MethodInvokingJobDetailFactoryBean
org.quartz.JobPersistenceException: Couldn't store job: org.springframework.scheduling.quartz.MethodInvokingJobDetailFactoryBean [See nested exception: java.io.NotSerializableException: org.springframework.scheduling.quartz.MethodInvokingJobDetailFactoryBean]
      at org.quartz.impl.jdbcjobstore.JobStoreSupport.storeJob(JobStoreSupport.java:849)
      at org.quartz.impl.jdbcjobstore.JobStoreCMT.storeJob(JobStoreCMT.java:309)
      at org.quartz.core.QuartzScheduler.addJob(QuartzScheduler.java:687)
      at org.quartz.impl.StdScheduler.addJob(StdScheduler.java:261)
      at org.springframework.scheduling.quartz.SchedulerFactoryBean.addJobToScheduler(SchedulerFactoryBean.java:754)
      at org.springframework.scheduling.quartz.SchedulerFactoryBean.registerJobsAndTriggers(SchedulerFactoryBean.java:705)
      at org.springframework.scheduling.quartz.SchedulerFactoryBean.afterPropertiesSet(SchedulerFactoryBean.java:521)
      at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.invokeInitMethods(AbstractAutowireCapableBeanFactory.java:1059)
CERTIFIED EXPERT
Top Expert 2016

Commented:
(See my last comment)

Author

Commented:
i didnt get u
CERTIFIED EXPERT
Top Expert 2016

Commented:
Please post the code
Mick BarryJava Developer
CERTIFIED EXPERT
Top Expert 2010

Commented:
you cannot use that factory bean with a persitenet store, they are not compatible.
If you weant a persistent job then you need to implement it yourself.

Author

Commented:
if u see the SchedulerFactoryBean class u can set the Datasource in it;

http://www.springframework.org/docs/api/org/springframework/scheduling/quartz/SchedulerFactoryBean.html


package bus;
import org.springframework.scheduling.quartz.*;
import org.quartz.*;

public class ExampleJob extends QuartzJobBean {

  private int timeout;
 
  /**
   * Setter called after the ExampleJob is instantiated
   * with the value from the JobDetailBean (5)
   */
  public void setTimeout(int timeout) {
    this.timeout = timeout;
  }
 
  protected void executeInternal(JobExecutionContext ctx)
  throws JobExecutionException {

        System.out.println("in....................");
      // do the actual work
  }

  public void doIt() {
    // do the actual work
              System.out.println("doIt...................");

  }

}

in the doIt() method i want to get database connection using JNDI;
Java Developer
CERTIFIED EXPERT
Top Expert 2010
Commented:
then do the JNDI lookup in your bean (or just give it a DataSource then as a parameter)

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts

Author

Commented:
i solved the problem like this;Is there any other approach other than this;




public class ExampleJob extends QuartzJobBean {

  private int timeout;
  private  DataSource dataSource;

  /**
   * Setter called after the ExampleJob is instantiated
   * with the value from the JobDetailBean (5)
   */
  public void setTimeout(int timeout) {
    this.timeout = timeout;
  }

    public void setDataSource(DataSource dataSource) {
    this.dataSource = dataSource;
  }

 
  protected void executeInternal(JobExecutionContext ctx)
  throws JobExecutionException {

        System.out.println("in....................");
      // do the actual work
  }

  public void doIt() {
    // do the actual work

      try
        {
       Context ctx = new InitialContext();

      DataSource ds =
            (DataSource)ctx.lookup(
               "java:oraclePool");

        }

        }catch(Exception e) {
      e.printStackTrace();
    }

  }

}


<?xml version="1.0" encoding="UTF-8"?><!DOCTYPE beans PUBLIC "-//SPRING//DTD BEAN//EN" "http://www.springframework.org/dtd/spring-beans.dtd"><!--
  - Application context definition for "springapp" DispatcherServlet.
  -->

<beans>
<bean id="dataSource" class="org.springframework.jndi.JndiObjectFactoryBean">
            <property name="jndiName"><value>java:/oraclePool</value></property>
      </bean>
 
<!--  Often you just need to invoke a method on a specific object. Using the MethodInvokingJobDetailFactoryBean you can do exactly this
-->
<bean id="exampleJob" class="org.springframework.scheduling.quartz.MethodInvokingJobDetailFactoryBean">
<property name="targetObject" ref="exampleBusinessObject"/>
<property name="targetMethod" value="doIt"/>
</bean>

<bean id="exampleBusinessObject" class="bus.ExampleJob">
<property name="dataSource">
                  <ref bean="dataSource" />
</property>
</bean>


<bean class="org.springframework.scheduling.quartz.SchedulerFactoryBean" lazy-init="false">
<property name="triggers">
  <list>
      <ref bean="cronTrigger"/>
      <ref bean="simpleTrigger"/>
    </list>

  </property>

 
</bean>
   
<bean id="simpleTrigger" class="org.springframework.scheduling.quartz.SimpleTriggerBean">
  <!-- see the example of method invoking job above -->
  <property name="jobDetail" ref="exampleJob"/>
      <!-- 10 seconds -->
  <property name="startDelay" value="10000"/>
      <!-- repeat every 50 seconds -->
  <property name="repeatInterval" value="50000"/>
</bean>


<bean id="cronTrigger" class="org.springframework.scheduling.quartz.CronTriggerBean">
  <property name="jobDetail" ref="exampleJob"/>
      <!-- run every morning at 6 AM -->
  <property name="cronExpression" value="0 0 6 * * ?"/>
</bean>



</beans>
Mick BarryJava Developer
CERTIFIED EXPERT
Top Expert 2010

Commented:
thats what I just suggested :)
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.