Implicit transactions in SyncOrchestrator ? RRS feed

  • Question

  • Hi,

    I am using SQL Express on the client side and need to ensure atomicity for multiple tables. Cannot use SyncGroup/SyncAgent as  there is no SqlExpressClientSyncProvider in the SDK. Does SyncOrchestrator use transactions implicitly or we need to explicitly add that to the Connection/Session ?



    Friday, November 11, 2011 10:51 AM


  • the transaction is automatically handled by sync framework. for sqlsyncprovider/syncorchestrator, the transaction is at the scope level (the counterpart of a sync group)
    • Marked as answer by drkarkera Friday, November 11, 2011 11:19 AM
    Friday, November 11, 2011 10:58 AM

All replies

  • the transaction is automatically handled by sync framework. for sqlsyncprovider/syncorchestrator, the transaction is at the scope level (the counterpart of a sync group)
    • Marked as answer by drkarkera Friday, November 11, 2011 11:19 AM
    Friday, November 11, 2011 10:58 AM
  • Thanks JuneT



    Friday, November 11, 2011 11:19 AM
  • I understand that by default the Microsoft Sync Framework places each scope in it's own transaction.  However, is it possible to override that so that multiple scopes can be within the SAME transaction?

    I have a large database and in order to handle Insert-Insert conflicts we are needing to put all referenced tables in the same scope.  However, drilling this down so that all related tables are in the same scope forces us to basicly put all tables in the same scope which we do not want.  We've worked out a way to handle the insert-insert problem disperced over multiple scopes, however, because each scope is in it's own SQL transaction, if our 2nd or 3rd scope fails and rolls back, the changes from the previous scopes have already been commited.

    - Maz

    Thursday, March 1, 2012 10:15 PM
  • not that am aware of.  generally, if all tables need to commit as a single transaction, you should put them in the same scope. its not unusual though to separate reference tables in its own scope and sync them first.
    Thursday, March 1, 2012 11:17 PM
  • June thanks for your quick reply!

    I would like to put all tables in the same scope, however, the problem is that some of the tables would be filtered and some would not be filtered.  We could put together filtered scopes, but that would cause each row of the non-filtered tables to be associated with multiple scopes which sync framework doesn't deal with well.

    Currently we are looking at three scopes.

    1)  Download Only:  these are our reference tables

    2)  Upload/Download All:  These are our non-filtered tables

    3)  Upload/Download Client Only:  These are our filtered tables

    Where we run into problems is that tables in Scope 2 may have FK relationships with tables in Scope 3.  Because resolving insert-insert conflicts relies on FK relationships (i.e. setting the FK field to null then returning to the table after the FK table is updated and resetting the null field to the appropriate value), we may have to make updates based on values from tables in a different scope.  This would work just fine, however, if Scope 3 would happen to fail for some reason, all the changes from scope 1 and 2 have already been committed which results in corrupted data if we can't get Scope 3 to complete successfully.

    It's possible that I'm missing a simple solution and just not seeing it.  Is there a better way to do what we're trying to accomplish?   


    June, I found a blog you did back in 2010:


    Comments #7 and #8 identify part of my problem as well where a client may one day decide to filter on Field='A' then the next day decide to filter on Field='B', and then the third day want Field IN ('A','B').  If you happened to have found a solution to this, then I could put all tables in the same scope and the insert-insert resolution issue would be solved.

    I've copied two most relevent comments from your blog below so you wouldn't have to go look them up.

    <cite>Comment by Nick— April 9, 2010 @ 11:43 pm</cite>

    JuneI\’ve been looking into this further, and I don\’t see how this approach can work practically, or perhaps I mistake the intention of the filtering approach.  The problem centers around the sync_min_timestamp which is the taken as the max value of all timestamps that have been synchronized (by default and in your example above). What this means is that if you synchronize when some data row is filtered out, and then you change the filter parameter such that the data row should be included, it will only synchronize if the timestamp for that row in the side table is greater than the sync_min_timestamp, which will only occur if the value of that row has been updated since the last sync. It works in the example that you have above because you are only dealing with one table and consequently sync_min_timestamp is tied to that specific table.  When you introduce a second table, you will start running into problems.  The only way I can see to address this would be to have a separate sync_min_timestamp per table, which does not appear to be supported by the sync framework.

    <cite>Comment by June — April 10, 2010 @ 11:15 am</cite>

    nick,i dont actually follow your question regarding sync_min_timestamp or are you mistaking the max_timestamp query above for the sync_min_timestamp?the max_timestamp is completely different from the sync_min_timestamp, the latter not being based on a query but rather thru an inspection of the replica\’s knowledge. in fact, max_timestamp is optional. it\’s just an optimization option so as not to do the SelectChanges. And also, the max_timestamp checking is per table.the sample code is to demonstrate having a single scope definition cater to different synching clients with different filtering requirements.client A can use a filter of X, client B can use a Filter of Y, etc…if client A suddenly decides it wants a Filter of Y now, an issue will actually be on how to clean up the rows that were previously downloaded by Filter X.you\’re scenario below however holds thru. If assuming your Filter X gives you a minimum time stamp of 100. Then you suddenly change to Filter Y, however the rows or some of the rows satisying Filter Y has timestamps less than 100, then they wont be selected because when it asks what rows have i previously sent to the client, the client will reply back saying it has rows that has timestamp 100 and above. Sync would then simply determine if those previously sent rows has changed or if there were new rows added. It doesnt care about those rows whose timestamp is lower than 100. (We\’re talking of Incremental syncs here).

  • - Maz

  • Edited by Mazuric Friday, March 2, 2012 5:12 PM
