[quartznet:4118] Purpose of selectWithLockSql?

classic Classic list List threaded Threaded
2 messages Options
Reply | Threaded
Open this post in threaded view
|

[quartznet:4118] Purpose of selectWithLockSql?

Matt Velic
I've read the limited documentation, and have done about 3 days worth of searching online, and I do not understand why Quartz needs to place a lock for a select statement on the LOCKS table during regular processing.

In our setup on MSSQL 2012, we need to process hundreds of jobs per minute, and we had been able to do so in the past. But in upgrading to a newer version of Quartz, it cannot keep up with the deluge of jobs that are being creating because this lock from the "SELECT * FROM {0}LOCKS WITH (UPDLOCK, ROWLOCK) WHERE SCHED_NAME = {1} AND LOCK_NAME = ?" is causing too much blocking.

As far as I can tell, there is no process from within Quartz that will ever update the LOCKS table, so what is the purpose of forcing this lock? Is it to block people who would attempt to manually change that table?

Can anyone explain the purpose of this code, and tell me why I shouldn't change the config file's selectWithLockSql to use "SELECT * FROM {0}LOCKS WHERE SCHED_NAME = {1} AND LOCK_NAME = ?" instead?

--
You received this message because you are subscribed to the Google Groups "Quartz.NET" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
To post to this group, send email to [hidden email].
Visit this group at https://groups.google.com/group/quartznet.
For more options, visit https://groups.google.com/d/optout.
Reply | Threaded
Open this post in threaded view
|

[quartznet:4119] Re: Purpose of selectWithLockSql?

jbvyncent
Sir, 

From my own analysis of the code, I suspect the inclusion of (UPDLOCK, ROWLOCK) was to address the potential for nonrepeatable and phantom reads (https://technet.microsoft.com/en-us/library/ms189122(v=sql.105).aspx) as a result of the QuartzNet does not properly configuring the transaction envelope in SQL Server. If you remove (UPDLOCK, ROWLOCK) you run the very real risk of (re)introducing concurrency side effects in your production environment.

If your development processes permit modifications to the QuartzNet source code for production environments, I recommend you consider restructuring all of the TSQL SQL Server code to configure the transaction envelope properly (e.g., SET TRANSACTION ISOLATION LEVELS = SNAPSHOT) instead of just removing (UPDLOCK, ROWLOCK).


On Wednesday, April 26, 2017 at 11:24:01 AM UTC-4, Matt Velic wrote:
I've read the limited documentation, and have done about 3 days worth of searching online, and I do not understand why Quartz needs to place a lock for a select statement on the LOCKS table during regular processing.

In our setup on MSSQL 2012, we need to process hundreds of jobs per minute, and we had been able to do so in the past. But in upgrading to a newer version of Quartz, it cannot keep up with the deluge of jobs that are being creating because this lock from the "SELECT * FROM {0}LOCKS WITH (UPDLOCK, ROWLOCK) WHERE SCHED_NAME = {1} AND LOCK_NAME = ?" is causing too much blocking.

As far as I can tell, there is no process from within Quartz that will ever update the LOCKS table, so what is the purpose of forcing this lock? Is it to block people who would attempt to manually change that table?

Can anyone explain the purpose of this code, and tell me why I shouldn't change the config file's selectWithLockSql to use "SELECT * FROM {0}LOCKS WHERE SCHED_NAME = {1} AND LOCK_NAME = ?" instead?

--
You received this message because you are subscribed to the Google Groups "Quartz.NET" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
To post to this group, send email to [hidden email].
Visit this group at https://groups.google.com/group/quartznet.
For more options, visit https://groups.google.com/d/optout.