locked
Syncronizing tables that have parent/child relationships defined RRS feed

  • Question

  • I'm working with an automagically generated master database that, when it creates parent/child relationships in the database, it creates a foreign key reference from the parent table to the child table.

     

    When I setup the Sync service, I have all tables that are in the syncronization to be synced as a single transaction.. this may be bad, but I have 25 tables that are all inter-dependent on each other... real world scenario.  I can break these up into two sets... call 'em customers and orders.

     

    Customers, when they're are new ones added in a client, they can sync from the client db to the server db without issue.  My problem starts when the orders are syncing.  In the orders section of the database, there's approximately 10-14 tables that are related.  What is happening is that I'm getting a conflict of keys during the inserts, even though this should all be going during the transaction, and there should be no way that the transaction is getting committed early.

     

    Any suggestions?

     

    I'm running the Sync Services for ADO.Net V2 framework.

    • Moved by Max Wang_1983 Friday, April 22, 2011 6:03 PM forum consolidation (From:SyncFx - Microsoft Sync Framework Database Providers [ReadOnly])
    Monday, August 11, 2008 5:50 AM

Answers

  • I believe this is referred to as a cross-reference or a circular reference.  Traditional approaches to providing the ability to perform DML operations against two tables with circular references are:

     

    1. Disable the FK constraints and re-enable after committing your DML operation
    2. Allowing nulls in your foreign key references

    Would either of these approaches work for you?

     

    Sean Kelley

    Microsoft

    Program Manager

     

    Friday, August 15, 2008 5:43 AM
    Moderator