Friday, March 2, 2012 4:01 PM
  • can you share some sample schema to help me understand it better and try to reproduce? also, sync framework ootb doesnt support dynamic filtering and partition realignment or rows going in and out of scope
    Monday, March 5, 2012 12:46 PM
  • You bet.  I'll put an example together with three tables which should explain the issue:

    Create Table Client (ID int identity Primary key,
                         ClientName varchar(50))

    Create Table Person (ID int identity Primary key,
                         ClientID int,
                         PersonName varchar(50),
            Foreign Key (ClientID) references Client(ID))

    Create Table Phone  (ID int identity Primary Key,
                         PersonID int,
                         PhoneNumber varchar(50),
            Foreign Key (PersonID) references Person(ID))

    Insert into Client (ID,ClientName) Values (1,'Client A')
    Insert into Client (ID,ClientName) Values (2,'Client B')

    Insert into Person (ID,ClientID,PersonName) Values (1,1,'Person A')
    Insert into Person (ID,ClientID,PersonName) Values (2,1,'Person B')
    Insert into Person (ID,ClientID,PersonName) Values (3,2,'Person C')
    Insert into Person (ID,ClientID,PersonName) Values (4,2,'Person D')

    Insert Into Phone (ID,PersonID,PhoneNumber) Values (1,'111-111-1111')
    Insert Into Phone (ID,PersonID,PhoneNumber) Values (2,'222-222-2222')
    Insert Into Phone (ID,PersonID,PhoneNumber) Values (3,'333-333-3333')
    Insert Into Phone (ID,PersonID,PhoneNumber) Values (4,'444-444-4444')


    Scope Upload/Download All:  Client and Phone
    Scope Upload/Download Client Only:  Person

    Filter on Person Table:  <FilterClause>[side].[ClientID] =1</FilterClause>

    Scope sync order:  1)  Upload/Download All   2)  Upload/Download Client Only


    Example Insert-Insert conflict:  A New person gets added to the database:

    Server Machine Insert
    Insert into Person (ID,ClientID,PersonName) Values (5,1,'Person E')
    Insert into Phone (ID,PersonID,PhoneNumber) Values (5,5,'555-555-5555')

    Client Machine Insert
    Insert into Person (ID,ClientID,PersonName) Values (5,1,'Person F')
    Insert into Phone (ID,PersonID,PhoneNumber) Values (5,5,'666-666-6666')

    Sync Process  (Sync from Client to Server)
    1)  First scope (ALL) runs trying to make make the following insert and causes a conflit:
        Insert into Phone (ID,PersonID,PhoneNumber) Values (5,5,'666-666-6666')

    2)  We would try to resolve the conflict by changing the ID from 5 to 6. So the insert statement becomes
        Insert into Phone (ID,PersonID,PhoneNumber) Values (6,5,'666-666-6666').  Also at this point the PersonID
        is pointing to the wrong Person Record pointing to "Person E" rather then "Person F".
        If you run this example in a database you'd have to SET IDENTITY_INSERT dbo.Phone ON

    3)  Because the Person table is out of scope we would not be able to resolve the corrupted record so we put the information into an in-memory
        conflict dictionary to be handled later.

    4)  Second scope runs inserts the Person record with a conflict because ID=5 already exists.

    5)  Conflict is resolved by setting ID=6
        If you run this example in a database you'd have to SET IDENTITY_INSERT dbo.Person ON

    6)  Code then goes through the in-memory conflict dictionary and find that the child table Phone had a data change (refer to step 2)

    7)  The following update is made to the Phone Record:
        Update Phone SET personid=6 where id=6

    As you can probably see this setup is a little precarious to say the least.  The biggest problem arises if the second scope (step 4) fails
    at some point in the process.  Any corrupted records (step 2) that were inserted from previous scopes have already been committed.

    We are NEEDING to limit the amout of data that ends up getting synced over to the Client Machine due to limited database space on those machines.
    Because of this putting all tables into the "Upload/Download All" scope is not an option with this approach. 

    Only alternitive we have found so far is to put all tables into one scope and limit the download to DATE instead of CLINIC.  But so far this doesn't
    look like it will reduce the database size far enough to make it work.

    As I mentioned in a previous post, we cannot do dynamic filtering on a single scope because a single client machine may randomly choose which
    client data it wants to download.

    Other then the obvious problem with this approach by purposely inserting corrupt data.  Can you explain a way that may help us out?

    - Maz

    • Edited by Mazuric Monday, March 5, 2012 4:21 PM
    Monday, March 5, 2012 4:18 PM
  • i think you're problem is actually in the choice of PK. by its nature, identity columns are problematic in synchronization applications.

    can you not change the PK to be compound key for Person to be Id+ClientId and for Phone to be Id+PersonId?

    if you can't change the PK (assuming its too late in the dev stage already), can you not add a guid column and "trick" Sync Framework to use it as a PK? have a look at this approach of tricking Sync Framework to use a different column for the PK: http://jtabadero.wordpress.com/2011/07/20/part-3-synchronizing-tables-where-client-and-server-primary-keys-are-different/

    you may want to look at these as well:



    Tuesday, March 6, 2012 10:14 AM
  • Unfortunatly modifying the PK is not an option.  We are dealing essentially a p2p system and the application code base on both ends of the sync are the same.  Thank you for the information you've given me!   Even if we can't get things to work perfect for this project at least we'll have our eyes open wider for the next.

    - Maz

    Tuesday, March 6, 2012 3:48 PM
  • June,

    I think we may have found a solution.  Takes a bit of coding, but it may work.  I'd like to run it by you to see what you think.

    The objective of this solution is to elimiate Insert-Insert conflicts by reseeding the IDENTITY value on the Primary Server.

    I'll only talk about and upload sync because that is where we have issues.  Downloads get handled a bit differently so we don't have the insert-insert problems that direction.

    Upload Side = The "Hub" database
    Download Side = The "Spoke" databases

    Proposed Solution
    Step 1)  Do inital download sync.
    Step 2)  Before any records are inserted on the Download side, record the current IDENT value
             of each table (IDENT_CURRENT('<table_name>')).  This data will be persisted in a
             seperate table and used later.

             EXAMPLE:  Table "People" has a current IDENT value of 5 

    Step 3)  Insert records on both the Upload and Download sides
    Step 4)  Again get all the current IDENT values for each table on the Download side and calculate
             The difference between the original value and the current value

             EXAMPLE Table "People" has current IDENT value of 7 and a previous IDENT value of 5 (value of 5 found in step 2)
                     The difference for table "People" is 2

    Step 5)  Send the difference data to the Upload side and increment the respective IDENT values by the corresponding difference values

             EXAMPLE:  Table "People" on the Upload side has a current IDENT value of 17 and my difference value is 2 (value of 2
                       found in step 4)
                       Reseed the IDENT value on table "People" to 19   (17+2=19)
    Step 6)  Track the skipped IDENT values for each table

      EXAMPLE: For table "People" the IDENT values that will be skipped are 18 and 19

    Step 7)  Send the Skipped IDENT value data back to the Download side

    Step 8)  Update the Download side database to reflect the new IDENT values (This is a recursive process that follows FK threads).

             EXAMPLE:  The first inserted record in table "People" had an IDENT value of 6. This value would be updated to the
                       first Skipped IDENT value on the Upload side wich is 18 (value of 18 found in Step 6)

    Step 9)  Preform Upload Sync

    Using this process the IDENT values of inserted records from table "People" on the download side get synced up to the upload side as just a NEW record rather then as an INSERT-INSERT conflict.

    This process would also require some locking of the database while the IDENT values were being discovered.

    What do you think of this solution?  Do you see any blaring problems?

    - Maz

    • Edited by Mazuric Wednesday, March 14, 2012 6:38 PM
    Wednesday, March 14, 2012 6:38 PM
  • updating values of PKs in Sync Framework is not supported.

    when you insert a new row a corresponding entry in the tracking table is inserted with the corresponding PK value.

    when you update the row, the update trigger will do a join between the base table and the tracking table. the join fails because the PKs are different. see this thread: http://social.msdn.microsoft.com/Forums/en-US/ssdsgetstarted/thread/7770faac-7511-4609-948a-f30ab297c01d. it's for SQL Azure Data Sync, but that service internally uses Sync Framework so it applies.

    can you not do identity ranges? client 1 has range 1-1000, client2 range 1001 to xxxxx....? see this approach: http://social.msdn.microsoft.com/Forums/en-US/ssdsgetstarted/thread/a61913f4-7dce-4199-a1b9-cae82bbc1a14

    again, i would still suggest adding a new column to your table to make the identity values unique. for example, a simple clientid+identity would make it unique and you dont have to change your PK. you can programmatically "trick" Sync Framework to use a different set of columns as its PK rather than using the actual PKs in the table.

    Thursday, March 15, 2012 2:01 AM
  • We can't do identity ranges because the number of new records that may occur is very unpredicable.  I see your point in that Sync Framework does not support updating the PK, and because my PKs are IDENTITY columns, I can't update it anyway.  So to get around these issues, I've built the process described in my previous post (with some modifications but essentially the same) outside of Sync Framework and eliminates all the insert-insert conflicts prior to sync framework being called. 

    I still have lots of testing to get through but so far things looks like they will work out.  By eliminating the insert-insert conflicts prior sync running, we should be able to put tables in whichever scope we choose regarless of FK relationships between the tables.

    Regardless of which scope a table goes into we'll have to make sure all of its parent tables have allready been processed.  This may cause us to have multiple "Clinic Only" and "All" scopes which must fire in a specific sequence, but that is easy business compared to dealing with the insert-insert problem within the Sync Framework.

    - Maz

    Friday, March 16, 2012 6:18 PM