Hi all, I have an issue with a stored proc that hopefully someone can help with...
A little background... I built a CF based message queue system. It is pretty simple; I have a table of 'messages' in the database. I can fire up as many infinite threads as I want and they process those messages. My initial problem was that the same messages were being executed multiple times when I ran multiple threads at once. A simply CFLOCK solved that. The problem now, is that I need to allow the message queue threads to run from multiple servers at once. So now I'm back to messages getting executed multiple times when threads are running from different servers. I moved all of the SQL from the CFLOCK into a stored proc thinking that I could easily wrap the SQL in a transaction (SQL Transaction, not CF). I'm having quite a bit more trouble than I expected. I'm running into deadlocks. 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 (or even moved to the next record if possible) This is the procedure after my latest attempts. Ive actually changed the approach and updated the record first THEn grabbed it to return hoping that would help. It still the same issue. Deadlocks Not sure how this is going to come through in an email but here it is. ------------------------------ IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[getNextMesssageQueueRecord]') AND type in (N'P', N'PC')) DROP PROCEDURE [dbo].[getNextMesssageQueueRecord] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE PROCEDURE getNextMesssageQueueRecord AS SET TRANSACTION ISOLATION LEVEL READ COMMITTED BEGIN SET NOCOUNT ON; begin transaction declare @uuid nvarchar(32) set @uuid = lower(replace(newID(), '-','')) update queue.messages set messagestatus='running' , dateStarted = getDate() , messageUUID = @uuid where messageStatus='pending' and messageID in ( select top 1 messageID from queue.messages with(updlock) where messageStatus = 'pending' order by messageID ) commit transaction select messageID, messageStatus, messageXML from queue.messages where messageUUID = @uuid END Go ----------------------------- Any ideas to resolve this would be greatly appreciated. Thanks! ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| 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:323945 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
