Quartz org.quartz.jobStore.selectWithLockSQL row lock

Question

I am using Quartz in clustered mode

I have some row lock contention on DB level caused by excessive call to :

org.quartz.jobStore.selectWithLockSQL

"SELECT * FROM QRTZ_LOCKS WHERE SCHED_NAME = :"SYS_B_0" AND LOCK_NAME = :1 FOR UPDATE"

I read quartz docs and is still not very clear to me why is above query is executed.

What is the purpose of having this row lock ?

Regards

Have a look at https://jira.terracotta.org/jira/browse/QTZ-35. JIRA is closed with "won't Fix" but has useful informaton — Dhananjay, Jul 10 '15 at 13:29

score 6 · Answer 1 · answered Nov 06 '14 at 13:30

6

The locks table is used by quartz for coordinating multiple schedulers when deployed in cluster mode. In a cluster only one node should fire the trigger, so a lock is used to avoid multiple nodes acquiring the same trigger.

From the clustering section of the documentation (http://quartz-scheduler.org/generated/2.2.1/html/qs-all/#page/Quartz_Scheduler_Documentation_Set%2Fre-cls_cluster_configuration.html%23):

Clustering currently only works with the JDBC-Jobstore (JobStoreTX or JobStoreCMT), and essentially works by having each node of the cluster share the same database. Load-balancing occurs automatically, with each node of the cluster firing jobs as quickly as it can. When a trigger's firing time occurs, the first node to acquire it (by placing a lock on it) is the node that will fire it.

answered Nov 06 '14 at 13:30

Marios

1,947
1
15
24

5

I'm having a similar issue. I understand the logic behind the query, but I also cannot get why it is executed so many times. In my case, for a job that is scheduled to run every 10 minutes, the query is being executed more than 11000 times per hour! Did you arrive to any conclusion? – A Costa Jul 08 '15 at 08:40
@ACosta. I'm having a similar issue. This sql is executed so many times, which is definitely out of proportion with number of tasks. Did you find out any solution? – Murphy Ng Jun 23 '20 at 10:47
I am facing the same issue. even I am observing that we have 700 jobs and each have a period time of 1 minute. but it's showing that jobs are being delayed in minutes. this table is accessibly using. – Atif Hussain Nov 30 '20 at 07:26

score 1 · Answer 2 · edited Jan 14 '21 at 05:56

In my case, I was experiencing a similar issue. I was using quartz fir running jobs whose logic involved fetching data from a foreign db. Whenever the connection between the application db and foreign db stopped due to some reason and the connection came back up the issue of locks surfaced and we used to get messages like this in the database logs

2021-01-14 12:06:17.935 KST [46836] STATEMENT:  
SELECT * FROM HVACQRTZ_LOCKS WHERE SCHED_NAME = 'schedulerFactoryBean' AND LOCK_NAME = $1 FOR UPDATE
2021-01-14 12:06:18.937 KST [46836] ERROR:  current transaction is aborted, commands ignored until end of transaction block

To solve this issue I used this property of quartz and once after using this property the issue went away. By default, the foe update part will be there at the end of the query but since the default query is replaced by the query which I wrote in the property file the for update portion is gone and no locks appear now and everything seems to be working smoothly.

selectWithLockSQL: SELECT * FROM {0}LOCKS  WHERE LOCK_NAME = ?

Quartz org.quartz.jobStore.selectWithLockSQL row lock

2 Answers2