4 Replies Latest reply on Aug 16, 2012 12:49 AM by Malcolm

    Need help understanding record locking...

    deanchampeau

      Hello,

       

      Most of my development so far has involved solutions (apps?) that are either single-user or are used by a small number of people. I've never had to worry much about users stepping on each other's toes by trying to edit the same record simultaneously. However, I'm now building a solution that will use scripts that edit a parent record as well as records in child and grandchild tables. The likelihood is pretty high that, without some scheme to prevent it, my scripts will break, resulting in all kinds of damage to my solution's data integrity. Can anyone point me in the direction of a best-practices tutorial regarding this issue, or offer me some tips about how to go about this?

       

      Thanks for listening!

       

      Dean

        • 1. Re: Need help understanding record locking...
          MarcDolley

          What makes you asume that the "likelihood is pretty high..."? Perhaps you could explain the nature of your solution and why you believe that users will be tripping over each other. In most cases, record locking errors aren't that common. But you can build options into your scripts to check if a record is locked before proceeding.

           

          Regards

          Marc

          • 2. Re: Need help understanding record locking...
            deanchampeau

            The solution is essentially an ERP. The main table (jobs) will relate to several child tables for a wide variety of tasks (e.g. sheeting, printing, die-cutting, folding). Plus, there will be a table in-between that regulates the order in which tasks are performed. The intermediate table will allow users to re-order the tasks in case there is a change to the job or if they simply forget a task when they initially do the job planning. The intermediate table is also necessary because it allows me to display all the tasks (which are in multiple tables) in a single portal on a "jobs" layout. (Portals, of course, only display the contents of a single table.)

             

            Anyway, there's a few scenarios in which a script would edit multiple "task" records... Deleting jobs is one scenario. If a user deletes a job, it needs to delete not only the parent record, but also the task records and the intermediate records that define task order. If another user is editing the record for one of the job tasks and another user is simultaneously deleting the job, the script would quite likely fail, resulting in a job that's half-deleted. The user doing the job deletion would have to content with a dialog saying his deletion was botched, and the database would contain a bunch of "orphan" records that are associated with a job that no longer exists. Does this make sense?

            • 3. Re: Need help understanding record locking...
              MarcDolley

              Makes perfect sense. It sounds like a fairly common scenario. The question remains, what is the likelihood of two people working on the same job at the same time? If it's a common occurrence, then you will have to include tests for locked records in your scripts and probably delete from the bottom up (so to speak). Delete the grandchild records, then the child records, then the parent record.

               

              In reality, we rarely encounter a situation where two people are trying to work on the same record simultaneously. It may not be necessary to add these overheads to your scripts.

               

              Marc

               

              P.S. Portals can display the contents of more than one table.

              • 4. Re: Need help understanding record locking...
                Malcolm

                Makes perfect sense. It sounds like a fairly common scenario. The question remains, what is the likelihood of two people working on the same job at the same time? If it's a common occurrence, then you will have to include tests for locked records in your scripts and probably delete from the bottom up (so to speak).

                 

                Does the other person know that you are sitting there, waiting for them to finish editing the record, so that you can delete it? That is whacky. It sounds like you need to discuss the business rules.

                 

                Malcolm