Alpha Software Mobile Development Tools:   Alpha Anywhere    |   Alpha TransForm subscribe to our YouTube Channel  Follow Us on LinkedIn  Follow Us on Twitter  Follow Us on Facebook

Announcement

Collapse

The Alpha Software Forum Participation Guidelines

The Alpha Software Forum is a free forum created for Alpha Software Developer Community to ask for help, exchange ideas, and share solutions. Alpha Software strives to create an environment where all members of the community can feel safe to participate. In order to ensure the Alpha Software Forum is a place where all feel welcome, forum participants are expected to behave as follows:
  • Be professional in your conduct
  • Be kind to others
  • Be constructive when giving feedback
  • Be open to new ideas and suggestions
  • Stay on topic


Be sure all comments and threads you post are respectful. Posts that contain any of the following content will be considered a violation of your agreement as a member of the Alpha Software Forum Community and will be moderated:
  • Spam.
  • Vulgar language.
  • Quotes from private conversations without permission, including pricing and other sales related discussions.
  • Personal attacks, insults, or subtle put-downs.
  • Harassment, bullying, threatening, mocking, shaming, or deriding anyone.
  • Sexist, racist, homophobic, transphobic, ableist, or otherwise discriminatory jokes and language.
  • Sexually explicit or violent material, links, or language.
  • Pirated, hacked, or copyright-infringing material.
  • Encouraging of others to engage in the above behaviors.


If a thread or post is found to contain any of the content outlined above, a moderator may choose to take one of the following actions:
  • Remove the Post or Thread - the content is removed from the forum.
  • Place the User in Moderation - all posts and new threads must be approved by a moderator before they are posted.
  • Temporarily Ban the User - user is banned from forum for a period of time.
  • Permanently Ban the User - user is permanently banned from the forum.


Moderators may also rename posts and threads if they are too generic or do not property reflect the content.

Moderators may move threads if they have been posted in the incorrect forum.

Threads/Posts questioning specific moderator decisions or actions (such as "why was a user banned?") are not allowed and will be removed.

The owners of Alpha Software Corporation (Forum Owner) reserve the right to remove, edit, move, or close any thread for any reason; or ban any forum member without notice, reason, or explanation.

Community members are encouraged to click the "Report Post" icon in the lower left of a given post if they feel the post is in violation of the rules. This will alert the Moderators to take a look.

Alpha Software Corporation may amend the guidelines from time to time and may also vary the procedures it sets out where appropriate in a particular case. Your agreement to comply with the guidelines will be deemed agreement to any changes to it.



Bonus TIPS for Successful Posting

Try a Search First
It is highly recommended that a Search be done on your topic before posting, as many questions have been answered in prior posts. As with any search engine, the shorter the search term, the more "hits" will be returned, but the more specific the search term is, the greater the relevance of those "hits". Searching for "table" might well return every message on the board while "tablesum" would greatly restrict the number of messages returned.

When you do post
First, make sure you are posting your question in the correct forum. For example, if you post an issue regarding Desktop applications on the Mobile & Browser Applications board , not only will your question not be seen by the appropriate audience, it may also be removed or relocated.

The more detail you provide about your problem or question, the more likely someone is to understand your request and be able to help. A sample database with a minimum of records (and its support files, zipped together) will make it much easier to diagnose issues with your application. Screen shots of error messages are especially helpful.

When explaining how to reproduce your problem, please be as detailed as possible. Describe every step, click-by-click and keypress-by-keypress. Otherwise when others try to duplicate your problem, they may do something slightly different and end up with different results.

A note about attachments
You may only attach one file to each message. Attachment file size is limited to 2MB. If you need to include several files, you may do so by zipping them into a single archive.

If you forgot to attach your files to your post, please do NOT create a new thread. Instead, reply to your original message and attach the file there.

When attaching screen shots, it is best to attach an image file (.BMP, .JPG, .GIF, .PNG, etc.) or a zip file of several images, as opposed to a Word document containing the screen shots. Because Word documents are prone to viruses, many message board users will not open your Word file, therefore limiting their ability to help you.

Similarly, if you are uploading a zipped archive, you should simply create a .ZIP file and not a self-extracting .EXE as many users will not run your EXE file.
See more
See less

