locked
Is the transaction based on scope level or table level? RRS feed

  • Question

  • Hello all,

    I would like to know if the sync transaction is based on the scope level or table level?

    So I tested this case in my own sync project,

    There are two tables (“Product”, “ProductSale”) in the scope

    BEGIN TRAN

    SELECT * FROM ProductSale with (holdlock)

    --commit

    Insert the new data into these two tables,

    Run the SP before sync happened,

    Result: the changes for the table “Product” have been sync, the changes for the table “ProductSale” have not been sync.

    So it seems that the transaction is not based on the scope level

    But how can I set the transaction based on the scope level

    Thanks,

    Abby


    Get reply from social.microsoft

    Wednesday, June 13, 2012 8:57 AM

All replies

  • its at the scope level.
    Wednesday, June 13, 2012 9:50 AM
  • But from the testing result, it is not at the scope level..


    Get reply from social.microsoft

    Thursday, June 14, 2012 3:04 AM
  • you issued a holdlock on the table within a transaction, your inserts will not go thru until you close that transaction...

    so when sync run while you have your holdlock within a transaction, the inserts hasnt gone thru, therefore they cant be selected

    to test it:

    on one query window run this:

    BEGIN TRAN

    SELECT * FROM ProductSale with (holdlock)

    on another query window, do an insert to ProductSale, check if it goes thru

    on another query window do a SELECT * FROM ProductSale and see if what you inserted is there


    • Edited by JuneT Thursday, June 14, 2012 3:53 AM
    Thursday, June 14, 2012 3:51 AM
  • There is a case happened in our system, a record was not synced to the other notes, the other records are ok to sync to the other notes.

    How can I solve this case?

    The exception message:

    5:01:45 PM 421       DataSyncCenter Information: 0 : 5:01:45 PM 421       ErrorsOccurred
    5:01:45 PM 421       DateTime=2012-05-23T09:01:45.4218750Z
    5:01:45 PM 421       DataSyncCenter Information: 0 : 5:01:45 PM 421       Data Conflict detected at ErrorsOccurred, Type: Data Source=POS101\SQLEXPRESS,1180;Initial Catalog=Wayne LS POS V1.03;User ID=sa;, Scope: TransactionBusinessData
    5:01:45 PM 421       DateTime=2012-05-23T09:01:45.4218750Z
    5:01:45 PM 421       DataSyncCenter Information: 0 : 5:01:45 PM 421       An error occurred during synchronization.
    Error source: .Net SqlClient Data Provider
    Error message: Timeout expired.  The timeout period elapsed prior to completion of the operation or the server is not responding.

    The sync inner trace:

    5:49:28 PM 828       ERROR  , ROWPOS.ConnectionPoints.DataCenterP2P, 11, 05/23/2012 09:49:28:828, Error during InsertCommand: System.Data.SqlClient.SqlException: Timeout expired.  The timeout period elapsed prior to completion of the operation or the server is not responding.
       at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
       at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
       at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
       at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
       at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
       at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)
       at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)
       at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe)
       at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
       at Microsoft.Synchronization.Data.DbChangeHandler.ExecuteCommandReturnRowcount(IDbCommand command, Boolean& found)
       at Microsoft.Synchronization.Data.DbChangeHandler.ExecuteChangeApplicationCommandBase(IDbCommand command, SyncRowMetadata rowMetadata, String commandName)
    5:49:28 PM 843       WARNING, ROWPOS.ConnectionPoints.DataCenterP2P, 11, 05/23/2012 09:49:28:843, Conflict detected with error: Timeout expired.  The timeout period elapsed prior to completion of the operation or the server is not responding.

    Regards,

    Abby


    Get reply from social.microsoft

    Thursday, June 14, 2012 4:45 AM
  • two ways you can address it:

    1. set the CommandTimeout to a higher value

    2. subscribe to the ApplyChangeFailed event, check for the error and set the resolution to retry

    Thursday, June 14, 2012 5:30 AM
  • Thanks a lot for your apply,

    We don't want to increase the CommandTimeout value, and the retry is ok to us,

    But from the case, but why the transaction is not rollback, why some of data are synced to the other parts.

    And from the test I did:

    BEGIN TRAN

    SELECT * FROM ProductSale with (holdlock)

    From the result: we still can see the transaction is not based on the scope

    Regards,

    Abby


    Get reply from social.microsoft

    Thursday, June 14, 2012 5:37 AM
  • in your sample, you have two tables, you issued a lock on one table, the other has no lock, therefore the other tables changes will be synched.

    you insert rows on both product and productsale, the insert on product will go thru, the inserts on productsale will not until the transaction is closed...

    when sync fx fires it queries, it will find the inserted rows for product because they have been committed. however, it wont find the inserted rows for productsale since they havent been inserted yet because of the lock. so changes for product get applied, and no change is detected for productsale, therefore nothing to apply.

    sync fx has nothing to rollback...

    if you're referring to the error you get on the trace, sync fx raised an event in the ApplyChangeFailed event for you to handle, you didnt handle it so it continous to sync the other record. just because one record cannot be applied doesnt mean it should rollback the entire synchronization.


    • Edited by JuneT Thursday, June 14, 2012 6:00 AM
    Thursday, June 14, 2012 5:58 AM
  • really good testing here, I was just told before the TRANSACTION is scope level but never tested by myself...

    I think the testing here is like:

            1. define scope with 2 tables in both source and target database.

            2. start Syncing, right after table 1 data get synced to target database, make sync fail at table 2 syncing(make an artificial timeout with Table lock).

    if the Syncing TRANSACTION level is the ''scope'(2 tables), the changes in table 1 should roll back since error occured.

    but obviously the result it NOT.


    This is shawn.



    • Edited by Shawn.shao Thursday, June 14, 2012 6:38 AM edit2
    Thursday, June 14, 2012 6:36 AM
  • an error was raised via the ApplyChangeFailed event and was handled correctly by the Sync Framework.

    again, do you expect the sync to fail if one record fails to apply because of a conflict? 

    you have the ApplyChangeFailed event so you can remedy the situation.

    thats why you have statistics of what has been applied and what has not been applied.

    the transaction on the scope is to guarantee that changes that have been successfully applied commit as a unit.

    it is not a commitment that all changes detected will be applied all or nothing.

    you wanna test rollback at the scope level, throw an error on the ApplyChangeFailed  event (or any other event where some changes has already been applied) so the sync aborts and see if changes were persisted.



    • Edited by JuneT Thursday, June 14, 2012 6:51 AM
    Thursday, June 14, 2012 6:49 AM
  • But that 2 tables is a unit(TRANSACTION) in their business, it's unacceptable that just table 1 have some data while the table 2 didn't?


    This is shawn.

    Thursday, June 14, 2012 7:04 AM
  • In this case, the conflict type is  DbConflictType.ErrorsOccurred,

    The code is:

                 if (e.Conflict.Type == DbConflictType.ErrorsOccurred)
                 {
                     StringBuilder log = new StringBuilder();
                     log.AppendLine("An error occurred during synchronization.");
                     log.AppendLine("Error source: " + e.Error.Source);
                     log.AppendLine("Error message: " + e.Error.Message);
                     Console.WriteLine(log);    
                 }
                 else
                 {
                     e.Action = ApplyAction.Continue;
                 }

    If I want to rollback the transaction when ErrorsOccurred, it seems that all the action typies cannot rollback the transaction

    Thanks,


    Get reply from social.microsoft

    Thursday, June 14, 2012 7:12 AM
  • sync fx has no idea of what constitutes a transaction from a business logic point of view.

    as far as its concern, a change was detected and it needs to apply it...the change tracking is even at the row level. there is nothing there to tell it two rows should always be together.

    its the same thing about parent-child relationships, it doesnt care much about the relationship.

    its just  DATA synchronization.

    if both tables need to be applied all or nothing, then simply catch the exception at the ApplyChangeFailed event and throw an exception to abort the sync (sync fx has no abort sync)

    Thursday, June 14, 2012 7:19 AM
  • Ok, so it is confirmed by you that the Sync Framework will never rollback applied changes even change applying failed for some table in the Scope, the only exception is we explicitly throw a exception in ApplyChnageFailed event, and this is supposed to be handled by Sync Framework internally and leading roll back all the changes in the Scope, and this is exactly what we want now: 'one table fail, all tables in Scope must fail either'.

          on more question? Instead throw a exception by user, why not add another Enum in DbApplyChangeFailedEventArgs, like 'RollbackAll', throwing exception is really a bad taste here...

     

      thanks.

     


    This is shawn.

    Friday, June 15, 2012 1:57 AM
  • i dont work for MS so i cant answer for them why there's no Rollback option in the ApplyChangeFailed event.

    with regards to the all or nothing, personally, i would prefer that the system tell which rows failed to apply so i can address them separately without preventing the other rows from being applied successfully. if am synching 10k rows, i will not want to rollback 9.9k rows because 100 failed and resync the entire 10k again.

    when designing scopes, you should put into consideration tables that should commit as one and isolate them from the others. 

    Sunday, June 17, 2012 12:49 PM
  • JuneT, Thanks a lot for your apply

    Throw an exception will rollback all the changes

                     if (e.Conflict.Type == DbConflictType.ErrorsOccurred)
                     {
                         StringBuilder log = new StringBuilder();
                         log.AppendLine("An error occurred during synchronization.");
                         log.AppendLine("Error source: " + e.Error.Source);
                         log.AppendLine("Error message: " + e.Error.Message);
                         Console.WriteLine(log);
                         throw new Exception("ErrorOccurred");
                     }
                     else
                     {
                         e.Action = ApplyAction.Continue;
                     }

    But rollback all will cost lot,

    From my test,

    There are Table1, Table2 and Table3 in the scope,

    BEGIN TRAN

    SELECT * FROM Table2 with (holdlock)

    The test result: the changes on Table1 and Table3 are sync to the other participant,

    So do you have any idea: Just the changes on Table1 sync to the other participant, and the changes after the Table2 (ErrorOccurred) don't sync to the other participant

    Regards,

    Abby


    Get reply from social.microsoft

    Wednesday, June 20, 2012 8:48 AM
  • hi abby,

    am not sure what you're asking here.

    so did the throw rollback all the changes in the scope?

    so you want to commit the changes on Table1 but rollback all changes after the error on Table2?

    Wednesday, June 20, 2012 9:01 AM
  • Yes, when I throw an exception, all the changes are rollbacked.

    And I want to commit the changes on Table1 but rollback all the changes after the error..

    Thanks,

    Abby


    Get reply from social.microsoft

    Thursday, June 21, 2012 4:36 AM
  • as i have mentioned, the transaction is at the scope level. so you can't tell sync fx you want to commit part of it and roll back a part of the scope.

    Thursday, June 21, 2012 5:36 AM