Multiple Instances Running - Lock Records to Eliminate Duplication

[josephstern]

I have a few FMP projects that I run where I need to process tens of thousands of records, and each process takes time.

 

So I want to be able to set up additional instances that can just run overnight on various computers in my office to accomplish these projects. 

 

My problem is, one instance will grab a record, and then sometimes another instance will grab the same record and process it. While this doesn't really matter (except for the wasted time) for some applications, in particular, for marketing emails, this is a problem.

 

I'm trying this:

Set Error Capture [ On ]


Perform Find [ Specified Find Requests: Find Records; Criteria: Test::Sent: “="no"” ] 

Go to Record/Request/Page [ First ] 

Loop 


If [ Test::Sent = "No" ] 

Open Record/Request
 

Set Field [ Test::Sent; "Yes" ] 

If [ Get ( LastError ) <> 301 ] 

Set Field [ Test::Result; “<send email>” ] 

End If 

Go to Record/Request/Page [ Next; Exit after last ] 

End If 

End Loop

 

So, say I have 100 matching records. I'll get 110 or so emails.

 

I can't figure out how to truly get each instance not to step on the toes of another instance. Any ideas? I've try committing records, flushing the cache to disk, etc.

[comment]

I am confused:

 

So, say I have 100 matching records. I'll get 110 or so emails.

 

I don't see how you can possibly conclude that from your test, when it's not sending any e-mails and - more importantly - it's not counting the number of times a record has been processed. If the find has found 100 records, you can end up with at most 100 records marked as sent - with no way to tell if any records were "sent" more than once.

 

You should be using a number field, initialized to 0 or empty, by:

Set Field [ Test::Sent; Test::Sent + 1 ]

 

or, even better, a text field (initially empty) as:

Set Field [ Test::Log; List ( Get(AccountName) & " " & Get(CurrentTimestamp) ; Test::Log ) ]

 

That way you can tell who processed the record when.

 

 

In any case, opening a record successfully locks it to other users, so I believe you should test for that first. I don't think you need to set a field for this - the Open Record step should generate an error of its own. Next, you should see if another user has not already processed this record during the time elapsed between your find and your opening the record. Finally, you should commit the records at the end of the loop.

 

 

---

BTW, the processing could be very likely optimized by dividing the eligible records between the stations.

[comment]

Just to make sure: you are sharing the file properly, using Filemaker's peer-to-peer hosting, as described here:

http://www.filemaker.com/11help/html/sharing_data.15.1.html#1029565

 

Otherwise all bets are off.

[LaRetta]

Perform Find [ Specified Find Requests: Find Records; Criteria: Test::Sent: “="no"” ] 

Go to Record/Request/Page [ First ] 

Loop 


If [ Test::Sent = "No" ] 

 

I also think you can skip the If[] test since it indicates ( if I read it correctly ) that you already find all 'no' records first.

[josephstern]

Just to make sure: you are sharing the file properly, using Filemaker's peer-to-peer hosting, as described here:

http://www.filemaker.com/11help/html/sharing_data.15.1.html#1029565

 

Otherwise all bets are off.

 

Thanks - using an FMP 11 server on a Mac Mini.

I also think you can skip the If[] test since it indicates ( if I read it correctly ) that you already find all 'no' records first.

 

Right, but I was doing a double-check since another instance could grab a record between the find and this step.

I am confused:

 

 

I don't see how you can possibly conclude that from your test, when it's not sending any e-mails and - more importantly - it's not counting the number of times a record has been processed. If the find has found 100 records, you can end up with at most 100 records marked as sent - with no way to tell if any records were "sent" more than once.

 

You should be using a number field, initialized to 0 or empty, by:

Set Field [ Test::Sent; Test::Sent + 1 ]

 

or, even better, a text field (initially empty) as:

Set Field [ Test::Log; List ( Get(AccountName) & " " & Get(CurrentTimestamp) ; Test::Log ) ]

 

That way you can tell who processed the record when.

 

 

In any case, opening a record successfully locks it to other users, so I believe you should test for that first. I don't think you need to set a field for this - the Open Record step should generate an error of its own. Next, you should see if another user has not already processed this record during the time elapsed between your find and your opening the record. Finally, you should commit the records at the end of the loop.

 

 

---

BTW, the processing could be very likely optimized by dividing the eligible records between the stations.

 

 

 

I'm sorry I wasn't clearer here. The "<send email>” was a long winded external call to my SMTPit plug-in. I'm definitely getting more than 100 emails here.

 

I agree that the "Open" step, if completed without error, should do the trick, but somehow it's just not. Maybe I'm not correctly testing for the error? Should I be clearing the error register somehow before performing the "Open" step so that I'd be sure to get an accurate result?

 

I like the logging idea and I'll give that a shot. But even if I can show that two instances are sending an email from the same record, then what?

 

Thanks for all the replies and help!

[comment]

But even if I can show that two instances are sending an email from the same record, then what?

I think that if the duplicates are very close in time, it would indicate that there is a gap in your process that allows the "competitor" to sneak in and process the record just before you lock it. This is why I suggested moving the error capture to immediately after Open Record, and test for "sent" only later.

 

Should I be clearing the error register  

No need, it clears itself.

 

using an FMP 11 server on a Mac Mini.

I am not quite sure what you mean by that. FMP is not server. If you are not sharing the file through a single host (which could be Filemaker Server or an instance of Filemaker Pro), then there's no locking mechanism in place - and worse, your file will almost certainly become corrupted.

[josephstern]

I think that if the duplicates are very close in time, it would indicate that there is a gap in your process that allows the "competitor" to sneak in and process the record just before you lock it. This is why I suggested moving the error capture to immediately after Open Record, and test for "sent" only later.

 

No need, it clears itself.

 

I am not quite sure what you mean by that. FMP is not server. If you are not sharing the file through a single host (which could be Filemaker Server or an instance of Filemaker Pro), then there's no locking mechanism in place - and worse, your file will almost certainly become corrupted.

 

Sorry for the inaccuracy. It's an FM Server 11.

[comment]

Ok, so that's one less thing to worry about. So, have you run the test with the logging yet?