none
Sync Framework infinite loop RRS feed

  • Question

  • Hello I'm using MSF 2.0 and I have an infinite loop on my MSF based application. I describe the problem in

    a very simplified way.

     

    I have two tables that are synchronized, Sales and SalesLanguage. Table Sales has a trigger in both databases which insert the same row

    in table SalesLanguage. When data is synchronized (table Sales is synchronized first) I get a LocalInsertRemoteInsert conflict, which is expected

    because in both sides the trigger on table Sales has inserted the same row. I handle the conflict with 'RetryWithForceWrite' which I expect that

    data from first database wins and is inserted on the second database and data on the second database is discarded.

     

    After doing that, a conflict LocalUpdateRemoteUpdate arises on the same row, which I don't understand since I don't see in SQL Profiler an update on

    this row on neither side. Then 'RetryWithForceWrite' is used again, and I have an infinite loop.

     

    Remember that this is not the real scenario. In the real scenario the trigger doesn't always insert the same row, it should only insert on certain

    conditions, but in one customer the trigger didn't check properly the conditions and the customer computer hanged. So I would like to understand

    what's happening to avoid this situation on other customers.

     

    Also, I have tested the problem with MSF 2.1 and I get the same results.

     

    Regards.


    jprieto
    Friday, February 18, 2011 10:26 AM

