locked
SyncService and transactions RRS feed

  • Question

  • Hello,

    i am using the SyncService to synchronize to firebrid databases.
    Before i can sync my datas i must do some changes in my databases. After the sync i will revoke these changes.
    These changes and the synchronisation are suppossed to be in one transaction.

    My plan is also:

    connection.BeginTransaction()
         makeChanges()
         Synchronize()
         revokeChanges()
    CommitTransaction()

    How can i solve this problem? I don't know how to use transactions with the Syncframework.
    For example i can start a transaction in my ServerSyncProvider, and deliver the transaction to any Syncadapter. But where is the point i can commit the transaction?

    best regards


    Boris
    • Moved by Hengzhe Li Friday, April 22, 2011 3:29 AM (From:SyncFx - Microsoft Sync Framework Database Providers [ReadOnly])
    Thursday, March 12, 2009 2:20 PM

Answers

  • here are a couple guidelines for such cases:

    to deal with complext db schema, use the table ordering feature: the sync apply changes on the tables in a group with the order that the tables were added to the syncAdapter for insert/update, and the reversed order for deletes. in such case, if you can carefuly add tables by following the RI among your tables, you can handle the majority of cases with complex db schema. hope you have already tried this.

    try to let the sync to be done with its own connection. my assumption your interup the connection used by the sync is to deal with issues associated with the complext RI. if probably ordering the table cannot resolve it ( or db schema is too complex to order the tables ), you may want to put your own logic to cache the failed record and do retries in your own code. the events ( applyChanges, ChangesApplied and ApplyChangeFailed exposes the dbconnection and dbtransaction you can hook up with your own logic ).

    regarding with "2. a way that just complete synchronizations are commited to our database", you can simply abort the sync if there is any errors ( in the applyChangeFailed event ) based on your need by throw application level exception.

    the above are only guidelines for such complicate scenaros. as you have already noticed, sync provide such flexible ways to allow users to control the flow however, users logic/code could be big and should be well-designed.

    I would be very interested to see if table ordering can help in this case.

    thanks
    Yunwen


    This posting is provided "AS IS" with no warranties, and confers no rights.
    Sunday, March 15, 2009 9:59 PM
    Moderator

All replies

  • there are a couple of points you need to consider before doing this:

    1. what will realy achieve by doing this? if you want interact the synchronize(), you can try to use the eventing provided by the sync services.
    2. Synchronize() could possibly have mutiple transactions ( each sync group has its own transaction ). with this scenario, your approach will not work.

    Could you elabrate your case with some detials so we can discuss this further more ?

    thanks
    Yunwen
    This posting is provided "AS IS" with no warranties, and confers no rights.
    Thursday, March 12, 2009 8:12 PM
    Moderator
  • Hello,

    there a two reasons why we want to do this.

    1. We have a realy complex architecture in our database. So we can't find a way to order these tables to synchronize them.
    By this reason we want to drop all foreignkeys before synchronize. After synchronize we want to add all foreignkeys back to database. I another case, nothing to do with snyc, we do the same and it works.
    Important is to do this changes in a transaction, that first no one other can see this and when an error occures we have the option to rollback.

    2. The synchronization will be added to an application. The SyncProcess will run in background of this application. The application and the SyncFramework will work parallel on the same database.
    In case that the connection will be interrupted during synchronization, we get an inconsistent database.

    For example:

    We have 10 tables
    We start synchronization
    Now Connection will be interrupted
    Just 5 Tables are synced

    Now the application has an inconsitent database.
    When we put the synchronization in a transaction, we can rollback in case of connection interrupted.


    For the second reason we have think about to put all tables in one SyncGroup, would this solve our problem with inconsistence database by connection interrupts?

    So the important thinks we need:

    1. a way to synchronize our complex database
    2. a way that just complete synchronizations are commited to our database


    Thanks for your help

    best regards



    Boris
    Friday, March 13, 2009 7:58 AM
  • here are a couple guidelines for such cases:

    to deal with complext db schema, use the table ordering feature: the sync apply changes on the tables in a group with the order that the tables were added to the syncAdapter for insert/update, and the reversed order for deletes. in such case, if you can carefuly add tables by following the RI among your tables, you can handle the majority of cases with complex db schema. hope you have already tried this.

    try to let the sync to be done with its own connection. my assumption your interup the connection used by the sync is to deal with issues associated with the complext RI. if probably ordering the table cannot resolve it ( or db schema is too complex to order the tables ), you may want to put your own logic to cache the failed record and do retries in your own code. the events ( applyChanges, ChangesApplied and ApplyChangeFailed exposes the dbconnection and dbtransaction you can hook up with your own logic ).

    regarding with "2. a way that just complete synchronizations are commited to our database", you can simply abort the sync if there is any errors ( in the applyChangeFailed event ) based on your need by throw application level exception.

    the above are only guidelines for such complicate scenaros. as you have already noticed, sync provide such flexible ways to allow users to control the flow however, users logic/code could be big and should be well-designed.

    I would be very interested to see if table ordering can help in this case.

    thanks
    Yunwen


    This posting is provided "AS IS" with no warranties, and confers no rights.
    Sunday, March 15, 2009 9:59 PM
    Moderator
  • Hello,

    after a redesign and ordering our tables, we have synced some datas. :)
    We have still problems with new data, but i hope we can fix it. When it works, or i have some questions i will answer here.


    But i just have a new Question.
    We are using two firebird Databases in our project. Cause of this we have fit this example http://code.msdn.microsoft.com/Release/ProjectReleases.aspx?ProjectName=sync&ReleaseId=1200 to firebird databases.

    Is there a way to activate batching from client to server syncronisation? In normal cases i just goes from server to client. But in our case our ClientProvider has a ServerProviderobject. When we use the batchsize from this object, does batching works from client to server?


    thanks

    Boris
    Tuesday, March 17, 2009 4:21 PM
  • Good to know it starts working.

    there is no batching support for upload in the current sync service release. you will need more frequent syncs to the server if your clients have relatively big amount of data to sync to server. we are working on the next release and batching will be supported for both upload and downlaod.

    thanks
    Yunwen
    This posting is provided "AS IS" with no warranties, and confers no rights.
    Tuesday, March 17, 2009 7:49 PM
    Moderator
  • Hello,

    is there a date, when the release will come out, yet?


    And another problem from our site.
    We can sync our data between the two databases. It works, but we have some errors and don't know where they occur.
    We catch these errors in the ApplyChangedFailed-event:

    violation of PRIMARY or UNIQUE KEY constraint "PK_Customer" on table "Customer"
    At procedure 'SP_INSERT_CUSTOMER'

    This error comes for every record, when we want to sync. It doesn't matter whether the record must be sync or if the record is up to date and must not be sync.
    I think the SyncFramework doesn't know if our records are up to date or not and try to execute the sqlstatements (or the stored procedures).

    Can it be, that our anchors are not right? I try to debug the syncprocess and look which timestamp is in lastreceived_anchor, but there is just a byte[], where i can't see the time.

    Or can it be caused by another position?

    It is really hard to find such errors. Do you have some tips, how i can better debug the syncProcess? It would be great to see how our Sql-Statements looks, when they will be send to the database. Or which DateTime is in the anchors.



    Thanks for your great help


    best regards


    Boris

    Wednesday, March 18, 2009 4:35 PM
  • I'm interested in BatchSize for upload as well...  Do you have a release date planed yet?

    Jason
    Friday, April 3, 2009 6:14 AM