[#QUARTZ-497] MS SQL Semaphore solution

Quartz Scheduler

MS SQL Semaphore solution

Created: 08/Aug/06 10:12 AM Updated: 24/Dec/07 01:30 AM

Component/s:

Job Stores

Affects Version/s:

1.5.2, 1.6

Fix Version/s:

1.6

Environment:

« Hide

Quartz 1.5.2
MS SQL 2000
JobStoreTX
clustering

Description

« Hide

http://forums.opensymphony.com/thread.jspa?forumID=6&threadID=39696

The above forum post outlines a way to get MS SQL to correctly lock a row by using an update rather than a select for update:

UPDATE {0}LOCKS SET LOCK_NAME = ? WHERE LOCK_NAME = ?

I wonder if this would work just as well:

UPDATE {0}LOCKS SET LOCK_NAME = LOCK_NAME WHERE LOCK_NAME = ?

We could easilty add this implementation now that 1.6 supports pluggable Semaphore implementations...

All

Comments

Change History

Sort Order:

[ Permlink | « Hide ]

Jasper Rosenberg added a comment - 17/Aug/06 09:23 AM

This concept is not MSSQL specific, so will create a generic semaphore that does an update called UpdateLockRowSemaphore.

As confirmed in the forum, the simpler expression with only one parameter does work, and so that will be the default implementation.

This new Semaphore has been tested against Oracle 10g, and can be installed in the properties like so:

org.quartz.jobStore.lockHandler.class = org.quartz.impl.jdbcjobstore.UpdateLockRowSemaphore

with optional parameters:

org.quartz.jobStore.lockHandler.updateLockRowSQL = UPDATE {0}LOCKS SET LOCK_NAME = LOCK_NAME WHERE LOCK_NAME = ?
org.quartz.jobStore.lockHandler.tablePrefix = QRTZ_

[ Permlink | « Hide ]

Jasper Rosenberg added a comment - 17/Aug/06 09:26 AM

At some point we need to provide a mechanism for Quartz to automatically pass the global tablePrefix to a Semaphore that requires it, rather than having it be redundantly defined. Probably easiest to have an interface that includes setTablePrefix(), and if the Semaphore implements that interface, the global tablePrefix is added to the properties for configuring the Semaphore (in the StdSchedulerFactory).

[ Permlink | « Hide ]

Jasper Rosenberg added a comment - 17/Aug/06 09:43 AM

I added the TablePrefixAware interface which StdSchedulerFactory uses to determine if it needs to coppy the JobStoreSupport's tablePrefix over to the Semaphore.

So this property is actually dead:
org.quartz.jobStore.lockHandler.tablePrefix = QRTZ_