I am using <cftransaction isolation="repeatable_read"> to single thread
access to scheduled tasks that live in a DB table. This application spawns
multiple servers, and each server is set to poll the scheduled task table
every 3 minutes. It grabs the tasks and executes them.
Now, if this was on one server, I could simply use a named exclusive lock to
single thread access to the tasks so that multiple servers don't pick up and
execute the same task (they should only be run once).
What I have ended up doing is something like the code below. Basically,
within the repeatable_read transaction I also update each row that was
picked up for processing. This has the effect of causing another server who
reads the data at the same time (before the enabled flag is set to false)
to fail. I've tested this by adding a sleep() command after the first read
and running this from two instances. One request always fails, so that is
working as I want it to.
I guess my question is, will this lead to performance problems? Is there a
more elegant way to do this?
<cftransaction isolation="repeatable_read">
<cfquery name="getEvents" datasource="#application.gs.datasource#">
Select ID,cfc,runMethod,arguments,frequency,frequencytype
from scheduledTasks
where enabled=1
and nextDate < <cfqueryparam cfsqltype="cf_sql_timestamp"
value="#createodbcdatetime(Now())#">
and startDate < <cfqueryparam cfsqltype="cf_sql_timestamp"
value="#createodbcdatetime(Now())#">
and endDate > <cfqueryparam cfsqltype="cf_sql_timestamp"
value="#createodbcdatetime(Now())#">
</cfquery>
<cfoutput query="getEvents">
<cftry>
<cfquery datasource="#application.gs.datasource#">
update scheduledtasks
set enabled=0
where id=<cfqueryparam cfsqltype="cf_sql_integer" value="#id#">
</cfquery>
<cfcatch>
<cfinvoke
component="#application.core.Log#"
method="call"
logFile="scheduledEventLauncher"
log_error="Unable to update table, another server has a lock?
#cfcatch.message# #cfcatch.detail#"
email="true">
<cfreturn false>
</cfcatch>
</cftry>
</cfoutput>
</cftransaction>
Thanks for the input! Hopefully the above code does not get mangled..
Brook
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Order the Adobe Coldfusion Anthology now!
http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion
Archive:
http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:349090
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