Data Integrity: Lock multiple records in multiple tables before delete operation

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

    Data Integrity: Lock multiple records in multiple tables before delete operation

    The purpose of this post is to start a dialog that will, hopefully, lead to a better understanding of the way to handle deletion of multiple records from multiple inter-related tables in a multi-user environment.

    Here is a diagram of one such (simplified) senerio:


    I should mention that the forms that the parent tables are associated with also have embedded browses on them. The child tables are represented on these forms as embedded browses. (each parent and it's two children on one form)

    The senerio is as follows. When you delete a record from parent 1, there will (sometimes) be a corresponding record in parent 2 that will also need to be deleted. In addition, all of the corresponding child records will also need to be deleted. I need to make sure that ALL of the records are deleted. There can be no orphans or corrupted relations.

    This is one approach I have been kicking around. I would like to hear feed back on this approach. Is it a good solution? Bad solution? Pros, cons? Alternate solutions?

    Code:
    [COLOR=magenta]option strict[/COLOR]
    [COLOR=blue]' Array of pointers to reference the tables[/COLOR]
    [COLOR=magenta]DIM tbl[6] as P
    [/COLOR][COLOR=blue]' Array of pointers to reference the queries
    [/COLOR][COLOR=magenta]DIM qry[6] as P[/COLOR]  
    [COLOR=blue]' Array to store the number of records that match each queries[/COLOR]
    [COLOR=magenta]DIM rec_cnt[6] as N[/COLOR]
    [COLOR=blue]' Index for while loops[/COLOR]
    [COLOR=magenta]DIM i as N
    [/COLOR]     
    [COLOR=blue]' Populate the array with pointers to each table[/COLOR]  
    tbl[1] = table.open("parent 1 child 1")
    tbl[2] = table.open("parent 1 child 2")
    tbl[3] = table.open("parent 2 child 1")
    tbl[4] = table.open("parent 2 child 2")
    tbl[5] = table.open("parent table 1")
    tbl[6] = table.open("parent table 2")
      
    [COLOR=blue]' Query all of the tables to get the records that need to be deleted
    ' Assume vFilter contains the proper filter expressions for each table
    ' Also get the number of records in each query[/COLOR]
    i = 1
    WHILE (i <= 6)
        qry[i] = tbl[i].Query_Create("",vFilter) 
        numrecs[i] = qry[1].records_get() 
        i = i+1
    END WHILE
        
    [COLOR=blue]' Lock the parent records first.
    ' This should ensure that the children are also locked!
    [/COLOR]ON ERROR GOTO parent_lock_failure
         p[5].change_begin()
         p[6].change_begin()
    ON ERROR GOTO 0
      
    [COLOR=blue]'IF lock was successful, begin delete routine
    [/COLOR]i = 1
    WHILE (i <= 6) [COLOR=blue]' Loop through each table
    [/COLOR]   WHILE (numrecs[i] > 0)[COLOR=blue] ' For each table delete each record in the query
    [/COLOR]        IF (tbl[i].mode_get() <> "CHANGE") 
                 tbl[i].change_begin()
            END IF
            ON ERROR GOTO child_lock_failure
                 tbl[i].delete()
            ON ERROR GOTO 0   
            
            tbl[i].change_end(.t.) 
            numrecs[i] = numrecs[i] - 1 
       END WHILE 
       
       [COLOR=blue]'cLose table, drop query, pack table[/COLOR]
       tbl[i].close()
       qry[i].drop()
       ON ERROR GOTO skip_pack
           tbl[i].pack()
       skip_pack:
       i = i + 1
    END WHILE
     
     
    [COLOR=#0000ff]' Could not obtain a lock to both of the parent tables.[/COLOR]
    parent_lock_failure:
    [COLOR=blue]
       ' Close all open tables
    [/COLOR]   tbl[1].close()
       tbl[2].close()
       tbl[3].close()
       tbl[4].close()
       tbl[5].close()
       tbl[6].close()
      
    [COLOR=blue]  ' Drop all queries[/COLOR] 
       qry[1].drop()
       qry[2].drop()
       qry[3].drop()
       qry[4].drop()
       qry[5].drop()
       qry[6].drop()
       END'
    RESUME NEXT  
     
    child_lock_failure:
       
      tbl[i].change_end(.f.)
    
      WHILE (i <= 6)
           tbl[i].close()
           qry[i].drop()
           i = i + 1
       END WHILE
       END'
    RESUME NEXT
    The solution above is not 100% complete. Some of the error checking was omitted intentionally.
    This script works by locking a single record in each parent table.
    The parent tables are related (not a set) to eachother. They both have a field that is used to correlate records between them.

    The thought behind this is that if I am able to lock both parent records at once then all of the related children should also be locked. Is this assumption correct?

    I look forward to hearing your thoughts...

    #2
    Re: Data Integrity: Lock multiple records in multiple tables before delete operation

    If you lock parent records, then the child records are locked IF referential integrity is enforced in the set creation, AND if you open the sets (with set.open_session()). If you open the tables individually outside of a set structure, then Alpha Five does not recognize that there are child records and the locks and deletions will not flow down through referential integrity.

    That's only one of the reasons your script won't work. You also can't just do a tbl[2].delete() and expect to delete all the records in "parent 1 child 2" that match the vFilter. You can do a delete_range(), or again, if you use referential integrity with cascading deletions, then just deleting the parent records should cascade down if you open the set, not the individual tables.

    Comment


      #3
      Re: Data Integrity: Lock multiple records in multiple tables before delete operation

      Dr. Wayne,

      Thanks for your response. I bought your XBasic for Everyone book and it was a great help!

      Originally posted by Peter.Wayne View Post
      If you lock parent records, then the child records are locked IF referential integrity is enforced in the set creation, AND if you open the sets (with set.open_session()). If you open the tables individually outside of a set structure, then Alpha Five does not recognize that there are child records and the locks and deletions will not flow down through referential integrity.
      That makes much sense. That brings me to my second question... is it safe to use referential integrity in a networked environment when using shadow tables? I have read you should not.

      Originally posted by Peter.Wayne View Post
      That's only one of the reasons your script won't work. You also can't just do a tbl[2].delete() and expect to delete all the records in "parent 1 child 2" that match the vFilter.
      My script queries each table, after which it does a tbl[I].records_get() on each table to get the number of records returned by each query. It then uses 2 while loops. The outer loop steps through each table and the inner loop steps through and delete each record in each table. Is this wrong?

      Code:
      i = 1
      WHILE (i <= 6)
          qry[i] = tbl[i].Query_Create("",vFilter) [COLOR="Red"]'Filter each table[/COLOR]
          numrecs[i] = qry[1].records_get() [COLOR="red"]'Get the number of recs returned by each query[/COLOR]
          i = i+1
      END WHILE
      Code:
      [COLOR="red"]'IF lock was successful, begin delete routine[/COLOR]
      i = 1
      WHILE (i <= 6) [COLOR="red"]' Loop through each table[/COLOR]   
          WHILE (numrecs[i] > 0) [COLOR="red"]' For each table delete each record in the query[/COLOR]
              IF (tbl[i].mode_get() <> "CHANGE") 
                   tbl[i].change_begin()
              END IF
              ON ERROR GOTO child_lock_failure
                   tbl[i].delete()
              ON ERROR GOTO 0   
              
              tbl[i].change_end(.t.) 
              numrecs[i] = numrecs[i] - 1 
         END WHILE 
         
         [COLOR="red"]'cLose table, drop query, pack table[/COLOR]
         tbl[i].close()
         qry[i].drop()
         ON ERROR GOTO skip_pack
             tbl[i].pack()
         skip_pack:
         i = i + 1
      END WHILE
      Originally posted by Peter.Wayne View Post
      [i]You can do a delete_range(), or again, if you use referential integrity with cascading deletions, then just deleting the parent records should cascade down if you open the set, not the individual tables.

      Comment


        #4
        Re: Data Integrity: Lock multiple records in multiple tables before delete operation

        I'm sorry, I misunderstood what you were doing in the second part of your script. The deletion will probably work the way you want it to work. Another part that will fail, however, is
        Code:
         tbl[i].close()
           qry[i].drop()
           ON ERROR GOTO skip_pack
           tbl[i].pack()
        You should drop the query and pack the table before you close the table pointer. You also should use query.options="T", which is more reliable than counting on <query>.drop() to drop the table pointer. You would still be better off with the delete_range() function, though, as it would spare you the while...end while loop.
        As for referential integrity: it works, but it just slows things to a crawl on a network (or at least it used to -- I haven't used it in five years or so.)
        I could propose a different scheme for you. Clearly you are trying to set up a "transaction" in which either all the targeted records in all six tables are deleted, or else none are. Alpha Five doesn't support this but it has a handy "feature" that you can use to your benefit: deleted records remain in the table until the table is packed. Why don't you:
        1) as you do now, create an array of queries for each table;
        2) keep track of the record numbers of each record that is targeted for deletion in each table with the recno_list_get() function;
        3) delete all the records in all the tables.
        4) if a deletion fails to execute, then just undelete all the records whose numbers you have saved in step 2.
        5) if there are no deletion failures, then you can pack.

        Comment


          #5
          Re: Data Integrity: Lock multiple records in multiple tables before delete operation

          Originally posted by Peter.Wayne View Post
          I'm sorry, I misunderstood what you were doing in the second part of your script. The deletion will probably work the way you want it to work. Another part that will fail, however, is
          Code:
           tbl[i].close()
             qry[i].drop()
             ON ERROR GOTO skip_pack
             tbl[i].pack()
          You should drop the query and pack the table before you close the table pointer. You also should use query.options="T", which is more reliable than counting on <query>.drop() to drop the table pointer. You would still be better off with the delete_range() function, though, as it would spare you the while...end while loop.
          No problem, and thanks again for taking time out of your day to enlighten me. I guess it would make sense to not close your table before you pack it since you would need the pointer to the table. :) I'd probably want to do something like this right?

          Code:
           
             qry[i].drop()
             tbl[i].close()
             ON ERROR GOTO skip_pack
             tbl[i] = table.open("tablename",RW_EXCLUSIVE)
             tbl[i].pack()
             tbl[i].close()

          I have noticed that the <query>.drop() method doesn't always drop the query as you point out. Do you have any idea what causes that behavior?I read the documentation on setting query.options="T", but it doesn't really explain things in great detail. So, just to be clear, if I set options="T" and use <tbl>.create_query(), do I still need to use <query>.drop()? or will ALPHA drop the query automatically. The documentation, again, isn't very clear in it's description of what exactly happens. It only says that the query is automatically deleted if "necessary". Which I presume to mean if building the current query would take up the last slot in the query queue. i.e., if this query is the 16th query it would be deleted after the query is run.


          Originally posted by Peter.Wayne View Post
          As for referential integrity: it works, but it just slows things to a crawl on a network (or at least it used to -- I haven't used it in five years or so.)
          I could propose a different scheme for you. Clearly you are trying to set up a "transaction" in which either all the targeted records in all six tables are deleted, or else none are. Alpha Five doesn't support this but it has a handy "feature" that you can use to your benefit: deleted records remain in the table until the table is packed. Why don't you:
          1) as you do now, create an array of queries for each table;
          2) keep track of the record numbers of each record that is targeted for deletion in each table with the recno_list_get() function;
          3) delete all the records in all the tables.
          4) if a deletion fails to execute, then just undelete all the records whose numbers you have saved in step 2.
          5) if there are no deletion failures, then you can pack.
          Excellent solution! I was thinking about coping of all the records into temporary tables so they could be restored if my delete operation failed, but your solution makes much better sense. I would just have to make sure the pack operation never failed. I suppose, once I have obtained an exclusive lock on the table the pack operation should never fail, right?

          Thanks again!
          Jeff

          Comment


            #6
            Re: Data Integrity: Lock multiple records in multiple tables before delete operation

            Jeff,
            Your pack operation doesn't have to succeed all the time; in fact, you don't have to pack at all unless you need to reclaim the space. In the recno_list_get() function you will get a list of all the record numbers that need to be deleted; if the deletion fails, you just undelete those. If there are still deleted (but unpacked) records from a previous successful deletion, they won't be undeleted because they won't be in your list of records to delete.
            Your script never acquires an exclusive lock on the tables, so in a multiuser environment it will probably fail to pack a lot of the time. But it doesn't really matter; if you only succeed in packing once a month, the schema will still work.

            Comment


              #7
              Re: Data Integrity: Lock multiple records in multiple tables before delete operation

              Originally posted by Peter.Wayne View Post
              Jeff,
              Your pack operation doesn't have to succeed all the time; in fact, you don't have to pack at all unless you need to reclaim the space. In the recno_list_get() function you will get a list of all the record numbers that need to be deleted; if the deletion fails, you just undelete those. If there are still deleted (but unpacked) records from a previous successful deletion, they won't be undeleted because they won't be in your list of records to delete.
              Your script never acquires an exclusive lock on the tables, so in a multiuser environment it will probably fail to pack a lot of the time. But it doesn't really matter; if you only succeed in packing once a month, the schema will still work.
              Dr. Wayne, thanks again for lending some clarity to this topic. I have poured over this forum and the documentation for countless hours, and for some reason, have still been struggling to understand how to best manage the task of deleting multiple records from multiple tables in a multi-user environment. With your help, I think the fog is finally starting to clear.
              One more question if I may. When you say, "Your script never acquires an exclusive lock on the tables", what exactly do you mean? Are you saying that you shouldn't lock a table exclusively, or that you can'tlock a table exclusively in a multi-user environment, or are you simply commenting that because my script doesn't obtain an exclusive lock on the table, it will fail?

              Thanks again,
              Jeff.

              Comment


                #8
                Re: Data Integrity: Lock multiple records in multiple tables before delete operation

                Jeff,
                All I'm implying is that your script would need to obtain exclusive access to the table for the pack() to execute. If another user is accessing the table(s) being packed, the pack will fail. But again, it doesn't really matter if the pack fails most of the time, as long as you put in some error-checking code to recover gracefully from the error.

                Comment


                  #9
                  Re: Data Integrity: Lock multiple records in multiple tables before delete operation

                  Dr. Wayne,

                  Thank you again! That too makes sense. I was looking through the documentation and your book, and didn't really find a good example of using the set commands to open a set and delete a record out of the parent table. (using Cascading Deletes\Referential Integrity) I didn't see a reference to a set.delete() command or anything that referred to the deletion of a record in a set. I'm imagining that I need to open the set the get a reference to the parent table and perform the delete operation on the parent record. Could you please give me a brief example of how I would go about doing this in my example? Thank you!

                  Jeff

                  Comment

                  Working...
                  X