Windows Azure Mobile Service to SQLite Data Sync for Windows Phone and Windows RT with Portable Class Libraries – Conflicts

In my previous post, Windows Azure Mobile Service to SQLite Data Sync…, I covered a simple framework for synchronising entities between Windows Azure Mobile Service (WAMS) and SQLite. Anyone familiar with synchronisation logic will of course have scoffed at this framework concluding that I haven’t dealt with cases where there are synchronisation conflicts, and they’d be correct. Well in actual fact I have: I assumed an optimistic resolution policy which states that the last update is the correct one to take. In this post I’ll cover a better approach to conflict resolution.

For the benefit of those who haven’t had to deal with synchronisation logic in the past, one of the common conflict scenarios is as follows:

– Users A and B both synchronise the same set of data so they both have local copies of the data, including a Person record: Person {Name = Jane, Age = 25}

– User A makes a change to the record: Person {Name = Jane, Age = 32}

– User B makes a change to the record:  Person {Name = Jane, Age = 30}

– User A synchronises with WAMS first, updating the server record to Person {Name = Jane, Age = 30}

– User B attempts to synchronise with WAMS

Current Scenario:

Under the current scenario the server record gets pulled down, effectively overwriting the record User B has updated

This assumes that User A’s update is correct, even though it was done before that of User B, just because User A synchronised before User B.

Alternate Scenario:

When User B attempts to synchronise, WAMS should reject the change, indicating that the record has changed since the last time User B synchronised. It should also return the current record, allowing User B to determine what the correct record is.

The question now becomes how do we extend the synchronisation logic presented in the previous post, to deal with this scenario.


The first step is to adjust WAMS to prevent updates to records where the data has changed since the last synchronisation. Note, we don’t need to handle inserts, since they are by definition unique (although it is possible that both User A and User B entered the same new record, they are unique and its up to the individual application to allow for duplicate records to be combined somehow).

When updated records are sent to WAMS the LastUpdated property is the same as when that record was last retrieved from WAMS (the LocalLastUpdated field is used to track records that have changed locally). As such, in the update script in WAMS it is possible to detect whether the record has been updated since the LastUpdated time. If it has we’ll return a 409 Conflict error, as per the following script.

function update(item, user, request) {
        console.log('Find existing entity with Id '+;
        var personTable = tables.getTable('Person');
            success: checkLastUpdated,
            error: checkFailed
    function checkLastUpdated(results) {
        if (results.length > 0) {
            console.log('Entity Id ' + results[0].id);
                console.log('Conflict found, not updating record');
                   "Entity has changed since last synchronisation");
            else {
                console.log('No conflict, updating record');
                item.LastUpdated=new Date();
        else {
            console.log('Entity search found no records');
            request.respond(statusCodes.NOT_FOUND,"Entity not found");
    function checkFailed()
        console.log('Entity search failed');
        request.respond(statusCodes.NOT_FOUND,"Entity not found");


If we run the application on two devices we can generate this conflict by synchronising both application instances. Changing the same record on both devices, synchronising one, and then synchronising the second. Note: The client side logic in part caters for this already by downloading changes from the server before uploading the client changes. If you jump over this in the debugger on the second client, you can see the 404 error that is raised when the conflict is detected:


If you check out the logs on WAMS you’ll see the output from the console.log statements that were in the update script:



Synchronisation Logic

There are two parts where we need to add conflict resolution:

– When server changes are downloaded – currently the updates from User A would be synchronised to the device, overwriting the changes from User B

– When local changes are uploaded – any conflicts with server records will result in a 409 failure.


Server Changes

To handle server changes we’ll add logic which will detect whether any of the downloaded changes conflict with any of the pending local changes. If there is a conflict, we need to store the downloaded record, alongside the local record, so that the user can choose which is the correct record. In this case we’re assigning the downloaded record a negative Id starting at –1000 (ie ConflictMarkerValue = –1). Whilst this limits us to 1000 new records (remember we assign new records a negative Id starting at –1) it means that we can effectively use the same table to store new, existing and conflict records.

// Check for update conflicts
var conflict = (from delta in updatedentities
                where delta.Id == entity.Id
                select delta).FirstOrDefault();
if (conflict != null)
    conflictsFound = true;
    var id = ConflictMarkerValue - entity.Id;
    var existing = await (from x in con.Table<TEntity>()
                    where x.Id == id
                    select x).FirstOrDefaultAsync();
    entity.Id = id;
    if (existing != null)
        await con.UpdateAsync(entity);
        await con.InsertAsync(entity);


You’ll also note that we set the conflictsFound flag to true. After iterating through all the downloaded records, if conflictsFound is true, the Sync method exits, returning a completed value of false – this is because we don’t want to upload any local records until the downloaded conflicts have been resolved. This will actually cover us with the second part where a 409 error gets returned but we’ll come to that in a minute.

If synchronisation doesn’t complete correctly we’ll need a mechanism for the user to iterate through the records that are in conflict and determine which record is correct.

public async Task<List<Conflict<TEntity>>> RetrieveConflicts<TEntity>()
    where TEntity : class, ISyncableEntity, new()
    var con = await Connect();
    // Retrieve the list of conflicted local entities
    var updatedentities = await (from remote in con.Table<TEntity>()
                                    where remote.Id <ConflictMarkerValue
                                    select remote).ToListAsync();
    var list = new List<Conflict<TEntity>>();
    foreach (var remote in updatedentities)
        var id = -(remote.Id - ConflictMarkerValue);
        var pair = await (from local in con.Table<TEntity>()
                            where local.Id == id && 
                            select local).FirstOrDefaultAsync();
        list.Add(new Conflict<TEntity>{Local = pair, Remote = remote});
    return list;


The list of conflicts can then be presented to the user, allowing them to make a decision as to which record they wish to keep:


When the user clicks the Accept Local or Accept Remote (clearly not a production-ready UX!) the ResolveConflict method is called:

public async Task ResolveConflict<TEntity>(Conflict<TEntity> conflict)
    where TEntity : class, ISyncableEntity, new()
         throw new Exception("Conflict not resolved");
    var con = await Connect();
    // Make sure we delete the conflicted record
    await con.DeleteAsync(conflict.Remote);
    if (conflict.ResolveLocal.Value)
        conflict.Local.LastUpdated = conflict.Remote.LastUpdated;
        await con.UpdateAsync(conflict.Local);
        conflict.Remote.LocalLastUpdated = DateTime.MaxValue;
        conflict.Remote.Id = conflict.Local.Id;
        await con.UpdateAsync(conflict.Remote);

If the user selects the local record to keep, then we simply update the LastUpdated value to when the records was last retrieved (ie when the conflict was discovered). If the user selects to keep the remote record, we simply overwrite the existing record with the remote record. Either way the changes are currently local only, which means we still need to force a Sync in order for those changes to propagate back to the server.


Local Changes

If there is a failure whilst uploading local changes to do with a conflict then the server will have returned a 409 Conflict. This error needs to be detected and the conflict resolved. However, when the server returns a 409 it doesn’t return the current server value. As such, we actually need to force another synchronisation in order to download the conflicting records (see previous section). To do this, we simply need to call Sync again!

catch (MobileServiceInvalidOperationException ex)
    if(ex.Response.StatusCode == HttpStatusCode.Conflict)
        syncAgain = true;
// Try sync again - this should retrieve conflicts
if (syncAgain)
    return await Sync<TEntity>();

These additions will help reduce any issues with conflicts. There are still some areas of weakness, for example the server validation isn’t carried out in a transaction, allowing for a write in between the read and write logic.

Leave a comment