none
Transactions and timestamp: Sync Framework for non-SQL Server 2008 databases? RRS feed

  • Question

  • Hi everyone,

    I am using Sync Fx 2.1 with the DbSync... components, against two Firebird databases. After fighting a lot with it, I managed to implement a working scenario, with all the bits working as expected, and all kind of unit tests to ensure that everything works.

    Almost. After complains from my customer, I was amazed to see that, for some reason, there were some rows not being synchronized. It looked "random": a sync process set up to run continuously worked for 5 hours, and then, suddenly, an insert wouldn't work because of a foreign key not being fulfilled. And from that point on, the sync would always fail. This is obviously a big issue, one I never saw before, and since then I have spent a lot of time looking at my code, trying to understand why and how it could happen.

    Enough of background. After more tests and thoughts, I managed to replicate the case in a unit test. And actually, the scenario is so simple that I am ashamed to admit that I should have thought of this test before:

    • Start transaction A
    • Insert a row "X"
    • Start the synchronization
    • During (or after) the synchronization, commit transaction A
    • Start the synchronization again

    During the first synchronization, the row X is not detected, as it wasn't committed when the synchronization started. Fine. But, during the second synchronization, the row X is skipped. And then is when the problem begins.

    I am using a sequence in each Firebird database to generate the timestamps -- as far as I understand, we need something monotonously increasing, and a sequence is perfect for that.

    In the scenario above, the row X will get (let's say) timestamp 50 when it is updated. Now, the synchronization will get the current timestamp of 51, and will enumerate rows up to that point. As the transaction A has not been committed, it won't see the row X, which is fine. Then the sync finishes, and the knowledge takes note that rows up to timestamp 51 have been synced.

    In the mean time (during or after the sync), the transaction A is committed, and the row X is now visible with timestamp 50.

    And then, a new sync begins. But based on the knowledge, it will look for rows with timestamp greater than 51, so it will skip our row X.

    I hope the case is clear. I have found a post from Rafik Robeal (an ex worker of Microsoft) which deals with this situation (the post is missing the images, but it is the same idea). The post is this: http://blogs.msdn.com/b/synchronizer/archive/2008/02/06/sql-server-change-tracking-vs-min-active-rowversion.aspx . His answer to the problem is, basically, to use SQL Server 2008, as all the rows will get a timestamp based on the transaction commit time, not on the operation time.

    I see his point, but I cannot use SQL Server 2008. I'm stuck with Firebird, and what it's worse, it could be MySQL or SQLite tomorrow - we're bound to our customer requests.

    So I beg for help here. At this point I can only see two reasons for this problem:

    1) I took the wrong decision when designing the timestamp provider. Using a sequence is not ok. But then, what should I use?

    2) I took the right decision, but it's the whole design of the Sync Framework that is flawed, and/or only designed to work with SQL Server 2008.

    3) Is there any other reason?

    I don't really believe that option 2 is right... after all, the Sync Framework is supposed to work with any ADO.NET compatible provider... But, as far as I see, the problem I have it's something that has to be affecting other Microsoft technologies such as SQL Server 2005 or 2000...

    Could anyone please give me a hand with this?

    Thank you really much in advance

    Fernando

    Tuesday, January 25, 2011 3:41 PM

Answers

  • Hi Fernando,

    The case you described is a known issue as you searched out from Rafik's blog. this is not a design flaw of sync framework, as the root cause is the lack of support for such needs in the backend DB server. SqlServer exposed the min_active_rowversion in SqlServer2005 SP2 to handle it.

    Here are some options you may consider to handle this issue.

    1. ensure that when sync starts, there is no pending transaction on the sync-ed tables. this is essentially to serialize the dataChange/sync process to avoid this situation.

    2. work with the backend DB server company to see if there are equivalent method as the min_active_rowVersion as in MS SqlServer 2005 SP2 or above.

    3. build your own anchor values with the non-SqlServer databases so that the right value can be persisted in the DB after the data change transactions were committed. by reading the post, this seems the way you have already use in the Firebird databases.

    back to your questions above, I would say choosing sync framework for data synchronization is a right choice given its flexibility and easy to customize the sync solutions to achieve the desired business needs and goals.

    Hope this helps.

    thanks

    Yunwen

     


    This posting is provided "AS IS" with no warranties, and confers no rights.
    Thursday, January 27, 2011 9:35 AM
    Moderator

All replies

  • Hi Fernando,

    The case you described is a known issue as you searched out from Rafik's blog. this is not a design flaw of sync framework, as the root cause is the lack of support for such needs in the backend DB server. SqlServer exposed the min_active_rowversion in SqlServer2005 SP2 to handle it.

    Here are some options you may consider to handle this issue.

    1. ensure that when sync starts, there is no pending transaction on the sync-ed tables. this is essentially to serialize the dataChange/sync process to avoid this situation.

    2. work with the backend DB server company to see if there are equivalent method as the min_active_rowVersion as in MS SqlServer 2005 SP2 or above.

    3. build your own anchor values with the non-SqlServer databases so that the right value can be persisted in the DB after the data change transactions were committed. by reading the post, this seems the way you have already use in the Firebird databases.

    back to your questions above, I would say choosing sync framework for data synchronization is a right choice given its flexibility and easy to customize the sync solutions to achieve the desired business needs and goals.

    Hope this helps.

    thanks

    Yunwen

     


    This posting is provided "AS IS" with no warranties, and confers no rights.
    Thursday, January 27, 2011 9:35 AM
    Moderator
  • Hello Yunwen,

    Thank you very much for your reply. It is good to reassure that this problem comes from one requirement, the min_active_rowversion, with implications that I did not understand before.

    Finally I opted to implement #2, as Firebird does have the same concept. #1 will work but seems a bit unrealistic - at least in our environment; and our approach with sequences does work, so #2 is my only option.

    It is not optimum, as Rafik already pointed out, because if you have a long-lasting transaction sitting there, the min_active_rowversion would be that one, and you wouldn't be synchronizing data already committed by other transactions in the mean time. But in our system, 99.9% of transactions should be really fast, and most important, this approach ensures that we won't miss any row again (with all the problems that it would have with data integrity).

    I see that SQL Server 2008 has implemented a clever versioning trick: assign the version at commit time, not at begin trans time. That seems to be the optimum approach, but unfortunately, I don't have that mechanism available in Firebird (at least, as far as I know). So the min_active_rowversion trick is my best option.

    Thanks again for your reply. If only, I wish the docs would include the requirement of the min_active_rowversion more prominent... A bit too late for me, but it could help others.

    Best regards,

    Fernando

    Friday, January 28, 2011 1:13 PM
  • Fernando, 

    I'm facing same difficulties you had faced. Would you mind, please, sharing how did you implement that triggers on Firebird? You can contact me: pauloquicoli at gmail.com

    thank you


    Desenvolvedor Delphi, C# - Colunista da revista ClubeDelphi

    Monday, March 19, 2012 7:37 PM