Bug 1010960

Summary: Quartz does not work with mssql2008R2 correctly - throwing org.quartz.impl.jdbcjobstore.LockException
Product: [Retired] JBoss BPMS Platform 6 Reporter: Radovan Synek <rsynek>
Component: Business CentralAssignee: Maciej Swiderski <mswiders>
Status: CLOSED NOTABUG QA Contact: Radovan Synek <rsynek>
Severity: high Docs Contact:
Priority: unspecified    
Version: 6.0.0CC: kverlaen
Target Milestone: ---   
Target Release: ---   
Hardware: Unspecified   
OS: Unspecified   
Whiteboard:
Fixed In Version: Doc Type: Bug Fix
Doc Text:
Story Points: ---
Clone Of: Environment:
Last Closed: 2013-09-27 07:02:12 UTC Type: Bug
Regression: --- Mount Type: ---
Documentation: --- CRM:
Verified Versions: Category: ---
oVirt Team: --- RHEL 7.3 requirements from Atomic Host:
Cloudforms Team: --- Target Upstream Version:
Embargoed:
Attachments:
Description Flags
process definition none

Description Radovan Synek 2013-09-23 11:51:56 UTC 
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):
BPMS-6.0.0.ER3

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 16:43:25 UTC 
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 07:02:12 UTC 
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.