All replies

  • Hello,

     

    Did you take care of the ordering of adding sync table objects to the sync agents?  In general parent tables should be added before the child table.

     

    Thanks.

    Monday, August 11, 2008 6:35 AM
    Answerer
  • yes.  They are in the respective order... such as

     

    Master -> details...

     

    but, with the way the db structure is setup, some of the master records have a 1-1 relationship with the detail records (maybe a tax record against an order), where when it is syncing, it feels like there is no transaction running, since its (the sync services api on the server side) saying that there are referential integrity errors.

     

    Is there any way of ensuring that a transaction is indeed being used when using a sync group?  Is there somewhere in code that I need to explicitly set "Make sure these are run in a transaction" so that I can avoid this issue?

     

    Monday, August 11, 2008 6:56 AM
  • 1. As long as "parent and child tables" are set in one SyncGroup object, and parent table is added before the child table, we should not have referential integrity issues.

    2. Can you capture what referential integrity error the sync service complains?  Please turn on SQL profiler if necessary.

    3. If you really likes to control the transaction, you can try

     

    Adding event handler to the server provider

    serverProvider.SelectingChanges += new EventHandler<SelectingChangesEventArgs>(serverProvider_SelectingChanges);

    serverProvider.ChangesSelected += new EventHandler<ChangesSelectedEventArgs>(serverProvider_ChangesSelected);

     

    define, create transaction and commit it.

    static IDbTransaction tranOCSsyncSession;

    static void serverProvider_ChangesSelected(object sender, ChangesSelectedEventArgs e)

    {

    e.Transaction.Commit();

    }

    static void serverProvider_SelectingChanges(object sender, SelectingChangesEventArgs e)

    {

    tranOCSsyncSession = e.Connection.BeginTransaction(IsolationLevel.ReadCommitted);

    e.Transaction = tranOCSsyncSession;

    }

     

    Please note: these code snippt is just for demo purpose only.

     

    Still I am interested to see the referential integrity error the sync service complains from the server side.

     

    Thanks.

    Monday, August 11, 2008 7:37 AM
    Answerer
  • here's what I can get you...

     

    the e.Conflict.ConflictType enumeration was set as "ErrorsOccurred"

     

    the Message in e.Error.Message is:

    ? e.Error.Message

    "The INSERT statement conflicted with the FOREIGN KEY constraint \"FK_DealDocument_DealDocumentTaxId\". The conflict occurred in database \"dp_HomeOffice\", table \"dbo.DealDocumentTax\", column 'Oid'.\r\nThe statement has been terminated."

     

    In this scenario, the DealDocument table has a column called "DealDocumentTaxId" which is against a table called DealDocumentTax.  In the DealDocumentTax table, there is a column called DealDocumentId which is a foreign key referenced in DealDocument.

     

    In my history of doing database programming, the only time where I ever had an issue like this was when there was no transaction being run over a set of insert/update/delete queries.

     

    Any help would be appreciated.

     

    Thanks.

    Monday, August 11, 2008 7:44 AM
  • Hello -

     

    Now I see that the error happened on the upload phase.

     

    From the error message, I can guess

    DealDocumentTax should be the parent table with its PK oid.

    DealDocument should be the child table with a FK DealDocumentTaxId referencing to DealDocumentTax.

     

    From your description,

    DealDocumentTax also reference to DealDocument using DealDocumentId.

     

    Do DealDocumentTax and DealDocument both have foreign keys referencing to the other table?

     

    Thanks.

     

    Monday, August 11, 2008 8:34 AM
    Answerer
  • In the way that the system was designed, DealDocumentTax is a child of DealDocument, as DealDocument is the master table in the relation.

     

    to answer "Do DealDocumentTax and DealDocument both have foreign keys referencing the other table?"...

    Yes.  The way that DevExpress eXpress Persistence Objects works for a 1 <==> 1 scenario, you must have a cross reference betweent the two tables.  When the database is generated, it creates a reference from the master (DealDocument) to the child (DealDocumentTax) tables.

    I had been in discussions with DevExpress' team for a few days about if/when they were going to look into N-Tier support of their suite, to which they were not planning, and if I had wanted such support (which is necessary for my business problem), I would need to come up with a work-around solution on my own.

    I'm beginning to consider abandoning their technology for another OR/M suite, such as Entity Framework due to this fact.

    I love their UI controls, but their idea of OR/M is limited to single-user applications or multi-user connected applications, and not occasionally connected systems.  Several users on their forums have switched because of this limitation, and according to them, have had a different learning curve, but have not looked back.

     

    My goal is to try to get this last hurdle resolved by no later than Wednesday, so that I can prepare an Alpha release of the latest revision for Friday.  I had a proprietary sync method written using a DTO model, and although it worked, It was becoming tedious to maintain.

     

    As of next Monday, we have a demo of the next release that I do not wish to cancel or postpone... hence the urgency and the late nights (I'm in EDT here) working on trying to understand the tidbits of the Sync Framework and getting it to run smooth.

     

    I did, as a test, remove the foreign key relations across the database for all DealDocument* tables, and the sync worked perfectly, so I am not sure whether or not the next release should be using full foreign key relations.

     

    As of now, the system has at least 3 relations the same as I have documented above.  for Tax, Shipping Address table, and Billing Address table (bad design, but didn't take the time yet to clean that part up for the "address" tables).  One step at a time Wink

     

    Thanks.

    Monday, August 11, 2008 9:05 AM
  • Hello,

     

    Be design, Sync Service does not support the circular PK/PK references.

    By the way, does this kind of PK/FK circular reference also persisted on the client database as well?  ( I guess not, otherwise the same INSERT will fail on the client also.)

     

    Thanks

     

    Monday, August 11, 2008 8:44 PM
    Answerer
  • Hello,

     

    One thing you can try to work around this issue - assume you just have this kind of FK INSERT.

    1. Create a temp dataset object globally.

    2. When ApplyChangeFailed() happens on the server provider side, capture this failed row and add it to the temp dataset object.

    3. When ChangesApplied() happens on the server privider side, process the data rows from temp dataset again, (try INSERT again).

     

    Thanks.

    Monday, August 11, 2008 10:07 PM
    Answerer
  • The inserts do not fail on the client when using the ClientUI.  I have not gotten to the point where bidirectional syncronization has been used.  Our product has been in development for about 8 months at this point without issue, and without having to remove any of the structure.

     

    This is bad form for the Sync Services framework, since you are telling me that, in essence, you are not running the sql statements in a transaction.  A PK/PK insert in a transaction is completely acceptable, and does not fail.  That is the methodology of how DevExpress is handling the PK/PK reference.

     

    Beginning to wonder if adopting a new technology was the right way to go... Guess it's time to start dismantling the relationships in the database.

     

    This should be addressed for V3 of the Sync Services for ADO.Net, as it allows orphan records to be created.

    Tuesday, August 12, 2008 4:22 AM
  • It's a work-around, but not a solution.  Will see if there is an alternate method to handle this scenario.

    Tuesday, August 12, 2008 4:23 AM
  • Hello,

     

    I have tried to do the "A PK/PK insert in a transaction is completely acceptable, and does not fail".

     

    Here comes my test code using TSQL. 

     

    2 tables, [parent] and [child].  Initially [child] references to [parent].  then on [parent] add a FK references to [child].  now we have a circular PK/FK reference between [parent] and [child]

     

    create table parent (pid int primary key, fk int, c2 int)

    create table child (cid int primary key, fk int references parent(pid), c2 int)

    alter table parent add constraint fk_parent_child foreign key (fk) references child(cid)

     

    begin tran

    insert into parent values (2, 1, 1)

    insert into child values (1, 2, 1)

    commit tran

     

    I am seeing failures in execution.  What might be the difference between the INSERT statement I have and the one working on your side?

     

    Msg 547, Level 16, State 0, Line 2

    The INSERT statement conflicted with the FOREIGN KEY constraint "fk_parent_child". The conflict occurred in database "master", table "dbo.child", column 'cid'.

    The statement has been terminated.

    Msg 547, Level 16, State 0, Line 3

    The INSERT statement conflicted with the FOREIGN KEY constraint "FK__child__fk__5772F790". The conflict occurred in database "master", table "dbo.parent", column 'pid'.

    The statement has been terminated.

     

     

    Wednesday, August 13, 2008 5:13 AM
    Answerer
  • I believe this is referred to as a cross-reference or a circular reference.  Traditional approaches to providing the ability to perform DML operations against two tables with circular references are:

     

    1. Disable the FK constraints and re-enable after committing your DML operation
    2. Allowing nulls in your foreign key references

    Would either of these approaches work for you?

     

    Sean Kelley

    Microsoft

    Program Manager

     

    Friday, August 15, 2008 5:43 AM
    Moderator
  • L Zhou...

     

    Haven't been following, as I've been working dilligently on other parts of the system.  As I said, it had been a workaround, and for now, will work.  I went back into the system, and realized that you are indeed right on this.  the FK/FK direct insert does not work.  I believe that I was thinking a PK->FK insert... too many hours in front of the computer Wink

     

    Since I will be enabling batching into the system for the next release, I cannot really have the fk constraint anyhow from my understanding.  So the point is null & void.

     

    Tuesday, August 26, 2008 6:05 AM
  • Option #2 doesn't seem like a valid operation, as it requires much more management of data outside of the sync services... meaning it sounds like I need to keep a list of child/parent relationships that I do not want to deal with.

     

    Option #1 is going to be the preferred workaround until more time is available to understand further the sync framework and how to better implement more of the core portions of the framework.

     

     

     

    Hindsight being 20/20, I would probably have re-designed this dependency to avert the use of the circular reference, as it makes development tedious and difficult to maintain.

    Tuesday, August 26, 2008 6:09 AM