4 Replies Latest reply on Nov 22, 2015 9:56 AM by DEC

    Import Script in a Multi-User Environment = Danger?


      Hi Everybody!


      I'm designing an FM database for a multi-user environment, and there's an important function of the database that concerns me.


      In short, the function enables the user to create a new parent record and its various child records based on a pre-existing template parent record and the corresponding child records of that template.


      In detail, the way that it works is as follows:


      Step 1.

      The database first asks the user to identify the pre-existing target parent template, and then sets a variable with the unique id of that parent template (ID_ParentTemplate).


      Step 2.

      It then conducts a find among all the children of that targeted parent template in order to prepare the found set of template children for import.


      Step 3.

      It then creates a new parent record, and then sets a variable with the unique ID of that new parent record (ID_Parent) that will later be applied to the imported children of the targeted parent template so that they'll be adopted as new child records of the new parent record.


      Step 4.

      It then imports all of the records in the found set in Step 2 into a separate "nursery" table where through a looping method the ID_Parent is established for each newly-born imported child record and where various other housekeeping operations are additionally applied to them.


      Step 5.

      Once these housekeeping operations are complete, the database then imports THOSE prepared child records into the table for all the child records associated with the various parents in the database.  Because the ID_Parent was established for each child record in Step 4, the pre-existing relationship between the parent and its children is automatically established.  Note that in Step 4, the database first deletes ALL of the records then existing in the "nursery" before the import in that Step 4 occurs, so as not to create a mess in the "nursery" from prior operations.


      My Concern:

      My concern is what disasters, if any, may arise when and if users effect the above script at more or less the same time?  I'm particularly concerned about the deletion that occurs in Step 2 that I referenced in Step 4.  Couldn't it happen that a trainwreck occurs if one user effects the script just before or after another user does?  Or is the order of operations sufficiently established that the first user will automatically come behind the second user and that the deletion of all the records and the subsequent looping script in Step 4 will not result in the application of prescribed attributions to the wrong set of child records, and the later importation of the wrong records?


      Your thoughts would be much appreciated; many thanks in advance!



        • 1. Re: Import Script in a Multi-User Environment = Danger?

          You've assessed the situation correctly. Step 4 is a potential train wreck. All that would have to happen is for one user to execute the mass deletion at the same time another user is trying to update a new record set.


          Can you not perform the housekeeping in the eventual child table?


          If not, I suggest you tie the user's account name or session ID to the "nursery" table records and only delete the records tied to the current user. And error trap this thing to death.

          • 2. Re: Import Script in a Multi-User Environment = Danger?

            Many thanks, Mike!


            I love the idea of discarding the notion of the nursery.  It's possible to conduct those housekeeping operations in the eventual final home table of the imported records as you suggest, but only if once imported, only the imported records would comprise the found set in that table immediately following the import, so that the housekeeping looping process would operate only on those imported records, and not on any of the other thousands of pre-existing records in that table. The most important step in the housekeeping is to assign the parent ID to each of the imported children; until that's done they all exist in a vacuum. I can try that process over the weekend, but do you happen to know whether this logic is sound and viable?


            Sent from my iPhone

            • 3. Re: Import Script in a Multi-User Environment = Danger?

              Whenever you perform an import, the resulting found set is always the imported records. So you won't affect any of the others.

              • 4. Re: Import Script in a Multi-User Environment = Danger?

                Hi Mike,


                I'm circling back to tell you that your advisement was right on the money and has not helped me avoid the train wreck, but further vastly simplified the entire process.  I can't thank you enough for the generous extension of your time and your impressive expertise, my friend; many, many thanks!!!