Concurrency problems in FM3?

[BobWeaver]

Has anyone encountered concurrency problems (ie., two or more users attempting to edit the same record at the same time) with Filemaker3? I have a problem in a multiuser database which I suspect might be a concurrency issue. It occurs when a script in the main datafile runs a subscript in a related file to search for a particular record and then link the current records in the respective files. The script works as required most of the time. But, occasionally, the record in the related file that ends up being linked to the main record is the wrong one (always record no 1 in the related file.) I checked the usual things like making sure the script goes to the proper layout and that the applicable fields are present etc. Nothing seems amiss. Has anyone else encountered anything like this?

Platform: FM 3 with the last available version 3 patch running on about 20 Macintoshes, and FM server version 2 with the last available patch running on a server Mac. Because of costs and the number of machines involved, we don't want to upgrade to version 5 until we have eliminated other plausible causes of the problem.

[Vaughan]

Errr, you must mean Server version 3... FMP Server v2.1 isn't compatible with v3 clients.

[BobWeaver]

Yes, sorry, it was a typo.

Anyway, I'm curious how Filemaker resolves concurrency problems when scripts are running. I know that when a user edits a record in a file, other users are locked out. But, when running a script that involves modifying records in two different files how do you prevent another user from grabbing the record in the background file before the first user establishes a link?

[Vaughan]

"...how do you prevent another user from grabbing the record in the background file before the first user establishes a link?"

You cannot, nor would you want to. If a record is in use then it should be locked.

The question should be "How do I write a script that checks for other users before running" or something similar.

I have a hunch that that isn't the problem anyway, because records are only locked if they are being editied (or the user has clicked in a field) and a user can only work in one record at a time. It's probably a script-logic problem, or related (no pun) to relationships.

I cannot see how upgrading to FMP 5 will help the problem. Better to try and work out what it is.

Can you reproduce it? What if you edit a record and run the script, does it occur then?.

[BobWeaver]

What the database is supposed to do is as follows:

The main db keeps track of grain purchase contracts. Occasionally, the buyer will request a sample from the grower. When it arrives, it is stored in a slot on a shelf. The slots are numbered 1..2500. A database file is used to track where the sample is stored. The sample file has 2500 records corresponding the storage slots. It has 3 fields: slotNo, OccupiedYN, and ContractNo. The contents of SlotNo are numbered in sequence from 1 to 2500. When a sample is received, the buyer will normally have the contract record from the main purchase contract database on the screen. He clicks a "sample received" button which executes a script which copies the current contract number from the current record, then executes a subscript in the sample file. The purpose of this subscript is to find a slot in which to store the sample. The only criterion is that the slot be currently unoccupied. So, a find request is executed: OccupiedYN="no". A check is made to ensure that at least one record is found. The first record of the found set is assigned to the sample. The contract number (which was previously copied) is stored in the contractNo field. The OccupiedYN field is set to 'yes', and the slot number from the slotNo field is copied so that it can then be pasted back into sample slot number field in the contract file.

All of the above has been tested, and it works...most of the time. It usually starts acting up a few days after I have left the site (150 miles from where I live). The problem is that it begins to assign slot 1 (the first record in the sample file) over and over. As an additional safety check, I added a conditional statement to the subscript right after the find request, to double check that the OccupiedYN field does indeed contain 'no'. It didn't make any difference. It still reassigned slot 1. BTW, it's not a case of all slots being full. This has always started happening when there were still plenty of empty ones.

The script is so simple that it's hard to think of anything that could possibly be amiss, especially since it works most of the time.

This problem has been around for 3 years now. I have tried rewriting the script in different ways. The result is always the same. It works correctly for a while, and then starts to assign the same slot over and over. Am I somehow missing something obvious? I'm at my wit's end.

[This message has been edited by BobWeaver (edited November 27, 2000).]

[dspires]

It's my understanding that if you attempt to run a script in a multi-user environment, if a record is encountered that is locked, it will be skipped. You can include error-capturing features to alert you to this. Another twist is that if portals are used, the first record (or portal row that is selected?) in the portal view falls into this locked scenerio.

[This message has been edited by dspires (edited November 27, 2000).]

[BobWeaver]

This is interesting, because the basic layout that is on most users screens is the grower file which has a portal to the purchase contract file and includes the related sample slot number field. Maybe this is the problem. If other users have this layout up on their screen the sample number field may be locked. The thing is, the only sample numbers that display are ones that are already linked and so they should be skipped anyway. But, it looks like portals create enough problems that it may be the best place to look for the problem. I've looked everywhere else. I may be able to redo the main page without the portal or something.... More sleepless nights.

Are there any good books that explain the details of how scripts etc., work in a multi-user mode?

[Vaughan]

I'd also look at the logic behind the slot occupied check. How exactly yo you "mark" a slot?

The IsEmpty() function is a very rubust and reliable way of checking fields. I'd suggest that you "mark" a slot when it is occupied, and empty the field when it is not. Then you can use the IsEmpty(slot) function to check, and it doesn't care what the contents actually are, just if it's empty or not.

I'd also lock down the entry options to the fields as much as possible: use a check box and/or do it with a script. Minimise the chance for rogue data in the field.

Then carefully check the logic of the script that finds the first available slot. Things that work really well in a single-user environment often break in multi-user mode -- stuff like imports and exports between databases especially.

Maybe your error check should be looking for NO records (empty found set) rather than one record. Error checking can be tricky in scripts...

[This message has been edited by Vaughan (edited November 27, 2000).]

[BobWeaver]

I have been using 'yes' or 'no' in the Occupied field, but will try the isEmpty() function. As you say multi-user mode brings some interesting problems with it, and I suspect it has a lot to do with these problems. I have quite a bit of experience with Filemaker as a single user database, but virtually none with the multi-user environment, so I am really feeling around blindly at this point.

I will probably hitch up the dogsled and head out there later this week. Better go buy some beer for trip, I guess (speaking of feeling around blindly.)

[This message has been edited by BobWeaver (edited November 28, 2000).]

[dspires]

As far as books go, Chapter 17 (Multiuser Design and Deployment Considerations

) in Coulombre/Price Using FileMakerPro 5 - covers this area quite well. They also make a distinction in the behaviour of FMP5 and FMP Server in the multiuser environment.

BTW, the error code for a locked record is Error 301 for error trapping in your scripts.

[This message has been edited by dspires (edited November 28, 2000).]

[BobWeaver]

Great. I ordered that book a few days ago. I'll see what it has to say.

Thanks to all for the help.