locked
DbSyncProvider logic and execution sequence RRS feed

  • Question

  • While executing syncronize.  It does seems it applies all deletes then inserts and updates.  This offen break into constraints in the following situation

    Table1

    ID

    1

    2

    3

     

    Table2

    NAME ID

    A       1

    B       2

     

     

    Table2.Id has reference to Table1.Id some reason If Table1.Id (value 2 is required to be deleted then will assign back to 3 before deleting 2 just to avoid constraints) .  But while syncing it applies to delete first resulting the constraint issue.

     

    I think executing the order of rowversion would solve the problem(in this case the update runs first then delete), am i missing anything?

     

     

    • Moved by Tina_Tian Friday, April 22, 2011 7:57 AM (From:SyncFx - Microsoft Sync Framework Database Providers [ReadOnly])
    Wednesday, October 15, 2008 7:13 PM

All replies

  • Udai,

    Yes we always apply deletes before applying inserts and updates.But we apply deletes in the reverse order to ensure we apply all child table FK deletes befre applying parent PK deletes. The order in which changes are applies is inferred from the SyncAdapters collection on your provider. You have to ensure that you add the parent table to that list before adding the child table.As long as you ensure that parent deletes are sent in the same sync session as the child FK delete and your adapters are configured in the right order, deletes should work.

     

    Post back if this is not the case and you are seeing something different

     

    Friday, October 17, 2008 3:40 AM
    Moderator
  • Hi Mahjayar,

    please refer the following script.  Note this is not actual table we use just to re-create the problem you can run the following script.

     

    IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[childrentbl]') AND type in (N'U'))

    DROP TABLE [dbo].[childrentbl]

    GO

    IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[parenttbl]') AND type in (N'U'))

    DROP TABLE [dbo].[parenttbl]

    GO

    create table [dbo].[parenttbl](parentid int primary key clustered,parentname nvarchar(50))

    go

    create table [dbo].[childrentbl](childid int, parentid int,

    constraint pk_ct primary key nonclustered (childid,parentid),

    constraint [fk_ct] foreign key ([parentid]) references [dbo].[parenttbl] ([parentid])

    )

    go

    insert into parenttbl(parentid,parentname)values(1,'data1')

    go

    insert into parenttbl(parentid,parentname)values(2,'data2')

    go

    insert into childrentbl(childid,parentid)values(1,2--parentid=2 assigned to children 1

    go

    --Now peform sync to make sure both sides are equal

    --Now i want to delete parentid=2 from table1 i knew that has reference to childrentbl, so before i delete i

    --make the childrentbl entries to have (1,1)

    update childrentbl set parentid=1 where parentid=--chainging to parentid=1 for the children 1

    go

    delete from parenttbl where parentid=2 --delete parentid=2

    go

    --Now if i sync to other side it is going to apply delete first in this case

    --there is no delete from childrentbl but there is a delete in parenttbl trying to apply

    --the parent tbl delete will cause the children tbl to have constraint violation on other side (The developer updated the childrentbl before delete from the parenttbl).  This is where i prefer applying sync based on the rownumber instead of delete first. 

     

    Please let me know if i missed anything.

     

    Thanks.

     

    Monday, October 20, 2008 1:46 PM
  • Udai,

     

    Mahesh asked you to post your app logic used to add SyncAdapters to the SynyAdapter collection on your server provider.  As he points out, the order in which those adapters are added defines the order in which changes are applied and must satisfy any FK constraints that you have added to your tables.  An example of this logic is show below:

     

    Code Snippet
    //Create a SyncAdapter for the Customer table, and then define
                //the commands to synchronize changes:
                /* SelectIncrementalInsertsCommand, SelectIncrementalUpdatesCommand,*/
                //  and SelectIncrementalDeletesCommand are used to select changes
                //  from the server that the client provider then applies to the client.
                /* InsertCommand, UpdateCommand, and DeleteCommand are used to apply*/
                //  to the server the changes that the client provider has selected
                //  from the client.

                //Create the SyncAdapter.
                SyncAdapter customerSyncAdapter = new SyncAdapter("Customer");           
               
                //Select inserts from the server.
                SqlCommand customerIncrInserts = new SqlCommand();
                customerIncrInserts.CommandText = 
                    "SELECT CustomerId, CustomerName, SalesPerson, CustomerType " +
                    "FROM Sales.Customer " +
                    "WHERE (InsertTimestamp > @sync_last_received_anchor " +
                    "AND InsertTimestamp <= @sync_new_received_anchor " +
                    "AND InsertId <> @sync_client_id)";
                customerIncrInserts.Parameters.Add("@" + SyncSession.SyncLastReceivedAnchor, SqlDbType.Timestamp);
                customerIncrInserts.Parameters.Add("@" + SyncSession.SyncNewReceivedAnchor, SqlDbType.Timestamp);
                customerIncrInserts.Parameters.Add("@" + SyncSession.SyncClientId, SqlDbType.UniqueIdentifier);
                customerIncrInserts.Connection = serverConn;
                customerSyncAdapter.SelectIncrementalInsertsCommand = customerIncrInserts;

                //Apply inserts to the server.
                SqlCommand customerInserts = new SqlCommand();
                customerInserts.CommandText =
                    "INSERT INTO Sales.Customer (CustomerId, CustomerName, SalesPerson, CustomerType, InsertId, UpdateId) " +
                    "VALUES (@CustomerId, @CustomerName, @SalesPerson, @CustomerType, @sync_client_id, @sync_client_id) " +
                    "SET @sync_row_count = @@rowcount";
                customerInserts.Parameters.Add("@CustomerId", SqlDbType.UniqueIdentifier);
                customerInserts.Parameters.Add("@CustomerName", SqlDbType.NVarChar);
                customerInserts.Parameters.Add("@SalesPerson", SqlDbType.NVarChar);
                customerInserts.Parameters.Add("@CustomerType", SqlDbType.NVarChar);
                customerInserts.Parameters.Add("@" + SyncSession.SyncClientId, SqlDbType.UniqueIdentifier);
                customerInserts.Parameters.Add("@" + SyncSession.SyncRowCount, SqlDbType.Int);
                customerInserts.Connection = serverConn;
                customerSyncAdapter.InsertCommand = customerInserts;
                                       
               
                //Select updates from the server.
                SqlCommand customerIncrUpdates = new SqlCommand();
                customerIncrUpdates.CommandText =
                    "SELECT CustomerId, CustomerName, SalesPerson, CustomerType " +
                    "FROM Sales.Customer " +
                    "WHERE (UpdateTimestamp > @sync_last_received_anchor " +
                    "AND UpdateTimestamp <= @sync_new_received_anchor " +
                    "AND UpdateId <> @sync_client_id " +
                    "AND NOT (InsertTimestamp > @sync_last_received_anchor " +
                    "AND InsertId <> @sync_client_id))";
                customerIncrUpdates.Parameters.Add("@" + SyncSession.SyncLastReceivedAnchor, SqlDbType.Timestamp);
                customerIncrUpdates.Parameters.Add("@" + SyncSession.SyncNewReceivedAnchor, SqlDbType.Timestamp);
                customerIncrUpdates.Parameters.Add("@" + SyncSession.SyncClientId, SqlDbType.UniqueIdentifier);
                customerIncrUpdates.Connection = serverConn;
                customerSyncAdapter.SelectIncrementalUpdatesCommand = customerIncrUpdates;
               
                //Apply updates to the server.
                SqlCommand customerUpdates = new SqlCommand();
                customerUpdates.CommandText =
                    "UPDATE Sales.Customer SET " +
                    "CustomerName = @CustomerName, SalesPerson = @SalesPerson, " +
                    "CustomerType = @CustomerType, " +
                    "UpdateId = @sync_client_id " +          
                    "WHERE (CustomerId = @CustomerId) " +
                    "AND (@sync_force_write = 1 " +
                    "OR (UpdateTimestamp <= @sync_last_received_anchor " +
                    "OR UpdateId = @sync_client_id)) " +
                    "SET @sync_row_count = @@rowcount";
                customerUpdates.Parameters.Add("@CustomerName", SqlDbType.NVarChar);
                customerUpdates.Parameters.Add("@SalesPerson", SqlDbType.NVarChar);
                customerUpdates.Parameters.Add("@CustomerType", SqlDbType.NVarChar);
                customerUpdates.Parameters.Add("@" + SyncSession.SyncClientId, SqlDbType.UniqueIdentifier);
                customerUpdates.Parameters.Add("@CustomerId", SqlDbType.UniqueIdentifier);
                customerUpdates.Parameters.Add("@" + SyncSession.SyncForceWrite, SqlDbType.Bit);
                customerUpdates.Parameters.Add("@" + SyncSession.SyncLastReceivedAnchor, SqlDbType.Timestamp);
                customerUpdates.Parameters.Add("@" + SyncSession.SyncRowCount, SqlDbType.Int);
                customerUpdates.Connection = serverConn;
                customerSyncAdapter.UpdateCommand = customerUpdates;


                //Select deletes from the server.
                SqlCommand customerIncrDeletes = new SqlCommand();
                customerIncrDeletes.CommandText =
                    "SELECT CustomerId, CustomerName, SalesPerson, CustomerType " +
                    "FROM Sales.Customer_Tombstone " +
                    "WHERE (@sync_initialized = 1 " +
                    "AND DeleteTimestamp > @sync_last_received_anchor " +
                    "AND DeleteTimestamp <= @sync_new_received_anchor " +
                    "AND DeleteId <> @sync_client_id)";
                customerIncrDeletes.Parameters.Add("@" + SyncSession.SyncInitialized, SqlDbType.Bit);
                customerIncrDeletes.Parameters.Add("@" + SyncSession.SyncLastReceivedAnchor, SqlDbType.Timestamp);
                customerIncrDeletes.Parameters.Add("@" + SyncSession.SyncNewReceivedAnchor, SqlDbType.Timestamp);
                customerIncrDeletes.Parameters.Add("@" + SyncSession.SyncClientId, SqlDbType.UniqueIdentifier);
                customerIncrDeletes.Connection = serverConn;
                customerSyncAdapter.SelectIncrementalDeletesCommand = customerIncrDeletes;

                //Apply deletes to the server.           
                SqlCommand customerDeletes = new SqlCommand();
                customerDeletes.CommandText =
                    "DELETE FROM Sales.Customer " +
                    "WHERE (CustomerId = @CustomerId) " +
                    "AND (@sync_force_write = 1 " +
                    "OR (UpdateTimestamp <= @sync_last_received_anchor " +
                    "OR UpdateId = @sync_client_id)) " +
                    "SET @sync_row_count = @@rowcount " +
                    "IF (@sync_row_count > 0)  BEGIN " +
                    "UPDATE Sales.Customer_Tombstone " +
                    "SET DeleteId = @sync_client_id " +
                    "WHERE (CustomerId = @CustomerId) " +
                    "END";
                customerDeletes.Parameters.Add("@CustomerId", SqlDbType.UniqueIdentifier);      
                customerDeletes.Parameters.Add("@" + SyncSession.SyncForceWrite, SqlDbType.Bit);
                customerDeletes.Parameters.Add("@" + SyncSession.SyncLastReceivedAnchor, SqlDbType.Timestamp);
                customerDeletes.Parameters.Add("@" + SyncSession.SyncClientId, SqlDbType.UniqueIdentifier);
                customerDeletes.Parameters.Add("@" + SyncSession.SyncRowCount, SqlDbType.Int);          
                customerDeletes.Connection = serverConn;
                customerSyncAdapter.DeleteCommand = customerDeletes;    


                //Add the SyncAdapter to the server synchronization provider.
                this.SyncAdapters.Add(customerSyncAdapter);

     

     

     

    Sean Kelley

    Program Manger

    Microsoft

    Tuesday, October 21, 2008 3:02 AM
    Moderator
  •  

    Hi Sean,

    Thanks for the reply.  Here is the snippet how am adding into collection

    DbSyncProvider provider = new DbSyncProvider();

    provider.SyncAdapters.Add(GetAdapter("parenttbl")); //Parenttbl

    provider.SyncAdapters.Add(GetAdapter("childrentbl"));//childrentbl

     

    I beleive you still missing my point.  Deleting records is not a problem.  we are not deleting the children record instead change the parentid and then delete the parentid from parenttbl (Note:  there is NO delete in childrentbl just update to some other parentid).  While syncing to other side the delete is going to happen first (so it will apply delete for parenttbl)but the acutual children table has the reference this is very unique situation.  Let me know if you need sample project to repro this.

     

    PS: This is peer-peer fashion sync

     

    Thanks & Regards,

    Udai.

    Wednesday, October 22, 2008 1:16 PM
  • Ah I see. This is not possible unless you break the FK change + PK delete in two separate sync sessions. i.e change FK row->sync and then delete PK row and sync.

     

    One more thing that you can try is to add a cascade delete clause on your PK. So this way when we first apply the PK delete it will delete the FK entry for you and add an tombstone entry for that FK. Then later runtime will apply your original FK update and fail (as the FK row has been deleted) and so the runtime will retry that update as an Insert and succeed. Your creation versions and update versions will continue to be correct but you will have to make sure your that your Insert/Update metadata queries correctly flips the Sync_IsTombstone bit so that it resurrects your tombstone entry for the FK row.

    Thursday, October 23, 2008 3:34 PM
    Moderator
  • One more solution is to do nothing and MSF would ensure that the PK delete would be applied the next time you sync. you see we hold negative exceptions in knowledges for items that werent applied during a sync session. During the next sync those individual items would be sent again by the source. So in your case, the first sync would fail the PK delete but apply the FK update. Then on the second sync your PK delete is resent and successfully applied. Does that work for you?

    Thursday, October 23, 2008 6:37 PM
    Moderator
  • Hi Makesh,

    Thanks for the reply.  Yep, it will sync on second time.  But the customers will not be happy if i ask them to do 2nd time, so i made workaround for now. 

     

    Is there any chance that future sync release will take care of this kind of issue?  atleast interanlly it will reapply (just ONE extra try after the actual apply is done and error occured) the rows marked for retry due to error will be fine too.

     

    I would still think there seems to be possibilities to sort out based on rowversion and applying in ascending order in comparison level.

     

    Thanks.

     

    Friday, October 24, 2008 4:40 PM