I've been trying to reply to correct myself but apparently the only place that I can get messages to the list from is home... not even using webmail (for the same account I use at home) will work... odd.
Anyway... that is NOT the query I ended up with. This is... <cfquery datasource="#variables.dsn#" name="local.getNextQueueItem"> Select top 1 m.messageID , m.messageStatus , m.messageXML from queue.messages m with (updlock) where m.messageStatus = 'pending' and m.dateStarted is null </cfquery> Everything is working as it should now. Thanks again for the help. .:.:.:.:.:.:.:.:.:.:. Bobby Hartsfield http://acoderslife.com http://cf4em.com -----Original Message----- From: Bobby Hartsfield [mailto:bo...@acoderslife.com] Sent: Thursday, June 25, 2009 9:22 PM To: cf-talk Subject: RE: SOT: CF based Message Queue - Thread safe stored procedure Been having issues posting to the list, hopefully this one makes it through... (thanks for posting the question for me Will). I'll forgive the fact that you owe me a car. That is actually something I tried in the stored procedure already but it looks like my problem may actually be somewhere else all together. The queue process goes like this... 1) Grab the next message with status of pending 2) update the message status to running 3) Execute that Message 4) Log the message to a message Queue log table 5) Delete the message I was testing this with the full message queue process and should have put something together that was much smaller and just a test of the locking issue. I was queuing up about 500 messages and firing off about 5 threads to process them. When I was done, I would end up with anywhere from 450 to 475 records in the log table and 60 to 70 messages still in the queue with a status of running. What I didn't check was to see if those 500 messages actually executed... which they are; all 500 every time! So apparently now that I'm locking the records, the process that logs them , then deletes is having trouble reading/deleting them. I'm going to try and change the logic around a bit and see what I can come up with. I started out simple with this then I made it way more complicated (in my head) than it needed to be. Thanks very much for putting me back on track Jochem. I'll keep you posted on the results. Oh. By the way, I did move the sql back into CF and this is what is working. <cftransaction> <cfquery datasource="#variables.dsn#" name="local.getNextQueueItem"> Select top 1 m.messageID , m.messageStatus , m.messageXML from queue.messages m with(XLOCK, SERIALIZABLE) where m.messageStatus = 'pending' and m.dateStarted is null FOR UPDATE </cfquery> <cfif local.getNextQueueItem.recordcount> <cfquery datasource="#variables.dsn#" name="local.updateMessageStatus"> update queue.messages set queue.messages.messageStatus = 'running' where queue.messages.messageID = <cfqueryparam cfsqltype="cf_sql_integer" value="#local.getNextQueueItem.messageID#" /> </cfquery> </cfif> </cftransaction> .:.:.:.:.:.:.:.:.:.:. Bobby Hartsfield http://acoderslife.com http://cf4em.com -----Original Message----- From: Jochem van Dieten [mailto:joch...@gmail.com] Sent: Thursday, June 25, 2009 6:09 PM To: cf-talk Subject: Re: SOT: CF based Message Queue - Thread safe stored procedure 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:324010 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
