10 Replies Latest reply on Apr 10, 2017 3:08 AM by ClickWorks

    Unique validation fails when multiple serverside sessions are committing at the same time.

    ClickWorks

      Replicated on:

       

      Filemaker server 15.0.1.137

      OS: Windows Server 2012 R2 Standard

       

      Filemaker server 15.0.3.308

      OS: windows sever 2008 R2 standard

       

      Filemaker Pro Advanced Client 15.0.3.305

      OS: Windows 10

      (the client is not really important here and is just used to make it easier to replicate and see the bug)

       

      We originally encountered the issue on filemaker server 13.0.9.905 running on a mac mini.

       

       

      Description of the problem

       

      Use case:

       

      We discovered this issue with a certain use case of us, where we had the specific requirement that certain scripts on the server may not run simultaneously. Therefore made use of a locking mechanism, relying on FM's unique validation.

       

      Before our script begins its tasks, it first navigates to a Lock table and attempts to create a lock record. Every type of lock record has a specific name and this must be unique, because there may never be more than one lock of a certain type. So there is a unique validation constraint on the "Name" field in our Lock table (Always validate, no override).

       

      We then use set error capture to capture the commit error (504), if there was no error then the script has obtained the lock and can continue.

      Once it is done it removes the lock, allowing others to obtain it. If it encounters error 504, then there already exists a lock with the same name and we revert our newly created record.

       

      When we fail to obtain the lock we wait for a moment and we keep trying again to obtain the lock, until a specified time limit is exceeded and we stop trying. This is done with the logic that the lock may be released any moment and if we keep trying for a while then the probability to successfully obtain the lock should increase.

       

      Issue:

       

      Because we rely on the FM database engine to prevent duplicate locks this looks like a very secure mechanism. Unfortunately when multiple serverside sessions are constantly attempting to commit the lock record, our locking mechanism can fail. The moment the lock is released, a race condition is created and multiple records are committed at the same time.

       

       

      How to replicate

       

      To replicate the issue we have included a demo file of our locking mechanism. In here we simply launch 10 serverside scripts that in a loop will constantly try to obtain the lock. During 120 seconds they will keep looping with a 0,1 second pause. The frequency in attempts (seconds pause) is intentionally chosen a little bit quicker than our original use case to increase the change of our problem occurring.

       

      1. Host the demo file on a filemaker server. Make sure Maximum Simultaneous Script Sessions is set to be above 10. (This is to prevent the interference of any strange behavior of FMS when the maximum number of sessions is exceeded, which would be completely unrelated to our issue.)
      2. Full access login: User: Admin Pass: Admin
      3. You will see an empty lock table, to initiate the demo simply click on the green run button.
      4. The script runs and a single lock record (LCK), with name "LCKName" is created. (The server session we started the earliest will probably claim the lock before the others.) We now have one good LCK record and 9 server side sessions that are trying to obtain the lock but constantly fail. (error 504 in server error log)
      5. Now delete the LCK record, by clicking on the bin. Now it is a rather a matter of luck if the bug will occur or not, so if you see again just one LCK record delete it again. And keep doing so, until multiple LCK record are visible. When no more LCK records appear, click run again. But due to the settings in this demo you should almost have a 100% chance of encountering the bug on the first run.
      6. When multiple LCK records appear with the same name, you have successfully replicated the issue. You can then see that these records where committed at exact the same millisecond.

       

      Workaround

       

      For now no solid workaround exist for this issue, a temporary solution in our use case was that we only run once and then quit. This way we drastically reduced the chance that our bug occurs.

       

      Clickworks

        • 1. Re: Unique validation fails when multiple serverside sessions are committing at the same time.
          CamelCase_data

          I'd be surprised if it was this simple and it had escaped you, but have you tried capturing the last error after the Set Field and not after Commit (so after line 56 in the _LCK.Set script rather than after line 59).

           

          That sort of makes more sense to me, and according to a quick test, also seems to work (though it's difficult to be certain as I always need to run the test a few times before I get the duplicate).

          • 2. Re: Unique validation fails when multiple serverside sessions are committing at the same time.
            TSGal

            ClickWorks:

             

            Thank you for your post and file.

             

            I am able to replicate the issue.  Multiple records with "LCKName" appear.

             

            Debugging the script is a bit time-consuming because of the custom functions and determining what is being passed.  Regardless, I have sent your post and sample file to our Development and Testing departments for review.  When I receive any feedback, I will let you know.

             

            TSGal

            FileMaker, Inc.

            1 of 1 people found this helpful
            • 3. Re: Unique validation fails when multiple serverside sessions are committing at the same time.
              TSGal

              ClickWorks:

               

              This is a timing issue.  If you turn on the option "Wait for completion" for the "Perform Script on Server" script step, the issue no longer occurs.

               

              TSGal

              FileMaker, Inc.

              • 4. Re: Unique validation fails when multiple serverside sessions are committing at the same time.
                CamelCase_data

                TSGal How can timing be a factor when it comes to a unique validation failing? I believe the question here isn't about how the script can be changed to avoid the issue; the scripting seems to be designed on purpose in order to reproduce what seems to be a bug in FileMaker's data validation.

                 

                I would expect this kind of data-level validation to be enforced absolutely, with no possibility of duplicates arising as long as the unique validation is in place, not matter how you program your script, do your manual data entry, import data, or whatever you do to edit data.

                • 5. Re: Unique validation fails when multiple serverside sessions are committing at the same time.
                  TSGal

                  CamelCase201507 (and ClickWorks):

                   

                  My apologies.  I forgot the point of the script was to create 10 simultaneous clients accessing the file.  The first note I received from Testing was the "Wait for completion" not set.  I've rewritten the original report.  I'll let you know when I receive more information.

                   

                  TSGal

                  FileMaker, Inc.

                  • 6. Re: Unique validation fails when multiple serverside sessions are committing at the same time.
                    ClickWorks

                    Thanks for your suggestion David Wikström,

                     

                    But it won't be solved that way, the unique validation error is generated when you attempt to commit the record and not when you set the field. This means that there won't be any error to capture during the set field ($intError = 0) and you will miss the "real" error on the commit. In the script we check the $intError variable to see whether our commit succeeded. The script will asume as if everything went perfectly and return "blnSuccess" as true and won't try again (We always successfully get the lock).

                     

                    Because we immediately stop trying, the chance of our bug occurring becomes very small and we may get the illusion it works fine.

                     

                    Clickworks

                    • 7. Re: Unique validation fails when multiple serverside sessions are committing at the same time.
                      CamelCase_data

                      You're right about that ClickWorks.

                      One - pretty quirky - way of detecting the error before committing would be to use "Insert Calculated Result" followed by "Go to Next Field". In my quick test, the "Go to Next Field" generates an error 1 (!?) when there's a validation error after the "Insert Calculated Result". Not really sure if this can be of any practical help in your case...

                       

                      The real, serious, issue in what you report is of course how come field-level data validation isn't reliably enforced.

                      • 8. Re: Unique validation fails when multiple serverside sessions are committing at the same time.
                        ClickWorks

                        Thanks for your suggestion David Wikström,

                         

                        But attempting to capture our unique validation error with a roundabout way (insert calculated result on name and error capture on go to field). Also doesn't work, in my test case there are still multiple records being created. So triggering the unique validation on a different way doesn’t help. But I do get the feeling the bug occurs a bit less frequent. Maybe also due to the error 301 (record in use) with the insert calculated result and the error 302 (table in use) with new record, that are now occasionally visible in the server error log.

                         

                        In case you were worried, for our specific user case we now avoid the issue (only single schedule attempts, and tries only once), so we're not in an urgent need of an alternate solution or quick fix. It is just problematic that the unique validation isn't strictly enforced by de database engine, although we tell it to (always validate).

                         

                        Clickworks

                        1 of 1 people found this helpful
                        • 9. Re: Unique validation fails when multiple serverside sessions are committing at the same time.
                          CamelCase_data

                          What really worries me is not so much your specific case, but the underlying implications of this issue to the reliability of FileMaker's data validation.

                           

                          Have you tried checking if the issue applies to unique validation only, or also to other validation criteria?

                          • 10. Re: Unique validation fails when multiple serverside sessions are committing at the same time.
                            ClickWorks

                            We only extensively tested with unique validation, but I think it will only occur when FM has to check other records for its validation. For example the "not empty", "in range", "member of" validation would always return false when the record would not fit the criteria. It wouldn't matter if others are committing as well and whether they succeeded or not.

                             

                            While other validations that might fail as well like "existing value" or "validation by calculation" are a bit difficult to reproduce. You need a validation criteria that returns true or false depending on the actions (ex. commit) of other serverside sessions. For the calculation I just did a quick test, extra To with a cartesian join and validation by calculation "ValueCount(FilterValues(List(_LCK~All::Name); _LCK::Name)) < 2" (similar to our unique validation)

                            And it fails as well (But this seems to be a bit less robust and maybe the calculation is in some cases evaluated incorrectly).

                             

                            Clickworks

                            1 of 1 people found this helpful