Will there not be a race condition there? If 2 servers run the same code at the same time, wouldn't' they be working with the same set of records? I'm not really sure if and how "FOR UPDATE NOWAIT" solves that?
Russ > -----Original Message----- > From: Jochem van Dieten [mailto:[EMAIL PROTECTED] > Sent: Wednesday, February 08, 2006 4:42 PM > To: CF-Talk > Subject: Re: cross server locking > > Russ wrote: > > So exactly how would I use something like this? I started with saying > > server locking, but the best thing would be... > > > > <cfquery name="myqry" datasource="mydsn"> > > Select top 1000 * from emailQueue with (updlock) where isSent=0 > > </cfquery> > > > > and for that query to return different things on different servers. > > Basically, I guess what I'm looking for is > > > > select top 1000 * from emailQueue where isSent=0 and ROW IS NOT LOCKED. > > I typically use something like: > > <cfquery all> > SELECT pk > FROM table > WHERE finished = FALSE > </cfquery> > <cfloop all> > <cftry> > <cftransaction> > <cfquery record> > SELECT * > FROM table > WHERE pk = #all.pk# > AND finished = FALSE > FOR UPDATE NOWAIT > </cfquery> > do something (email, ftp, etc) > <cfquery> > UPDATE table > SET finished = TRUE > WHERE pk = #all.pk# > </cfquery> > </cftransaction> > <cfcatch type="db"> > </cfcatch> > </cftry> > </cfloop> > > Jochem > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Message: http://www.houseoffusion.com/lists.cfm/link=i:4:231679 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
