asked on

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

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">

<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>

<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 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">

<property name="jobDetail" ref="exampleJob"/>
      
<property name="startDelay" value="10000"/>
      
<property name="repeatInterval" value="50000"/>
</bean>

<bean id="cronTrigger" class="org.springframework.scheduling.quartz.CronTriggerBean">
<property name="jobDetail" ref="exampleJob"/>
      
<property name="cronExpression" value="0 0 6 * * ?"/>
</bean>

</beans>

CEHJ

>>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

chaitu chaitu

ASKER

what's this query actually do

CEHJ

No idea - i'd guess it's to do with synching jobs.

Mick Barry

you don't have a table named QRTZ_LOCKS in your database

Mick Barry

do you have perms for that data store to create tables

chaitu chaitu

ASKER

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>

chaitu chaitu

ASKER

after creating above table do i need this query( SELECT * FROM {0}LOCKS UPDLOCK WHERE LOCK_NAME = ?)

Mick Barry

> 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

chaitu chaitu

ASKER

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]

CEHJ

>>MethodInvokingJobDetailFactoryBean

Please post the above

chaitu chaitu

ASKER

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)

CEHJ

(See my last comment)

chaitu chaitu

ASKER

i didnt get u

CEHJ

Please post the code

Mick Barry

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.

chaitu chaitu

ASKER

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;

ASKER CERTIFIED SOLUTION

Mick Barry

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

chaitu chaitu

ASKER

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">

<beans>
<bean id="dataSource" class="org.springframework.jndi.JndiObjectFactoryBean">
            <property name="jndiName"><value>java:/oraclePool</value></property>
      </bean>


<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">

<property name="jobDetail" ref="exampleJob"/>
      
<property name="startDelay" value="10000"/>
      
<property name="repeatInterval" value="50000"/>
</bean>

<bean id="cronTrigger" class="org.springframework.scheduling.quartz.CronTriggerBean">
<property name="jobDetail" ref="exampleJob"/>
      
<property name="cronExpression" value="0 0 6 * * ?"/>
</bean>

</beans>

Mick Barry

thats what I just suggested :)