Violation of PRIMARY KEY constraint 'PK_QRTZ_SCHEDULER_STATE'

[RekoLoi]

Summary:

We have been using quartz in our application for a while now and seemingly randomly after an update and a restart some but not all clients started throwing errors like this:

ClusterManager: Error managing cluster: Failure updating scheduler state when checking-in: Violation of PRIMARY KEY constraint 'PK_QRTZ_SCHEDULER_STATE'. Cannot insert duplicate key in object 'dbo.QRTZ_SCHEDULER_STATE'. The duplicate key value is (KMWEBScheduler, 177c83f328ae1778879779063) at org.quartz.impl.jdbcjobstore.JobStoreSupport.clusterCheckIn(JobStoreSupport.java:3467) ~[quartz-jobs-2.4.1.jar:2.4.1]

These errors do not appear as soon as an instance is restarted. This happens in enviroments with one, two and four quartz schedulers. When multiple quartz shedulers are in use they eventually all start throwing the same error

Environment
Quartz version: 2.4.1
Application server: Tomcat with varying amounts of VMs
Database: MSSQL
Use case: Clustered Quartz using JobStoreTX
Java version: Java 8

Observations

1. Does not happen to all instances. Only some with no clear pattern
2. If we fully restart a client these errors either disappear, come back after some time or appear even though previously it was fine
3. Querying QRTZ_SCHEDULER_STATE table shows that some of the instances disappear from the table. Then they are recreated with a new INSTANCE_NAME but eventually start throwing the same errors.
4. When the errors are happening querying QRTZ_SCHEDULER_STATE clearly shows that a row with a matching scheduler name and instance name does exist. No extra schedulers exist in the table

quartz.properties

#============================================================================
# Configure Main Scheduler Properties
#============================================================================

# Update check should always be disabled in production
org.quartz.scheduler.skipUpdateCheck = true
# Name can be anything.
org.quartz.scheduler.instanceName = KMWEBScheduler
# Every instance has to have an id. AUTO makes it automatic. The generator class is used for creating the ids.
org.quartz.scheduler.instanceId = AUTO
# This is the default generator. It can be changed to completely custom if needed.
org.quartz.scheduler.instanceIdGenerator.class = org.quartz.simpl.SimpleInstanceIdGenerator

# Threads spawned by Quartz will inherit the context ClassLoader of the initializing thread.
# Setting this value to ‘true’ may help with class loading, JNDI look-ups, and other issues
# related to using Quartz within an application server.
org.quartz.scheduler.threadsInheritContextClassLoaderOfInitializer = true

# The default factory. Will instantiate a new job class for every execution.
org.quartz.scheduler.jobFactory.class = org.quartz.simpl.PropertySettingJobFactory

# Run main thread of the scheduler as a daemon thread. This means the active threads won't prevent Tomcat shutdown.
org.quartz.scheduler.makeSchedulerThreadDaemon = true

org.quartz.scheduler.interruptJobsOnShutdown = true

#============================================================================
# Configure JobStore
#============================================================================
# Clustering is needed for saving jobs etc. to database
org.quartz.jobStore.isClustered = true
# The the number of milliseconds the scheduler will ‘tolerate’ a trigger to pass its next-fire-time by,
# before being considered “misfired”. The default is 60 seconds (=60000).
org.quartz.jobStore.misfireThreshold = 60000
# 20 is the default.
org.quartz.jobStore.maxMisfiresToHandleAtATime = 20
# Default is false but setting this to true can help in preventing lock timeouts
# with some databases under high load, and “long-lasting” transactions.
org.quartz.jobStore.txIsolationLevelSerializable = false
# JAMIXDB Quartz schema creation needs to be changed if this is changed.
org.quartz.jobStore.tablePrefix = QRTZ_
# JobStoreTX should be used as the app is not using JTA transactions.
org.quartz.jobStore.class = org.quartz.impl.jdbcjobstore.JobStoreTX
# Use Specific delegate for MS SQLServer
org.quartz.jobStore.driverDelegateClass = org.quartz.impl.jdbcjobstore.MSSQLDelegate
# This is the same name as in Tomcat context just for clarity
org.quartz.jobStore.dataSource = jamixDatasource

#============================================================================
# SimpleThreadPool-Specific Properties
#============================================================================
# Run job threads as a daemon thread. See org.quartz.scheduler.makeSchedulerThreadDaemon
org.quartz.threadPool.makeThreadsDaemons = true

#============================================================================
# Configure Datasources
#============================================================================
# Use the Tomcat context defined datasource with Quartz too
org.quartz.dataSource.jamixDatasource.jndiURL: java:comp/env/jamixDatasource
org.quartz.dataSource.jamixDatasource.driver = com.microsoft.sqlserver.mssql-jdbc
# Recommended threadPool size + 3
org.quartz.dataSource.jamixDatasource.maxConnections = 13

#============================================================================
# Configure ThreadPool
#============================================================================
org.quartz.threadPool.class = org.quartz.simpl.SimpleThreadPool
# Most of the jobs only fire once a day so 1-2 would probably be enough but recipe cache update runs
# multiple times in a minute so better make it safe (also 10 is the default value).
org.quartz.threadPool.threadCount = 10
# Value between 1-10. 5 is the default.
org.quartz.threadPool.threadPriority = 5