Quartz.net clustered is throwing error

Collectives™ on Stack Overflow

Find centralized, trusted content and collaborate around the technologies you use most.
Learn more about Collectives
Teams
Q&A for work
Connect and share knowledge within a single location that is structured and easy to search.
Learn more about Teams
I am trying to run quartz.net in clustered environment (2 quartz services using single database as job datastore). I have set quartz.jobStore.clustered = true and other properties as below in quartz.exe.config
<quartz>
    <add key="quartz.jobStore.tablePrefix" value="Qrtz_"/>
    <add key="quartz.jobStore.misfireThreshold" value="60000"/>
    <add key="quartz.jobStore.type" value="Quartz.Impl.AdoJobStore.JobStoreTX, Quartz"/>
    <add key="quartz.jobStore.driverDelegateType" value="Quartz.Impl.AdoJobStore.StdAdoDelegate, Quartz"/>
    <add key="quartz.jobStore.dataSource" value="myDS"/>
    <add key="quartz.dataSource.myDS.connectionString" value="Server=DEV1;user=dev2;password=P&ssW0rd;database=DATAU"/>
    <add key="quartz.jobStore.useProperties" value="false"/>
    <add key="quartz.dataSource.myDS.provider" value="SqlServer-20"/>
    <add key="quartz.scheduler.exporter.type" value="Quartz.Simpl.RemotingSchedulerExporter, Quartz"/>
    <add key="quartz.scheduler.exporter.port" value="555"/>
    <add key="quartz.scheduler.exporter.bindName" value="QuartzScheduler"/>
    <add key="quartz.scheduler.exporter.channelType" value="tcp"/>
    <add key="quartz.scheduler.exporter.channelName" value="httpQuartz"/>
    <add key="quartz.jobStore.clustered" value="true" />
</quartz>
However, I am getting following error 
  ERROR Quartz.Impl.AdoJobStore.JobStoreTX - ClusterManager: Error managing cluster: Failure obtaining db row lock: Line 1: FOR UPDATE clause allowed only for DECLARE CURSOR.
I am using SQL2012 (with latest Service Pack applied) as job store.
Further, I tried replace the lock query with following property as below
<add key="quartz.jobStore.selectWithLockSQL" value="SELECT * FROM &#123;0&#125;LOCKS UPDLOCK WHERE LOCK_NAME = '?'" />
If I do that, I am getting following error
  [QuartzScheduler_QuartzScheduler-NON_CLUSTERED_MisfireHandler] ERROR Quartz.Impl.AdoJobStore.JobStoreTX - MisfireHandler: Error handling misfires: Failure obtaining db row lock: Violation of PRIMARY KEY constraint 'PK_QRTZ_LOCKS'. Cannot insert duplicate key in object 'dbo.QRTZ_LOCKS'. The duplicate key value is (QuartzScheduler, TRIGGER_ACCESS).
As requested in the comment, here is my full working config
<?xml version="1.0" encoding="utf-8"?>
<configuration>
<configSections>
    <section name="quartz" type="System.Configuration.NameValueSectionHandler, System, Version=1.0.5000.0,Culture=neutral, PublicKeyToken=b77a5c561934e089"/>
    <section name="log4net" type="log4net.Config.Log4NetConfigurationSectionHandler, log4net"/>
    <sectionGroup name="common">
        <section name="logging" type="Common.Logging.ConfigurationSectionHandler, Common.Logging"/>
    </sectionGroup>
</configSections>
<common>
    <logging>
        <factoryAdapter type="Common.Logging.Log4Net.Log4NetLoggerFactoryAdapter, Common.Logging.Log4net1211">
            <arg key="configType" value="INLINE"/>
        </factoryAdapter>
    </logging>
</common>
<connectionStrings>
    <add name="EARTHEntities" connectionString="metadata=res://*/DatabaseModel.EarthModel.csdl|res://*/DatabaseModel.DBhModel.ssdl|res://*/DatabaseModel.DBModel.msl;provider=System.Data.SqlClient;provider connection string=&quot;data source=server;initial catalog=db;user=remoteuser;password=pwd;MultipleActiveResultSets=True;App=EntityFramework&quot;" providerName="System.Data.EntityClient"/>
