Bug 1010960 - Quartz does not work with mssql2008R2 correctly - throwing org.quartz.impl.jdbcjobstore.LockException
Quartz does not work with mssql2008R2 correctly - throwing org.quartz.impl.jd...
Product: JBoss BPMS Platform 6
Classification: JBoss
Component: Business Central (Show other bugs)
Unspecified Unspecified
unspecified Severity high
: ---
: ---
Assigned To: Maciej Swiderski
Radovan Synek
Depends On:
  Show dependency treegraph
Reported: 2013-09-23 07:51 EDT by Radovan Synek
Modified: 2013-09-27 03:02 EDT (History)
1 user (show)

See Also:
Fixed In Version:
Doc Type: Bug Fix
Doc Text:
Story Points: ---
Clone Of:
Last Closed: 2013-09-27 03:02:12 EDT
Type: Bug
Regression: ---
Mount Type: ---
Documentation: ---
Verified Versions:
Category: ---
oVirt Team: ---
RHEL 7.3 requirements from Atomic Host:
Cloudforms Team: ---

Attachments (Terms of Use)
process definition (6.43 KB, application/xml)
2013-09-23 07:51 EDT, Radovan Synek
no flags Details

  None (edit)
Description Radovan Synek 2013-09-23 07:51:56 EDT
Created attachment 801584 [details]
process definition

Description of problem:
Running Business-Central in 2-node cluster on EAP6 domain, with MS SQL2008R2 database, following exception occurs several times in server log:

14:42:11,147 ERROR [org.quartz.impl.jdbcjobstore.JobStoreCMT] (QuartzScheduler_jBPMClusteredScheduler-rsynek1379680894322_ClusterManager) ClusterManager: Error managing cluster: Failure obtaining db row lock: Line 1: FOR UPDATE clause allowed only for DECLARE CURSOR.: org.quartz.impl.jdbcjobstore.LockException: Failure obtaining db row lock: Line 1: FOR UPDATE clause allowed only for DECLARE CURSOR. [See nested exception: com.microsoft.sqlserver.jdbc.SQLServerException: Line 1: FOR UPDATE clause allowed only for DECLARE CURSOR.]
	at org.quartz.impl.jdbcjobstore.StdRowLockSemaphore.executeSQL(StdRowLockSemaphore.java:109) [quartz-1.8.5.jar:]
	at org.quartz.impl.jdbcjobstore.DBSemaphore.obtainLock(DBSemaphore.java:112) [quartz-1.8.5.jar:]
	at org.quartz.impl.jdbcjobstore.JobStoreSupport.doCheckin(JobStoreSupport.java:3201) [quartz-1.8.5.jar:]
	at org.quartz.impl.jdbcjobstore.JobStoreSupport$ClusterManager.manage(JobStoreSupport.java:3825) [quartz-1.8.5.jar:]
	at org.quartz.impl.jdbcjobstore.JobStoreSupport$ClusterManager.run(JobStoreSupport.java:3861) [quartz-1.8.5.jar:]
Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: Line 1: FOR UPDATE clause allowed only for DECLARE CURSOR.
	at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:216)
	at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1515)
	at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePreparedStatement(SQLServerPreparedStatement.java:404)
	at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement$PrepStmtExecCmd.doExecute(SQLServerPreparedStatement.java:350)
	at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:5696)
	at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:1715)
	at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:180)
	at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:155)
	at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.executeQuery(SQLServerPreparedStatement.java:285)
	at org.jboss.jca.adapters.jdbc.WrappedPreparedStatement.executeQuery(WrappedPreparedStatement.java:462)
	at org.quartz.impl.jdbcjobstore.StdRowLockSemaphore.executeSQL(StdRowLockSemaphore.java:89) [quartz-1.8.5.jar:]
	... 4 more

Steps to reproduce:
1. setup a jbpm cluster with mssql2008R2
2. deploy attached process definition (simple scenario with timer node)
3. start the process

Version-Release number of selected component (if applicable):

Additional info:
This bug could be solved by specifying different select in property org.quartz.jobStore.selectWithLockSQL=SELECT * FROM {0}LOCKS UPDLOCK WHERE LOCK_NAME = ?
Comment 1 Maciej Swiderski 2013-09-24 12:43:25 EDT
Radek, according to this issue https://jira.terracotta.org/jira/browse/QTZ-151 the problem should be fixed in 1.8.5 version of quartz and in fact that is the version we are currently using.

Have you configured org.quartz.jobStore.driverDelegateClass=org.quartz.impl.jdbcjobstore.MSSQLDelegate

if that won't work then I think we would need to stick to documenting that in case MSSQL is used then this property must be added there.
Comment 2 Radovan Synek 2013-09-27 03:02:12 EDT
Thanks Maciej, I have been using org.quartz.impl.jdbcjobstore.StdJDBCDelegate class, which is not perfect for all DBs. The specific delegate class works, so no need for using select statement directly. I have spoken with Eva Kopalova about documenting this configuration as well.

Note You need to log in before you can comment on or make changes to this bug.