Avoiding False Conflicts Resulting From Interrupted Sync
A great thread on the sync services forum yesterday brought up this interesting question on what happens if the sync session is interrupted due to connection failure or client application crash …etc. To visualize the scenario in your mind, picture an n-tier sync app which uses a web service to upload changes to the server. At sync time, the web method was called to upload changes to the server; the server applies the changes but prior to returning to the client the connection was dropped. In this case, the client has not received the acknowledgement from the server and thus didn’t save the new sent anchor. In the next sync, the client will attempt to upload the changes that it sent last time along with any new changes to the server. Now, the question is how can you, the developer, deal with duplicate application of same rowa and what can the DbServerSyncProvider do to help you out?
Let me answer this by going back to the tracking infrastructure that you have seen in the samples. Recall from the demos that one of the tracking columns added on the server was originator_id column. This column records the id of the client which makes the last change to the row. If the change was done on the server (outside of sync), the value of this column is reset to the server id instead (which is typically 0)
The originator_id column is needed for two reasons:
1- Breaking the Loop
Changes uploaded by a given client should not come down when the same client download changes. originator_id column helps you filter out those changes
2- Interrupted Sync
When the client app crashes or the connection to the server is dropped the sync is interrupted. Some of the in-flight changes might have been processed already but the sync anchor which is persisted after successful sync have not been saved yet which means that in the next sync some of the already-processed changes will be resent. This situation results in false conflicts. However, you can avoid these conflicts using originator_id knowing that a client change cannot conflict with its own pervious change. In other words, if client A uploaded a change to row R, then the same change (or even new change) to R was uploaded again; there should be no conflict as long as the row on the server has A as the originator_id. If R was updated on the server (i.e. by different client) in the mean time, then conflict is unavoidable.
How can you implement this logic in your commands?
Actually, this logic is already implemented in all demos and also generated by the SyncAdapterBuilder. Here is a quick recap:
For update and delete commands, the where clause skips the conflict detection check (update_timestamp <= @sync_last_received_anchor) if the pervious originator_id is same as the synchronizing client (update_originator_id = @sync_client_id) as show below:
Update Command:
update [orders]
set [order_date] = @order_date,
[update_originator_id] = @sync_client_id
where (update_timestamp <= @sync_last_received_anchor or update_originator_id = @sync_client_id) and [order_id] = @order_id
set @sync_row_count = @@rowcount
Delete Command:
delete from [orders] where (update_timestamp <= @sync_last_received_anchor or update_originator_id = @sync_client_id) and [order_id] = @order_id
set @sync_row_count = @@rowcount
Insert Command:
There are two ways to avoid false conflicts when inserting a row:
(1) Optimistic: Let it fail hard and deal with the conflict later on (as explained below)
(2) Pessimistic: Before applying the insert, make sure that the row does not exist already. There is an obvious race condition between the first check and the insert though
The demos typically show simple insert command. For interrupted sync scenario, the second insert of the same row will fail and throws exception; the DbServerSyncProvider will surface the error as ApplyRowFailedEvent. With this event you could decide to either skip the row or retry with the force write flag set. In the latter case, your command has to execute an update instead of insert when the flag is set, as follows:
if @sync_force_wirte = 0
insert into [orders] ([order_id], [order_date], [update_originator_id])
values (@order_id, @order_date, @sync_client_id)
else
update [orders]
set [order_date] = @order_date,
[update_originator_id] = @sync_client_id
where (update_timestamp <= @sync_last_received_anchor or update_originator_id = @sync_client_id) and [order_id] = @order_id
set @sync_row_count = @@rowcount
I tend to prefer the optimistic approach since such failures are typically very low percentage. However, the pessimistic approach would perform better if the probability of interruptions is high.
Update: Just to let you know, I left Microsoft to start a new company, Raveable Hotel Reviews . See examples: Romantic Hotels in Myrtle Beach , Best Hotels in Seattle , Top 10 Hotels in Miami , Hotels with in-room hot tub and Kid friendly hotels in San Francisco . Your feedback is welcome on twitter.com/raveable, raveable blog.