locked
Swapping Unique Constraint Values RRS feed

  • Question

  • Hi,

    A problem that I have with my existing Sync Framework 2.1 solution is related to unique constraints. I am wondering if there is a solution available in 4.0 - or if I am missing one that is already available in 2.1.

    If a sync node goes through the following three step process to swap two unqie key values before syncing then conflicts occur.

    1. Change the first row's value in the column covered by a unique constraint to some temporary value that has not so far been used.
    2. Change the second row's value in this column to the value that the first row had originally.
    3. Change the first row's value to the value that the second row had originally.

    The peer seems to attempt to apply these changes without the intermediate step descirbed in point 1. This fails though as changing the value in row 2 to the original value in row 1 violates the unique constraint.

    One possible workaround is to perform a sync after step 1.

    Another is to handle the conflict during synchronization however it seems like including a solution within the Sync Framework itself would both be possible and add value - that is if there is not already one there ;)

    Thanks,

    Scott


    MCPD Enterprise Applications
    Sunday, October 31, 2010 7:55 PM

Answers

  • There isn't any support in the Sync Framework to do this with Database Providers. The problem is that when enumerating changes on the source before sync, a snapshot of the data is used - we don't really sync each change that is made.

    Let me have someone else in the team take a look to see if there is any way you can queue up failed changes and try to apply them again in the same sync session.

    thanks

    Monday, November 1, 2010 4:53 PM
  • Hi,

    Sid is right that Database provider doesn't re-play every intermediate change that you made between two adjacent syncs. You can consider below approachs as possible workaround:

    1. Get DBConnection and DbTransaction objects from the first DbApplyingChangesEventArgs, and disable unique contraint temporarily in this transaction. Re-enabling it in the same transaction when reaching the last DbChangesAppliedEvent (DbChangesAppliedEventArgs.Context.IsLastBatch == True). It works for your scenario, but will fail the whole transaciton if it is a real unique constraint violation.

    2. Database provider doesn't provide any supports for you to queue up failed changes. You can queue up them by your self after receiving DbChangeFailedEvent. You can fetch the failed row from the DbChangeFailedEventArgs.Context.DataSet and store them in your own list. When reaching the last DbChangesAppliedEvent, you can apply them with the same sync transaction. They are treated as local changes, and you are expected to see update-update conflict for these rows next time. But you can resolve them with either Continue or ForceWrite conflict policies at that time.

    Thanks,
    Dong


    This posting is provided AS IS with no warranties, and confers no rights.
    Tuesday, November 2, 2010 12:49 AM

All replies

  • There isn't any support in the Sync Framework to do this with Database Providers. The problem is that when enumerating changes on the source before sync, a snapshot of the data is used - we don't really sync each change that is made.

    Let me have someone else in the team take a look to see if there is any way you can queue up failed changes and try to apply them again in the same sync session.

    thanks

    Monday, November 1, 2010 4:53 PM
  • Hi,

    Sid is right that Database provider doesn't re-play every intermediate change that you made between two adjacent syncs. You can consider below approachs as possible workaround:

    1. Get DBConnection and DbTransaction objects from the first DbApplyingChangesEventArgs, and disable unique contraint temporarily in this transaction. Re-enabling it in the same transaction when reaching the last DbChangesAppliedEvent (DbChangesAppliedEventArgs.Context.IsLastBatch == True). It works for your scenario, but will fail the whole transaciton if it is a real unique constraint violation.

    2. Database provider doesn't provide any supports for you to queue up failed changes. You can queue up them by your self after receiving DbChangeFailedEvent. You can fetch the failed row from the DbChangeFailedEventArgs.Context.DataSet and store them in your own list. When reaching the last DbChangesAppliedEvent, you can apply them with the same sync transaction. They are treated as local changes, and you are expected to see update-update conflict for these rows next time. But you can resolve them with either Continue or ForceWrite conflict policies at that time.

    Thanks,
    Dong


    This posting is provided AS IS with no warranties, and confers no rights.
    Tuesday, November 2, 2010 12:49 AM
  • It is possible to swap the values using a single updateable operation

    -- Research -- How to swap values in a unique constraint scenario 20101103
    USE tempdb
    GO

    IF OBJECT_ID('tempdb.dbo.#tmp') IS NOT NULL
    BEGIN
      PRINT 'Dropping table'
      DROP TABLE #tmp
    END
    GO
    CREATE TABLE #tmp
    (
      Id int PRIMARY KEY,
      Column1  int NOT NULL,
      Column2 int NOT NULL,
      UNIQUE (Column1, Column2)

    )

    INSERT INTO #tmp
    SELECT 1, 10, 2647 UNION ALL SELECT 2, 10, 3658

    -- fails unless constraints are disabled, integrity risk
    /*
    BEGIN TRAN

    UPDATE #tmp
    SET Column2 = 3658 
    WHERE Id = 1

    UPDATE #tmp
    SET Column2 = 2647
    WHERE Id = 2

    COMMIT
    */

    -- succeeds even if a unique constraint exists
    -- no integrity risk, update both values at once...
    UPDATE #tmp
    SET Column2 = 
      CASE 
        WHEN Id = 1 THEN 3658 

        WHEN Id = 2 THEN 2647 
        ELSE Column2 
      END
    WHERE 
      Id IN (1,2)
    GO
    Wednesday, November 3, 2010 10:37 PM
  • It is very helpful thanks!

    Alexander Yaremchuk

    Monday, August 26, 2013 1:52 PM