All replies

  • Here is a theory. See if this is true.

    This is because the trigger on the Sales table needs more logic to handle the different scenarios.

    After the insert-insert conflict occurs, and you chose RetryWithForceWrite, sync tried to update your destination with the source row. In doing so, your trigger on Sales got executed and tried to insert a row into the SalesLanguage table. This failed because a row with same PK already existed. This failed and sync thinks this is because of an update-update conflict (reason being that an update was tried and row exists on the target - so update-update is the most possible reason).

    Now if your trigger correctly determines that when the sales row is getting updated after the conflict, and in turn updates the SalesLanguage row, that row applying will succeed - resulting in the correct resolution of the Sales row conflict.

    See if this theory is right and if so, try updating your trigger logic and see if you can resolve the problem.


    This posting is provided AS IS with no warranties, and confers no rights
    Wednesday, February 23, 2011 6:18 AM
  • Hello Mahesh.

     

    First of all, thanks for your answer. But there is something I don't understand.

     

    After the insert-insert conflict and I choose RetryWithForceWrite, I expect Sync Framework to update the row which has produced the conflict, which is in SalesLanguage table, which has no trigger at all. Furthermore, the trigger on table Sales is only a insert trigger, so in case Sales table is updated (which I don't know why has to be updated) the trigger also is not executed.

     

    Am I missing something in your answer ?

     

    I know that probably the problem is on the trigger and in the real scenario I have modified the trigger to avoid this problem, but I need to know what really happened to avoid this situation again.

     

    Best Regards.


    jprieto
    Thursday, February 24, 2011 7:06 AM
  • To narrow down, can you just simplify the repro and have only the SalesLanguage in the scope and try your repro.

    Also if the schema and trigger on SalesLanguage can be posted, it can help.

     


    This posting is provided AS IS with no warranties, and confers no rights

    Thursday, February 24, 2011 8:20 AM
  • Hello.

     

    Problem can’t be simplified more. If I put only one table with the trigger that insert, then synchronization fails because a primary key violation, no conflict happen.

    The schema of the tables and the trigger are (remember that this is only an example, not the real scenario in which there are about 20 tables and a complex trigger).

     

    -Sales : it doesn’t matter the schema but you can create it with following information.

                    Columns:

                                   -Number   int

                                   -Version    int

     

    Both columns are the primary key

     

    -SalesLanguage

                    Columns:

    -Number   int

                                   -Version    int

                                    -LanguageId int

     

    All columns are part of the primary key.

     

    A insert trigger on table Sales:

     

     

    CREATE TRIGGER [dbo].[TI_Sales] ON [dbo].[Sales]

    FOR INSERT

    AS

     

    insert into SalesLanguage (Number, Version, LanguageId)

    SELECT 1, 1, 25

     

    GO

     

     

    Then insert one row in table Sales in one side. The trigger will insert an additional row on table SalesLanguage. Then synchronice a scope with both tables. The insert on table Sales when applied on the second side will insert on table SalesLanguage.  Then the synchronization tries to insert the row on table SalesLanguage and the conflict arises.

     

    Remember that my question is not about how to solve the proble, question is, why on this scenario a infinite loop happens.


    jprieto
    Thursday, February 24, 2011 12:42 PM
  • Can you also tell what rows you inserted into Sales and SalesLanguage at both the sides please?
    This posting is provided AS IS with no warranties, and confers no rights
    Friday, February 25, 2011 6:59 AM
  • - First Side:

        Insert INTO Sales  (Number, Version)
        SELECT 1, 1

      Then the trigger inside SalesTable will do the following insert

        insert into SalesLanguage (Number, Version, LanguageId)
        SELECT 1, 1, 25

    - No rows inserted on second side.

    - Then Synchronice (UploadAndDownload)

    - Sync Framework will synchronice first the row on table Sales. Then the trigger on the second side will insert the row on SalesLanguage. Then SF will try to synchronice row from SalesTable. As this row exists on  destiny (trigger created it), conflict InsertInsert happens. Then RetryWithForceWrite is applied an I get infinite loop (conflict UpdateUpdate on SalesLanguage)

    jprieto
    Friday, February 25, 2011 8:17 AM
  • Jose, I dont understand a few things.

    You say no rows are inserted on the second side. Yes you mention insert-insert conflict occuring.

    What I thought happened was:

    1. On side1: Insert row into Sales

    2. On side1: Trigger executes and inserts row into SalesLang

    3. On side2: Insert a row into Sales

    4. On side2: Trigger executes and inserts row into SalesLang

    5. Sync - this sync created insert-insert conflict. Since you selected RetryWithForceWrite, sync up will update side2's Sales row with the value from side 1 now

    6. After this operation, trigger executes again and tries to insert row into SalesLang, but fails, because there is already a row in SalesLang with same PK. The conflict raised here is UpdateUpdate conflict.

    7. None of the data gets applied and you continued to see conflicts.

    Is this not the right things I got?

    If so, can you try to delete the trigger on side2 or update it so that before it inserts, it checks for the row existence and inserts only if not present?


    This posting is provided AS IS with no warranties, and confers no rights
    Friday, February 25, 2011 9:23 AM
  • Steps 3 and 4 you describe are not happening.

     

    Process is:

    1. On side1: Insert row into Sales

    2. On side1: Trigger executes and inserts row into SalesLang

    3. Sync Starts

    4. Sync insert a row into side 2 in table Sales

    5. Trigger on side 2 inserts a row into SalesLanguage

    6. Sync tries to insert a row into SalesLanguage (same row the trigger has already inserted in step 5)

    7. Conflict Insert-Insert

    8. RetryWithForceWrite applied on side 2.

    9. Infinite loop.

     

    Mahesh, in the real world I can’t remove the trigger. So my problem is not how to resolve a specific trigger (which I have modified it in the real scenario), the problem is what happened here and what can I do to ensure that this doesn’t happen again.

     

    P.D. During the infinite loop, syncframework's generated stored procedure ‘SalesLanguage_selectChanges’ was executed 225 million of times. That’s what I want to avoid.


    jprieto
    Friday, February 25, 2011 9:51 AM
  • I've learned two things working with Sync Framework and SQL that apply to this situation.

    First, triggers are a real problem.  You're going to dislike this, but as you see, if a trigger creates a new row due to a sync transaction, this can get very problematic.  In your case it creates an infinite loop for conflicts.  I could also see it bouncing back and forth adding more and more rows, depending on how the trigger was written.  In my situation the trigger caused a different conflict, but it totally destroyed our conflict prevention logic.

    Can you not modify the trigger logic to not insert a row in this situation?  Maybe because of some new field in Sales that indicates where the Sales row was created?  (IE, trigger only creates a new row when Sales row created on same system, but when sync'd and thus system is different, trigger does nothing).

    Otherwise, you're going to have a lot of difficulty getting the standard SQL sync provider to ignore these changes.

    The second item I'd point out to you is that conflict resolution CAN become an infinite loop.  There's no guarantee that when we specify RetryWithForceWrite it'll succeed, and if it doesn't it will fail and create another conflict.  If we keep forcing it and it keeps failing, that by definition is an infinite loop.  I had to find a way to detect rows I JUST processed in conflict resolution to break that loop.

    -Kevin

    Thursday, March 3, 2011 8:54 PM