Results 1 to 5 of 5

Thread: HOWTO: Stop 'Deadlock' or 'Error 4106' occurring in DataFlex

Threaded View

Previous Post Previous Post   Next Post Next Post
  1. #1
    Join Date
    Mar 2009
    Location
    New Zealand
    Posts
    1,657

    Lightbulb HOWTO: Stop 'Deadlock' or 'Error 4106' occurring in DataFlex

    Hi All,

    We have had our share of the dreaded DEADLOCK and/or Error 4106 occur in our application – especially in larger 80+ user sites, we were seeing over 2,000 deadlocks a day.

    We have since almost eliminated it and are now seeing less than 10 a day.

    There has been a number of threads relating to this issue, I thought I would share the method we used....

    We accomplished this by creating a new event within the Data Dictionary class called 'Data_Set_Locking' as per:

    Code:
    Class cDataDictionary is a DataDictionary
     
        { MethodType=Event }
        Procedure Data_Set_Locking
        End_Procedure
     
        Procedure Reset_Filemodes_For_Lock
          Forward Send Reset_Filemodes_For_Lock 
          Send Data_Set_Locking
        End_Procedure
     
    End_Class
    The Problem: DataFlex locks all related parents, irrespectively.

    Using the Order Entry workspace as an example, if you are adding or updating a product (item) on an Order, then the corresponding OrderDetail, OrderHeader, Customer, Inventory & Vendor records are locked and so it should be.

    Of these records, locking the Vendor record is almost unnecessary for this operation, as it’s only used as a reference for the product. The DD doesn't know this - only the developer does.

    Therefore, if 2 or more users are ordering 1 or more products of the same vendor at the same time then the likelihood of Error 4106 occurring is pretty high. The bottleneck, in this case, is the Vendor record.



    The Solution: Change the FILE_MODE of any reference only parent tables to READ ONLY

    Using the Order Entry workspace as an example, adding the following code into the cInventoryDataDictionary.dd would resolve the VENDOR table ever causing a deadlock issue.

    Code:
        Procedure Data_Set_Locking
          Boolean bHasRec
          Integer iStatus
         
          Move True to bHasRec
          If (bHasRec and OPERATION_ORIGIN=Self) Begin
            Move (not(OPERATION_MODE=MODE_DELETING)) to bHasRec
          End
          If (bHasRec) Begin
            Get_Attribute DF_FILE_STATUS of Vendor.File_Number to iStatus
            If (not(iStatus=DF_FILE_INACTIVE)) ;
              Set_Attribute DF_FILE_MODE of Vendor.File_Number to DF_FILEMODE_READONLY
          End
        End_Procedure
    Changing the file modes of tables within the Reset_Filemodes_For_Lock (aka Data_Set_Locking) is safe, as the DD resets the file modes of all the tables after it has completed a 'write' operation.

    You do NOT need to store and/or restore the file modes of the tables you change as the DD already does it for you.


    Alternative Solution

    Using NULL Parents – try normalising your data and use NULL parents where possible, if the option above is not feasible.

    For example, If you have a reference, say Customer Category, and the category is ‘NONE’ and if 50%+ of your customers have a category of ‘NONE’ then get rid of the ‘NONE’ category and allow for NULL parent instead.


    Disclaimer

    As this is an advance method, you should use it at your own risk or at least understand the risk involved.

    As a developer, you need to be 100% sure that the table you’re setting as READ ONLY is in fact used as a reference only in the operation it is a part of, may it be Creating, Updating or Deleting.

    If this is wrongly set and the parent table does a table save or delete, you will then get an Error 66 - Attempt to edit a protected record

    And the kicker is when DataFlex reports Error 66, it does NOT report the Table number in the error message therefore you would need to debug every Data_Set_Locking and OnSaveRecord to figure out which table caused the error.


    Summary

    I hope this helps someone beat this dreaded error
    Last edited by raveens; 28-Sep-2022 at 08:08 PM.
    Regards,
    Raveen Sundram

    Software Development Manager
    Excellent Software Ltd
    Auckland, New Zealand
    www.helixretail.co.nz


Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •