Answered by:
Swapping Unique Constraint Values

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.
- 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.
- Change the second row's value in this column to the value that the first row had originally.
- 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 ApplicationsSunday, 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
- Marked as answer by María del Mar Alvarez Rohena Thursday, February 3, 2011 6:59 PM
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.- Proposed as answer by Sid Singh [MSFT]Microsoft employee Tuesday, November 2, 2010 12:50 AM
- Marked as answer by María del Mar Alvarez Rohena Thursday, February 3, 2011 6:59 PM
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
- Marked as answer by María del Mar Alvarez Rohena Thursday, February 3, 2011 6:59 PM
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.- Proposed as answer by Sid Singh [MSFT]Microsoft employee Tuesday, November 2, 2010 12:50 AM
- Marked as answer by María del Mar Alvarez Rohena Thursday, February 3, 2011 6:59 PM
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 20101103USE tempdbGO
IF OBJECT_ID('tempdb.dbo.#tmp') IS NOT NULLBEGINPRINT 'Dropping table'DROP TABLE #tmpENDGOCREATE TABLE #tmp(Id int PRIMARY KEY,Column1 int NOT NULL,Column2 int NOT NULL,UNIQUE (Column1, Column2)
)
INSERT INTO #tmpSELECT 1, 10, 2647 UNION ALL SELECT 2, 10, 3658
-- fails unless constraints are disabled, integrity risk/*BEGIN TRAN
UPDATE #tmpSET Column2 = 3658WHERE Id = 1
UPDATE #tmpSET Column2 = 2647WHERE Id = 2
COMMIT*/
-- succeeds even if a unique constraint exists-- no integrity risk, update both values at once...UPDATE #tmpSET Column2 =CASEWHEN Id = 1 THEN 3658
WHEN Id = 2 THEN 2647ELSE Column2ENDWHEREId IN (1,2)GOWednesday, November 3, 2010 10:37 PM -
It is very helpful thanks!
Alexander Yaremchuk
Monday, August 26, 2013 1:52 PM