On Thu, Jun 25, 2009 at 9:52 PM, Will Tomlinsonwrote:
> The whole flow of the Stored Procedure needs to be something like this...
>
> 1) Grab the next message record where the status is 'pending'
> 2) Update that record's status to 'running'
> 3) Return the record
>
> While that is running, no other process should be able to access the record
> that is being processed but It'd be nice if it didn't cause immediate
> deadlocks but waited
In CF / SQL you don't need stored procedures for that, just the SQL
FOR UPDATE to locjk a row until transaction commit:
<!-- Get candidate rows -->
<cfquery name=q1>
SELECT PK
FROM queue
WHERE running = FALSE
</cfquery>
<cfloop query=q1>
<cftransaction>
<!-- Try to lock -->
<cfquery name=q2>
SELECT *
FROM queue
WHERE PK = #q1.PK#
AND running = FALSE
FOR UPDATE
</cfquery>
<cfif q2.recordcount>
<!-- Lock success -->
<cfset result = q2 />
<!-- Update row -->
<cfquery name=q3>
UPDATE queue
SET running = TRUE
WHERE PK = #q1.PK#
</cfquery>
</cfif>
</cftransaction>
</cfloop>
This is all assuming your database understands and implements the SQL
standard. If it doesn't you may have to translate some of these
queries to something else, like using "with (updlock)" and snapshot
isolation, before it works.
> (or even moved to the next record if possible)
In order to do that you need to make q2 stop waiting for the lock.
There is no way to do so in straight SQL, but several databases offer
a NOWAIT keyword to affect that. Alternatively you can set a query
timeout on q2 on the CF side.
Jochem
--
Jochem van Dieten
http://jochem.vandieten.net/
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Want to reach the ColdFusion community with something they want? Let them know
on the House of Fusion mailing lists
Archive:
http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:323956
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe:
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
