cfthread or cflock?

[Balance]

I've been using CF for a while but haven't had an opportunity to use cfthread (and have used cflock occassionally).

I have a page that does a number of resource intensive things, in the following order:

1) Runs a stored proc (let's call it SP 1) that selects everyone that will get an email

2) Writes the email info to a table and a bunch of related tables

3) Another SP that does a big insert in different tables

4) CFMail using the query attribute from step #1

The issue I'm running into is that if user a selects a "contact list" of say 5,000 records at 11:30 and then user b comes along and selects a list of 100 at 11:32, the email content from user a will go out to user b's list b/c it ran faster than the first process. Should I wrap the whole page in one giant cflock or is this a good scenario for cfthread? This seems like the classic "race condition" issue which is addressed by cflock but thought I'd ask.

Thanks for any insight.

[ilssac]

Neither option sounds very good to me.

If user b's list ran faster then user a's then the two jobs where already threaded.  I presume they are threaded at the HTTP request level, but one would need a more indepth understanding of your code to know this.

If you single thread the process, that means user b can not do ANYTHING until user a's job is complete, which sounds like it could be a while.

What you need to figure out is why user a's and user b's data are getting comingled.  Yes you have a race condition, but in this case I suspect the culprit are in these tables where you store information about the jobs, and that the records in these tables do not have the proper identifiers so that both u

[Owainnorth]

Completely agree with ilssac here, in this situation neither is suitable.

You're trying to get around database concurrency with the wrong tools. What you're kinda trying to reinvent is SQL Server's Serializable locking, whereby each query runs as if it were in sequence with when other transaction start times. However, that's generally a very bad thing except in extreme cases.

I would look more into adding columns into your email info table to give it the concept of "batches", which is what we did here. One table that stores a batch id, the user who started it, when it was created etc, and when it completed. You then insert the email details into another table, and FK it across.

You can then deal with emails on a batch-by-batch basis, rather than all at once.

Trying to use a basic holding table which supports multiple users is a dark road, down which only severe arseache lies.

Hope that helps.

O.

[Adam Cameron.]

As the others have said: this is a data design/integrity problem.  CTHREAD won't help you, and CFLOCK would just be treating the symptom, not the problem.

Two questions:

1) Why aren't steps 1-3 not all done on the DB, returning a final recordset to CF for doing the emailing?

2) How is it the two requests can't distinguish between what's their data and what's another request's data?

--
Adam