9 Replies Latest reply on Jul 7, 2014 1:08 PM by disabled_ntaylor

    Multi-User Related Record Simultaneous Locking Not Working

    emory_brown

      Summary

      Multi-User Related Record Simultaneous Locking Not Working

      Product

      FileMaker Pro

      Version

      13.0v3

      Operating system version

      OSX 10.9.3 and Windows Server 2008 R2 64bit

      Description of the issue

      This is a recurring issue on high traffic systems when two different salespeople submit separate orders at the same second. Both orders have items that reference the same inventory record.

      The orders will not see each other's updates or lock the inventory record correctly, resulting in one transaction writing over the other and causing an inventory discrepancy.

      I have both video documentation and a test file that the issue can be reproduced in.

      Steps to reproduce the problem

      1 - Host file on server 13.
      2 - Connect to file on client A
      3 - Go to layout "Order - Data Entry"
      4 - Click Records > New Record
      5 - Fill in order date, line item name (Product1), quantity (2)
      6 - Connect to file on client B
      7 - Repeat steps 4-6. Enter a line with the same product, quantity (3)
      8 - Submit both orders simultaneously. I programmed the submit button to delay until a certain second of every minute, to ensure that they run at the same time.

      Expected result

      Whichever script tries to update the inventory record second should run into record-locking and return an error.

      Actual result

      Inventory is either updated by one script or the other, but not both. Both transactions run without locking, but one overwrites the other.

      Exact text of any error message(s) that appear

      No error is returned.

      Workaround

      No good work-arounds so far.

        • 1. Re: Multi-User Related Record Simultaneous Locking Not Working

               Emory Brown:

                

               Thank you for the post.

                

               If possible, I would like to take a look at your file. Check your inbox at the top of this page for instructions on how to submit your file.

                

               TSFalcon

               FileMaker, Inc.

          • 2. Re: Multi-User Related Record Simultaneous Locking Not Working

                 Emory Brown:

                  

                 Thank you for the sample file.

                  

                 Essentially, the reason this occurs is two "Set Field" script steps are occurring simultaneously on the same field in the same record. Record locking will occur and the script that falls second on the stack will not set the field. Additionally, if the secondary "Set Field" occurs immediately after the first, the record locking does not occur and the second replaces the contents of the field. 

                  

                 Record locking can only occur if a field is being actively modified:

                  

            Open Record/Request

                  

                 "Use the Open Record/Request script step first to obtain a write lock."

                  

                 Here are a couple of suggested workarounds:

                  

                 Option #1: Review the method used in the Inventory.fmp12 starter solution ( File > New From Starter Solution )

                  

                 Option #2: Create a "flag" field that is set to "1" when a user is currently running the script and check the field value before allowing another user to proceed; afterwards, set the value to "0" to unflag.

                  

                 Option #3: Force the record lock by actively modifying the field to be updated in another window and do not commit the changes until the entire process is complete.

                  

                 Option #4: Force the record lock using "Open Record/Request" and do not commit the record or navigate away from the record until the process is complete.

                  

                 The last three options are more cumbersome than the In - Out method used in the starter solution.

                  

                 TSFalcon

                 FileMaker, Inc.

            • 3. Re: Multi-User Related Record Simultaneous Locking Not Working
              emory_brown
                   

              "Additionally, if the secondary "Set Field" occurs immediately after the first, the record locking does not occur and the second replaces the contents of the field."

                   TSFalcon:

                   Are you saying that the second script winning is intended behavior?

                   Since both clients are connected to the server, that should mean server can handle two users trying to edit the same record simultaneously. 

                   Shouldn't there only be two options: 1) second client gets a record locking error, or 2) it sees the new inventory value set by the first client?

                   Neither of these is happening. Instead, the second client doesn't see the updated inventory quantity and uses the old value, causing data-integrity problems. 

                   Thanks,

                   Emory Brown

              • 4. Re: Multi-User Related Record Simultaneous Locking Not Working

                     Emory Brown:

                      

                     Thank you for the reply.

                      

                     "Are you saying that the second script winning is intended behavior?"

                      

                     Correct. Only a single field is being modified, so FileMaker can not modify the field (twice) without the first change being committed. 

                      

                     "Since both clients are connected to the server, that should mean server can handle two users trying to edit the same record simultaneously."

                      

                     Server is resolving the changes. The number of users doesn't matter because the first user to modify the field will lock the field during modification. 

                      

                     "Shouldn't there only be two options: 1) second client gets a record locking error, or 2) it sees the new inventory value set by the first client?"

                      

                     When two scripts are run simultaneously, FileMaker treats each script as separate user accounts connecting to the script.  If one script is setting a field and that set field process has completed before the second script attempts to set the same field, the result is that the second script has overwritten that field with its own value.

                      

                     TSFalcon

                     FileMaker, Inc.

                • 5. Re: Multi-User Related Record Simultaneous Locking Not Working
                  emory_brown
                       

                  the result is that the second script has overwritten that field with its own value

                        

                  The crux of all of this is that the script increments. In other words, a field is being set by referring to itself . Here's an example (this isn't from the demo file's script, just an example of incrementing in a script):

                  Set Field[ PRODUCT::inventoryQty ; PRODUCT::inventoryQty + 3 ]

                       The demo file is doing a variation of the above. If the 1st script was successful in changing the field's value, the 2nd script should see that new value, and use it. But the 2nd script doesn't see the new value.

                        

                  Here's what should happen if FileMaker was handling the situation correctly:

                       Starting Inventory Qty = 4

                       Script 1 increments by 3. Ending inventory value = 7.

                       Script 2 increments by 3. Ending inventory value = 10.

                        

                       Here's what really happens:

                       Starting Inventory Qty = 4

                       Script 1 increments by 3. Ending inventory value = 7.

                       Script 2 increments by 3. Ending inventory value = 7 (this is 7 because script 2 never saw script 1's changes).

                        

                       There should only be two possible outcomes in a simultaneous submit like this: One of the two scripts should have gotten a record locking error, or both scripts increment properly and you end up with the expected inventory value (in the example above, 10).

                        

                  • 6. Re: Multi-User Related Record Simultaneous Locking Not Working
                    emory_brown

                         Hi TSFalcon:

                         Any updates? 

                    • 7. Re: Multi-User Related Record Simultaneous Locking Not Working

                           Emory Brown:

                            

                           Thank you for the reply. 

                            

                           Field A = 30

                           Purchased 1st = 5

                           Purchased 2nd = 10 

                            

                           Script A (1st user run) grabs the value of field A to a variable ($var) and Purchased 1st to a variable ($purchased).

                            

                           $var = 30

                           $purchased = 5

                            

                           Before $purchase is subtracted from $var:

                            

                           Script A (2nd user run) grabs the value of field A to a variable ($var) and Purchased 2nd to a variable ($purchased).

                            

                           $var = 30 

                           $purchased = 10

                            

                           This is how the change will resolve:

                            

                           Set Field

                           $var - $purchased = 20

                            

                           Since we are acting simultaneously on the same field without first committing and resolving changes, only the final change will occur.

                            

                           This is as designed because the action takes place on a single field using "Set Field" and the value of the field is first set to 25, then immediately set to 20. 

                            

                           Please review the workarounds previously provided. 

                            

                           TSFalcon
                           FileMaker, Inc.

                      • 8. Re: Multi-User Related Record Simultaneous Locking Not Working
                        emory_brown

                             TSFalcon: 

                             Thank you for your detailed reply.

                              Unfortunately, I updated the script to not even use variables -- to just grab the inventory value when it is ready to set the field -- and I'm seeing the same issue. I can send you the new test file without any values being stored in variables. It's all real-time set fields. 

                             

                        Since we are acting simultaneously on the same field without first committing and resolving changes

                        This is what I do not understand. How can two clients be allowed to act simultaneously on the same record? That's what record locking is for. 

                        Thanks,

                        Emory Brown

                        • 9. Re: Multi-User Related Record Simultaneous Locking Not Working

                               Emory Brown:

                                

                               Thank you for the reply. 

                                

                               Record locking occurs when a second user tries to enter a field on a record currently activated by another user. From the help for the "Set Field" script step:

                                

                               "When possible, the Set Field script step makes the record active and leaves it active until the record is exited or committed. Scripts that use a series of Set Field script steps should group these steps together if possible, so that subsequent Set Field script steps can act on the record without having to lock the record, download and upload data, index the field, and so on, after each individual Set Field script step. These functions and record level validation are performed after the record has been exited or committed."

                                

                               "How can two clients be allowed to act simultaneously on the same record?"

                                

                               The actions occur one right after the other without first committing change #1, the second action occurs as if change #1 never occurred at all. 

                                

                               Additionally, I am having one of our Technical Support contacts send you a sample file demonstrating how to avoid this problem. 

                                

                               TSFalcon
                               FileMaker, Inc.