</connectionStrings>
<log4net>
    <appender name="ConsoleAppender" type="log4net.Appender.ConsoleAppender">
        <layout type="log4net.Layout.PatternLayout">
            <conversionPattern value="%date [%thread] %-6level %logger - %message %exception%newline"/>
        </layout>
    </appender>
    <appender name="EventLogAppender" type="log4net.Appender.EventLogAppender">
        <layout type="log4net.Layout.PatternLayout">
            <conversionPattern value="%date [%thread] %-6level %logger - %message %exception%newline"/>
        </layout>
    </appender>
    <appender name="GeneralLog" type="log4net.Appender.RollingFileAppender">
        <file value="Quartz.log"/>
        <appendToFile value="true"/>
        <rollingStyle value="Date"/>
        <datePattern value="yyyyMMdd"/>
        <maxSizeRollBackups value="1"/>
        <maximumFileSize value="5MB"/>
        <layout type="log4net.Layout.PatternLayout">
            <conversionPattern value="%date [%thread] %-5level %logger - %message%newline"/>
        </layout>
    </appender>
        <level value="INFO"/>
        <appender-ref ref="GeneralLog"/>
        <appender-ref ref="EventLogAppender"/>
        <appender-ref ref="ConsoleAppender"/>
    </root>
</log4net>
    We use quartz.config for this server, you can always use configuration section if you want to.
    Configuration section has precedence here.  
<quartz>
    <add key="quartz.jobStore.tablePrefix" value="Qrtz_"/>
    <add key="quartz.jobStore.misfireThreshold" value="60000"/>
    <add key="quartz.jobStore.type" value="Quartz.Impl.AdoJobStore.JobStoreTX, Quartz"/>
    <add key="quartz.jobStore.driverDelegateType" value="Quartz.Impl.AdoJobStore.StdAdoDelegate, Quartz"/>
    <add key="quartz.jobStore.dataSource" value="myDS"/>
    <add key="quartz.dataSource.myDS.connectionString" value="Server=server;user=remoteuser;password=pwd;database=db"/>
    <add key="quartz.jobStore.useProperties" value="false"/>
    <add key="quartz.dataSource.myDS.provider" value="SqlServer-20"/>
    <add key="quartz.scheduler.exporter.type" value="Quartz.Simpl.RemotingSchedulerExporter, Quartz"/>
    <add key="quartz.scheduler.exporter.port" value="555"/>
    <add key="quartz.scheduler.exporter.bindName" value="QuartzScheduler"/>
    <add key="quartz.scheduler.exporter.channelType" value="tcp"/>
    <add key="quartz.scheduler.exporter.channelName" value="httpQuartz"/>
    <add key="quartz.jobStore.clustered" value="true" />
    <add key="quartz.jobStore.lockHandler.type" value="Quartz.Impl.AdoJobStore.UpdateLockRowSemaphore, Quartz"/>
    <add key="quartz.scheduler.instanceId" value="AUTO" />
</quartz>
Try playing with this config setting:
properties["quartz.jobStore.driverDelegateType"] = "Quartz.Impl.AdoJobStore.MSSQLDelegate, Quartz";
Wait, that one may be old....
Maybe this one:
    <add key="quartz.jobStore.lockHandler.type"
value="Quartz.Impl.AdoJobStore.UpdateLockRowSemaphore, Quartz"/>
Found from this example:
https://groups.google.com/forum/#!topic/quartznet/Csw2un9KUy0
I'm going from memory....I don't have the working example I once had.....
Here is some code to help figure this one out:
So maybe using "Quartz.Impl.AdoJobStore.SqlServerDelegate" instead of "Quartz.Impl.AdoJobStore.StdAdoDelegate" is an option.
namespace Quartz.Impl.AdoJobStore
    /// <summary>
    /// A SQL Server specific driver delegate.
    /// </summary>
    /// <author>Marko Lahma</author>
    public class SqlServerDelegate : StdAdoDelegate
namespace Quartz.Impl.AdoJobStore
    /// <summary>
    /// Provide thread/resource locking in order to protect
    /// resources from being altered by multiple threads at the same time using
    /// a db row update.
    /// </summary>
    /// <remarks>
    /// <para>
    /// <b>Note:</b> This Semaphore implementation is useful for databases that do
    /// not support row locking via "SELECT FOR UPDATE" or SQL Server's type syntax.
    /// </para>
    /// <para>
    /// As of Quartz.NET 2.0 version there is no need to use this implementation for 
    /// SQL Server databases.
    /// </para>
    /// </remarks>
    /// <author>Marko Lahma (.NET)</author>
    public class UpdateLockRowSemaphore : DBSemaphore
                You should not add the quartz.jobStore.lockHandler.type when using SQLServerDelegate, it adds necessary and optimal configuration automatically.
– Marko Lahma
                Dec 4, 2014 at 5:59
        Thanks for contributing an answer to Stack Overflow!
Please be sure to answer the question. Provide details and share your research!
But avoid …
Asking for help, clarification, or responding to other answers.
Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.