Quartz db lock exception

[alesj]

When configuring Quartz scheduler bean I got this exception.

I use MSSQL 2000 and its default jdbc driver.

Where is this READ ONLY cursor set?

Thanx,
Ales

btw: what is the best jdbc driver to use with MSSQL2000 when using Spring with Hibernate


//----- configuration -----
<bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close">
<property name="driverClassName"><value>${jdbc.driverClassNa me}</value></property>
<property name="url"><value>${jdbc.url}</value></property>
<property name="username"><value>${jdbc.username}</value></property>
<property name="password"><value>${jdbc.password}</value></property>
</bean>

<bean id="scheduler" class="org.springframework.scheduling.quartz.Sched ulerFactoryBean">
<property name="dataSource"><ref bean="dataSource"/></property>
<!--<property name="transactionManager"><ref bean="hibernateTransactionManager"/></property>-->
<property name="applicationContextSchedulerContextKey"><valu e>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.driverDelegateClass">org. quartz.impl.jdbcjobstore.MSSQLDelegate</prop>
</props>
</property>
</bean>

//------- exception ------
Ecos ERROR [20-08-2004 12:12:29] ContextLoader.java - Context initialization failed
org.springframework.beans.factory.BeanCreationExce ption: Error creating bean with name 'scheduler' defined in resource [/WEB-INF/connectors.xml] of ServletContext: Initialization of bean failed; nested exception is org.quartz.SchedulerConfigException: Failure occured during job recovery.
org.quartz.SchedulerConfigException: Failure occured during job recovery. [See nested exception: org.quartz.impl.jdbcjobstore.LockException: Failure obtaining db row lock: [Microsoft][SQLServer 2000 Driver for JDBC][SQLServer]FOR UPDATE cannot be specified on a READ ONLY cursor. [See nested exception: java.sql.SQLException: [Microsoft][SQLServer 2000 Driver for JDBC][SQLServer]FOR UPDATE cannot be specified on a READ ONLY cursor.]]
at org.quartz.impl.jdbcjobstore.JobStoreSupport.initi alize(JobStoreSupport.java:447)
at org.quartz.impl.jdbcjobstore.JobStoreCMT.initializ e(JobStoreCMT.java:149)
at org.springframework.scheduling.quartz.LocalDataSou rceJobStore.initialize(LocalDataSourceJobStore.jav a:72)
at org.quartz.impl.StdSchedulerFactory.instantiate(St dSchedulerFactory.java:756)
at org.quartz.impl.StdSchedulerFactory.getScheduler(S tdSchedulerFactory.java:888)
at org.springframework.scheduling.quartz.SchedulerFac toryBean.createScheduler(SchedulerFactoryBean.java :431)
at org.springframework.scheduling.quartz.SchedulerFac toryBean.afterPropertiesSet(SchedulerFactoryBean.j ava:384)
at org.springframework.beans.factory.support.Abstract AutowireCapableBeanFactory.invokeInitMethods(Abstr actAutowireCapableBeanFactory.java:948)
at org.springframework.beans.factory.support.Abstract AutowireCapableBeanFactory.createBean(AbstractAuto wireCapableBeanFactory.java:284)
at org.springframework.beans.factory.support.Abstract AutowireCapableBeanFactory.createBean(AbstractAuto wireCapableBeanFactory.java:204)
at org.springframework.beans.factory.support.Abstract BeanFactory.getBean(AbstractBeanFactory.java:199)
at org.springframework.beans.factory.support.Abstract BeanFactory.getBean(AbstractBeanFactory.java:136)
at org.springframework.beans.factory.support.DefaultL istableBeanFactory.preInstantiateSingletons(Defaul tListableBeanFactory.java:203)
at org.springframework.context.support.AbstractApplic ationContext.refresh(AbstractApplicationContext.ja va:279)
at org.springframework.web.context.support.XmlWebAppl icationContext.refresh(XmlWebApplicationContext.ja va:131)
at org.springframework.web.context.ContextLoader.crea teWebApplicationContext(ContextLoader.java:156)
at org.springframework.web.context.ContextLoader.init WebApplicationContext(ContextLoader.java:97)
at org.springframework.web.context.ContextLoaderListe ner.contextInitialized(ContextLoaderListener.java: 48)
at org.apache.catalina.core.StandardContext.listenerS tart(StandardContext.java:3188)
at org.apache.catalina.core.StandardContext.start(Sta ndardContext.java:3404)
at org.apache.catalina.core.ContainerBase.start(Conta inerBase.java:1123)
at org.apache.catalina.core.StandardHost.start(Standa rdHost.java:638)
at org.apache.catalina.core.ContainerBase.start(Conta inerBase.java:1123)
at org.apache.catalina.core.StandardEngine.start(Stan dardEngine.java:343)
at org.apache.catalina.core.StandardService.start(Sta ndardService.java:388)
at org.apache.catalina.core.StandardServer.start(Stan dardServer.java:506)
at org.apache.catalina.startup.Catalina.start(Catalin a.java:781)
at org.apache.catalina.startup.Catalina.execute(Catal ina.java:681)
at org.apache.catalina.startup.Catalina.process(Catal ina.java:179)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Nativ e Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(Native MethodAccessorImpl.java:39)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(De legatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:324)
at org.apache.catalina.startup.Bootstrap.main(Bootstr ap.java:243)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Nativ e Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(Native MethodAccessorImpl.java:39)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(De legatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:324)
at com.intellij.rt.execution.application.AppMain.main (Unknown Source)
* Nested Exception (Underlying Cause) ---------------
org.quartz.impl.jdbcjobstore.LockException: Failure obtaining db row lock: [Microsoft][SQLServer 2000 Driver for JDBC][SQLServer]FOR UPDATE cannot be specified on a READ ONLY cursor. [See nested exception: java.sql.SQLException: [Microsoft][SQLServer 2000 Driver for JDBC][SQLServer]FOR UPDATE cannot be specified on a READ ONLY cursor.]
at org.quartz.impl.jdbcjobstore.StdRowLockSemaphore.o btainLock(StdRowLockSemaphore.java:154)
at org.quartz.impl.jdbcjobstore.JobStoreCMT.cleanVola tileTriggerAndJobs(JobStoreCMT.java:205)
at org.quartz.impl.jdbcjobstore.JobStoreSupport.initi alize(JobStoreSupport.java:444)
at org.quartz.impl.jdbcjobstore.JobStoreCMT.initializ e(JobStoreCMT.java:149)
at org.springframework.scheduling.quartz.LocalDataSou rceJobStore.initialize(LocalDataSourceJobStore.jav a:72)
at org.quartz.impl.StdSchedulerFactory.instantiate(St dSchedulerFactory.java:756)
at org.quartz.impl.StdSchedulerFactory.getScheduler(S tdSchedulerFactory.java:888)
at org.springframework.scheduling.quartz.SchedulerFac toryBean.createScheduler(SchedulerFactoryBean.java :431)
at org.springframework.scheduling.quartz.SchedulerFac toryBean.afterPropertiesSet(SchedulerFactoryBean.j ava:384)
at org.springframework.beans.factory.support.Abstract AutowireCapableBeanFactory.invokeInitMethods(Abstr actAutowireCapableBeanFactory.java:948)
at org.springframework.beans.factory.support.Abstract AutowireCapableBeanFactory.createBean(AbstractAuto wireCapableBeanFactory.java:284)
at org.springframework.beans.factory.support.Abstract AutowireCapableBeanFactory.createBean(AbstractAuto wireCapableBeanFactory.java:204)
at org.springframework.beans.factory.support.Abstract BeanFactory.getBean(AbstractBeanFactory.java:199)
at org.springframework.beans.factory.support.Abstract BeanFactory.getBean(AbstractBeanFactory.java:136)
at org.springframework.beans.factory.support.DefaultL istableBeanFactory.preInstantiateSingletons(Defaul tListableBeanFactory.java:203)
at org.springframework.context.support.AbstractApplic ationContext.refresh(AbstractApplicationContext.ja va:279)
at org.springframework.web.context.support.XmlWebAppl icationContext.refresh(XmlWebApplicationContext.ja va:131)
at org.springframework.web.context.ContextLoader.crea teWebApplicationContext(ContextLoader.java:156)
at org.springframework.web.context.ContextLoader.init WebApplicationContext(ContextLoader.java:97)
at org.springframework.web.context.ContextLoaderListe ner.contextInitialized(ContextLoaderListener.java: 48)
at org.apache.catalina.core.StandardContext.listenerS tart(StandardContext.java:3188)
at org.apache.catalina.core.StandardContext.start(Sta ndardContext.java:3404)
at org.apache.catalina.core.ContainerBase.start(Conta inerBase.java:1123)
at org.apache.catalina.core.StandardHost.start(Standa rdHost.java:638)
at org.apache.catalina.core.ContainerBase.start(Conta inerBase.java:1123)
at org.apache.catalina.core.StandardEngine.start(Stan dardEngine.java:343)
at org.apache.catalina.core.StandardService.start(Sta ndardService.java:388)
at org.apache.catalina.core.StandardServer.start(Stan dardServer.java:506)
at org.apache.catalina.startup.Catalina.start(Catalin a.java:781)
at org.apache.catalina.startup.Catalina.execute(Catal ina.java:681)
at org.apache.catalina.startup.Catalina.process(Catal ina.java:179)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Nativ e Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(Native MethodAccessorImpl.java:39)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(De legatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:324)
at org.apache.catalina.startup.Bootstrap.main(Bootstr ap.java:243)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Nativ e Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(Native MethodAccessorImpl.java:39)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(De legatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:324)
at com.intellij.rt.execution.application.AppMain.main (Unknown Source)
* Nested Exception (Underlying Cause) ---------------
java.sql.SQLException: [Microsoft][SQLServer 2000 Driver for JDBC][SQLServer]FOR UPDATE cannot be specified on a READ ONLY cursor.
at com.microsoft.jdbc.base.BaseExceptions.createExcep tion(Unknown Source)
at com.microsoft.jdbc.base.BaseExceptions.getExceptio n(Unknown Source)
at com.microsoft.jdbc.sqlserver.tds.TDSRequest.proces sErrorToken(Unknown Source)
at com.microsoft.jdbc.sqlserver.tds.TDSRequest.proces sReplyToken(Unknown Source)
at com.microsoft.jdbc.sqlserver.tds.TDSRPCRequest.pro cessReplyToken(Unknown Source)
at com.microsoft.jdbc.sqlserver.tds.TDSRequest.proces sReply(Unknown Source)
at com.microsoft.jdbc.sqlserver.tds.TDSCursorRequest. openCursor(Unknown Source)
at com.microsoft.jdbc.sqlserver.SQLServerImplStatemen t.execute(Unknown Source)
at com.microsoft.jdbc.base.BaseStatement.commonExecut e(Unknown Source)
at com.microsoft.jdbc.base.BaseStatement.executeQuery Internal(Unknown Source)
at com.microsoft.jdbc.base.BasePreparedStatement.exec uteQuery(Unknown Source)
at org.apache.commons.dbcp.DelegatingPreparedStatemen t.executeQuery(DelegatingPreparedStatement.java:92 )
at org.quartz.impl.jdbcjobstore.StdRowLockSemaphore.o btainLock(StdRowLockSemaphore.java:137)
at org.quartz.impl.jdbcjobstore.JobStoreCMT.cleanVola tileTriggerAndJobs(JobStoreCMT.java:205)
at org.quartz.impl.jdbcjobstore.JobStoreSupport.initi alize(JobStoreSupport.java:444)
at org.quartz.impl.jdbcjobstore.JobStoreCMT.initializ e(JobStoreCMT.java:149)
at org.springframework.scheduling.quartz.LocalDataSou rceJobStore.initialize(LocalDataSourceJobStore.jav a:72)
at org.quartz.impl.StdSchedulerFactory.instantiate(St dSchedulerFactory.java:756)
at org.quartz.impl.StdSchedulerFactory.getScheduler(S tdSchedulerFactory.java:888)
at org.springframework.scheduling.quartz.SchedulerFac toryBean.createScheduler(SchedulerFactoryBean.java :431)
at org.springframework.scheduling.quartz.SchedulerFac toryBean.afterPropertiesSet(SchedulerFactoryBean.j ava:384)
at org.springframework.beans.factory.support.Abstract AutowireCapableBeanFactory.invokeInitMethods(Abstr actAutowireCapableBeanFactory.java:948)
at org.springframework.beans.factory.support.Abstract AutowireCapableBeanFactory.createBean(AbstractAuto wireCapableBeanFactory.java:284)
at org.springframework.beans.factory.support.Abstract AutowireCapableBeanFactory.createBean(AbstractAuto wireCapableBeanFactory.java:204)
at org.springframework.beans.factory.support.Abstract BeanFactory.getBean(AbstractBeanFactory.java:199)
at org.springframework.beans.factory.support.Abstract BeanFactory.getBean(AbstractBeanFactory.java:136)
at org.springframework.beans.factory.support.DefaultL istableBeanFactory.preInstantiateSingletons(Defaul tListableBeanFactory.java:203)
at org.springframework.context.support.AbstractApplic ationContext.refresh(AbstractApplicationContext.ja va:279)
at org.springframework.web.context.support.XmlWebAppl icationContext.refresh(XmlWebApplicationContext.ja va:131)
at org.springframework.web.context.ContextLoader.crea teWebApplicationContext(ContextLoader.java:156)
at org.springframework.web.context.ContextLoader.init WebApplicationContext(ContextLoader.java:97)
at org.springframework.web.context.ContextLoaderListe ner.contextInitialized(ContextLoaderListener.java: 48)
at org.apache.catalina.core.StandardContext.listenerS tart(StandardContext.java:3188)
at org.apache.catalina.core.StandardContext.start(Sta ndardContext.java:3404)
at org.apache.catalina.core.ContainerBase.start(Conta inerBase.java:1123)
at org.apache.catalina.core.StandardHost.start(Standa rdHost.java:638)
at org.apache.catalina.core.ContainerBase.start(Conta inerBase.java:1123)
at org.apache.catalina.core.StandardEngine.start(Stan dardEngine.java:343)
at org.apache.catalina.core.StandardService.start(Sta ndardService.java:388)
at org.apache.catalina.core.StandardServer.start(Stan dardServer.java:506)
at org.apache.catalina.startup.Catalina.start(Catalin a.java:781)
at org.apache.catalina.startup.Catalina.execute(Catal ina.java:681)
at org.apache.catalina.startup.Catalina.process(Catal ina.java:179)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Nativ e Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(Native MethodAccessorImpl.java:39)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(De legatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:324)
at org.apache.catalina.startup.Bootstrap.main(Bootstr ap.java:243)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Nativ e Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(Native MethodAccessorImpl.java:39)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(De legatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:324)
at com.intellij.rt.execution.application.AppMain.main (Unknown Source)

[alesj]

An answer to myself. 8)

Quartz properties must include the following property:
org.quartz.jobStore.selectWithLockSQL = SELECT * FROM {0}LOCKS UPDLOCK WHERE LOCK_NAME = ?

:wink: ,
Ales

[pburleson]

As to your question about best JDBC driver, I've had very good success with the Merlia Driver from inet-software.

http://www.inetsoftware.de/

As another note, the MS JDBC Driver appears to be the Data Direct Driver just crippled to only work with SQL Server 2000.

Patrick

[alesj]

Thanx for the info.

What about some open source driver?
Not that I think that 400$ in a lot, but since all my software is developed by open source, I hope some good driver also exists. :?:

Bye,
Ales

[irbouho]

did you tried jTDS http://jtds.sourceforge.net/.

[alesj]

did you tried jTDS http://jtds.sourceforge.net/.

This one I know. But I remembered this line in old default hibernate.properties:
## jTDS (not supported!)

But I checked now in the new Hibernate 2.1.6 default properties and this line is gone .

When did this happen?

Ales

[irbouho]

jTDS is also becoming a common choice for enterprise-level applications: starting with release 0.8-rc1 jTDS passes the Hibernate test suite, making it the driver of choice for SQL Server.