11 Replies Latest reply on Oct 27, 2014 2:53 PM by CamdenNarzt

    Unique value validation fails in multi-user environment

      Summary

      Unique value validation fails in multi-user environment

      Product

      FileMaker Server

      Version

      11.0.3.309

      Operating system version

      Mac OS X Server 10.6.7, Windows Server 2008

      Description of the issue

      Two concurrent users running a script are allowed to input the same value into two separate records in a field with validation set to require unique values.

      Steps to reproduce the problem

      On a shared database create a number field and turn on "Unique value" in validation options. Open the file from two separate computers, move to a layout based on the table that contains the field created.  Now run the following script on both computers simultaneously (it may take a few tries to get timing right):

      Set Error Capture [On]
      New Record/Request
      Set Field [table::field; 42]
      Commit Records/Requests [No dialog]

      Have tested with Filemaker Server running on Windows and well as on Mac OS X.  Clients using either Filemaker Adv 9 or 11.  All with same results.

      Expected result

      One of the users is not allowed to commit it's record.

      Actual result

      Two saved records with the same value in the indicated field.

        • 1. Re: Unique value validation fails in multi-user environment
          TSGal

          Ronnie Rios:

          Thank you for your post.

          The Commit Records will still commit the record because you have Error Capture turned On.  After the Commit Records step, I used Get (LastError), and for one user, it returns 0 while the other returns 504 (meaning it is not a unique value).  At this point, I can then decide what I want to do with the non-unique value.

          TSGal
          FileMaker, Inc.

          • 2. Re: Unique value validation fails in multi-user environment

            TSGal,

            Thanks for your reply.

            First, I don't think Filemaker Pro is suppose to commit the record even with error capture on.  That said, in my tests, I also used Get (LastError) and returned 0 when both records where committed.  It's easier to replicate using the debugger and stepping thru the script simultaneously; even the debugger error reporting returns a 0.

            Also, when not run at the same time, Filemaker Pro behaves as expected: one of the records is not saved and error code 504 is returned.

            Many regards,

            Ronnie

            • 3. Re: Unique value validation fails in multi-user environment
              TSGal

              Ronnie Rios:

              If you create a new table in FileMaker Pro, create a Number field and set it for Unique validation, enter 1 into the first record, and enter 1 into the second record.  You will get a dialog box saying "<field name> is defined to contain unique values only.  Allow this duplicate value?" with the options "Revert Field", "No" and "Yes".  By turning on error capture, this dialog box is suppressed and the data will be entered.  If you have the option unchecked "Allow user to override during data entry", you are not able to override, but the script will still override.

              I still cannot get Get(LastError) to return 0 for both clients.  I simply use a field named "Error", and after the Commit Records script step, I have:

              Set Field [Error ; Get(LastError)]

              I don't doubt this isn't happening to you, so after committing the record, follow it up by setting the field to itself and Commit Records again to see if an error is returned.  If the first time it isn't recognized due to simultaneous commit, then the second time, after both records have been committed, will return an error if a duplicate value exists.

              TSGal
              FileMaker, Inc.

              • 4. Re: Unique value validation fails in multi-user environment

                Hi TSGal,

                "By turning on error capture, this dialog box is suppressed and the data will be entered."
                Yes, entered, but not saved; clicking outside the fields to commit the record will display the mentioned dialog box.

                "If you have the option unchecked 'Allow user to override during data entry', you are not able to override, but the script will still override."
                In all my test that option has always been unchecked and the script does not override (unless both users commit at the same time); again, the data is entered but not committed, the way it should be.

                The easiest way to replicate this is to sit in front of two computers with Filemaker Pro Adv and run the script in debug mode.  Step thru the script simultaneously and you can view the records being saved and the last error reported as 0 from the script debugger window.  I would recommend not using a field to capture the error.

                I'll try the workaround you propose. But even if it does work for my current script it does not correct the underlying issue.

                Thanks again,

                Ronnie

                • 5. Re: Unique value validation fails in multi-user environment
                  TSGal

                  Ronnie Rios:

                  With Script Debugger on, I was able to make it fail once (out of 25+ times).  With Script Debugger turned off, I can never make it fail, as I'm executing the scripts simultaneously.  One user will show 0 and the other 504, and it may be reversed the next time.

                  The extra field was used to capture the error.  I changed it to an IF routine, and a user created duplicate dialog box appeared in its place.

                  In the case Get (LastError) returned 0, I added some additional steps that set the "Unique" field to itself and Commit Records and displayed Get (LastError).  This always returned 0 since it was never executed.  To test these additional script steps, I purposefully set a field as a non-unique value and committed it.  Then, I ran the script and it returned 504.  Here are the script steps I used:

                  Set Error Capture [ On ]
                  New Record/Request
                  Commit Records/Requests []
                  Set Field [ <Unique field> ; 110 ]
                  # the "110" started at 1, and then I replaced it each time I ran the script. I probably should have used a global field
                  Commit Records/Requests []

                  If [ Get(LastError) > 0 ]
                     Show Custom Dialog [ "Non Unique" ]
                  Else
                     Set Field [ <Unique field> ; <Unique field> ]
                     Commit Records/Requests []
                     Set Field [ <extra field> ; Get (LastError) ]
                  End If

                  -----

                  Let me know what steps you are using so I can try to replicate exactly what you have.

                  TSGal
                  FileMaker, Inc.

                  • 6. Re: Unique value validation fails in multi-user environment

                    Hi TSGal,

                    sorry it took me so long to reply to your post, I've been very busy.
                    As I mentioned before, using the Script Debugger I get no errors 100% of the time.  I've uploaded a screen recording illustrating what I do.  In order to show both computer screens simultaneously I ran Screen Sharing on my MacBook Pro to view the display of the other computer that was right next to me.  I ran the script twice to demonstrate that it happens every time.  In this test I even added an extra Commit right after the New Record, with no avail.

                    Also, I tested your workaround and it partially works.  You see, when both users perform the script at the same time they create their respective records and both set their fields to the same value, again with no errors reported; then when the field is set again with the same value they BOTH get a 504 error because that value is already in use in not one but two records.

                    I believe this is a serious issue.  The database engine needs to be able to ensure unique values when requested with no exceptions.
                    Thanks,

                    Ronnie

                    • 7. Re: Unique value validation fails in multi-user environment
                      TSGal

                      Ronnie Rios:

                      Thank you for the information, but I've been unsuccessful reproducing the problem.  I have both machines side by side (with the Script debugger), with the mouse executing each script step one by one simultaneously (like your movie), and I still get one machine or the other with a 504 error.  If I turn off Set Error Capture, one of the machines will display the message to "Allow the duplicate value?".

                      How many people are going to be entering data?  How many people will be updating that Unique Value field simultaneously?

                      TSGal
                      FileMaker, Inc.

                      • 8. Re: Unique value validation fails in multi-user environment

                        Hi TSGal,
                        I've figured out that when done via my local server it's harder to replicate (about 25%-33%) but when done with my remote server (located in a different state) I can make it happen almost 100% of the time. I'm guessing network latency helps.

                        BTW, the "Set Error Capture" and "New Record/Request" are not required to replicate it; I've seen it happen with just a "Set Field" and "Commit Records/Requests" on existing records. If you contact me back-channel I'm willing to demonstrate it live via screen share.

                        My guess is that the issue lies with Filemaker Server not being able to recognize that two concurrent users are setting the same value when it happens too close to one another. This could affect solutions running with many users and high traffic or looped scripts relying on unique values (like my case).

                        My solution will be running with about 20 concurrent users but I've worked around it by simply modifying my scripts to not rely on the validation.

                        That being said, you mentioned you have been able to replicate it at least once so you know it can happen, at least to others.

                        Let me know if I can be of further help.

                        Many regards,


                        Ronnie

                        • 9. Re: Unique value validation fails in multi-user environment
                          philmodjunk

                          Just exploring the possible ramifications here....

                          Do you have "validate always" enabled on this field?

                          Does it make any difference? (I would expect not, but want to nail this one down as tight as possible...)

                          • 10. Re: Unique value validation fails in multi-user environment
                            Hi  PhilModJunk, yes, "validate always" is enabled, but have tried both ways and same outcome.  I posted a video in a previous post that shows all relevant field settings and the issue as it happens. Regards, Ronnie
                            • 11. Re: Unique value validation fails in multi-user environment
                              CamdenNarzt

                              Has this ever been fixed? Having a server version that doesn't support concurrent use because it's not thread safe or some such problem is oxymoronical.