Building a Mobile Sync Engine with SQLite (Part 3)

Advanced Conflict Resolution for Offline-First Mobile Applications

In Part 1 of this series, we built the foundation of a mobile sync engine using SQLite. We created a local database, tracked changes, uploaded updates, and downloaded new data from the server.

In Part 2, we made the sync engine much more efficient by implementing incremental synchronization, allowing devices to exchange only the records that changed instead of transferring entire datasets.

Our synchronization system is now fast and bandwidth-efficient.

However, one important problem remains.

Imagine two users editing the same record at nearly the same time.

Which version should be kept?

Should one change overwrite the other?

Should both be merged?

Should the user decide?

These situations are known as conflicts, and handling them correctly is one of the biggest challenges when building offline-first applications.

In this guide, we’ll improve our sync engine by implementing conflict detection and resolution strategies that keep data consistent across multiple devices.

Understanding Synchronization Conflicts

A conflict occurs when two devices modify the same record before either device has synchronized with the server.

Imagine this situation.

Phone

The user changes a task from:

Buy groceries

to

Buy groceries and milk

The phone is offline.

Tablet

At the same time, the user edits the same task.

The new value becomes:

Buy groceries tomorrow

The tablet is also offline.

Neither device knows about the other’s change.

Eventually both reconnect.

Now the server receives two different versions of the same record.

Both appear to be valid.

Which one should become the final version?

Why Conflicts Matter

Ignoring conflicts can cause:

• Lost work

• Inconsistent data

• User confusion

• Incorrect reports

Imagine editing a customer address.

Phone:

123 Main Street

Tablet:

125 Main Street

If one update silently overwrites the other, the application may lose important information.

Production systems must detect these situations before deciding what to do.

Detecting Conflicts with Version Numbers

One common approach is using a version number.

Each record stores:

CREATE TABLE tasks (
    id TEXT PRIMARY KEY,
    title TEXT,
    completed INTEGER,
    version INTEGER,
    updated_at INTEGER
);

Initially:

Task
Version = 1

Every successful update increases the version.

Version 1
      ↓
Version 2
      ↓
Version 3

When the client uploads a change, it also sends the version number it edited.

Example:

{
  "id": "task_1001",
  "version": 4,
  "title": "Buy groceries and milk"
}

If the server already stores Version 5, it immediately knows that another device updated the record first.

A conflict has been detected.

Optimistic Concurrency Control

Most offline-first applications use optimistic concurrency control.

The word “optimistic” means:

Assume conflicts are rare, but detect them when they occur.

Instead of locking records while users edit them, every device works independently.

Only during synchronization does the server compare versions.

If the versions match:

Update Accepted

If they differ:

Conflict Detected

This approach keeps applications responsive while still protecting data.

Strategy 1: Last Write Wins

The simplest conflict resolution strategy is Last Write Wins (LWW).

The server compares timestamps.

Example:

Phone
10:15 AM

Tablet
10:18 AM

The newest timestamp wins.

Advantages:

• Easy to implement

• Fast

• Minimal storage requirements

Disadvantages:

• Older changes disappear

• Users may lose work without realizing it

LWW works well for simple applications but is not ideal for important business data.

Strategy 2: Server Wins

Some systems always trust the server.

If a conflict occurs:

Server Version
       ↓
Accepted

The local update is rejected.

Advantages:

• Predictable behavior

• Easy to manage

Disadvantages:

• Local edits may be discarded

This approach is useful when the server represents an authoritative source of truth.

Strategy 3: Client Wins

Some applications allow the newest client update to overwrite the server.

Advantages:

• Local user always sees their latest changes

Disadvantages:

• Other users’ work may disappear

This strategy is uncommon in collaborative systems but may work for personal applications.

Strategy 4: Manual Conflict Resolution

For important information, users should decide.

Example:

Server version:

Buy groceries tomorrow

Phone version:

Buy groceries and milk

Instead of choosing automatically, the application displays both versions and asks the user which one to keep.

This is common in:

• Document editors

• Note-taking applications

• Medical software

• Financial systems

Although manual resolution requires user input, it avoids accidental data loss.

Merge Operations

Sometimes two updates do not actually conflict.

Example:

Phone changes:

completed = true

Tablet changes:

title = Buy groceries tomorrow

Because different fields changed, the sync engine can merge them automatically.

Final record:

Title = Buy groceries tomorrow
Completed = true

No information is lost.

Merge operations often provide the best user experience.

Recording Conflicts

Instead of resolving conflicts immediately, some applications record them.

Example table:

CREATE TABLE sync_conflicts (
    id INTEGER PRIMARY KEY,
    entity_id TEXT,
    local_version TEXT,
    server_version TEXT,
    detected_at INTEGER
);

The app later reviews unresolved conflicts.

Benefits include:

• Better auditing

• Easier debugging

• User-assisted resolution

Keeping Multiple Devices Consistent

Imagine a user owns:

• Phone

• Tablet

• Laptop

Each device has its own SQLite database.

The server acts as the coordination point.

Whenever one device synchronizes successfully:

• The server stores the latest version.

• Other devices receive the update during their next synchronization.

• Every device eventually reaches the same state.

This is known as eventual consistency.

Devices may not be identical immediately, but they become consistent over time.

Applying Updates Safely

Conflict handling should always happen inside a transaction.

Example:

BEGIN TRANSACTION;

-- Apply updates

COMMIT;

If an error occurs:

ROLLBACK;

SQLite guarantees that either:

• Every update succeeds

or

• Nothing changes

This prevents partially synchronized data.

Common Conflict Scenarios

Production applications often encounter situations such as:

Delete vs. Update

One device deletes a record.

Another edits it.

Should the deletion win?

Should the edit restore the record?

Multiple Offline Devices

Three devices remain offline for several days.

Each edits the same customer record.

The server later receives three different versions.

Simultaneous Synchronization

Two devices upload changes within milliseconds.

Version checking prevents updates from silently overwriting each other.

Best Practices

When building production sync engines:

• Use version numbers for conflict detection.

• Keep timestamps for auditing.

• Resolve conflicts inside transactions.

• Log unresolved conflicts.

• Merge changes whenever possible.

• Avoid silent data loss.

• Let users resolve important conflicts manually.

These practices make synchronization more predictable and trustworthy.

Putting Everything Together

Our mobile sync engine now follows this workflow:

User Updates Record
         ↓
SQLite Stores Local Change
         ↓
Sync Engine Uploads Update
         ↓
Server Compares Version
         ↓
Conflict?
      ↓        ↓
    No         Yes
    ↓          ↓
 Apply     Resolve Conflict
 Update         ↓
    ↓      Store Final Version
    ↓          ↓
 Other Devices Synchronize

Compared to Part 1, our synchronization engine is now significantly more capable.

It supports:

• Offline work

• Incremental synchronization

• Version tracking

• Conflict detection

• Automatic and manual conflict resolution

• Multi-device consistency

Closing Thoughts

Building a reliable mobile sync engine involves much more than uploading and downloading records.

As applications grow and users begin working across multiple devices, conflicts become unavoidable.

By introducing version numbers, optimistic concurrency control, merge operations, and structured conflict resolution, we can prevent silent data loss while keeping the application responsive.

SQLite continues to provide the reliable local storage layer, while the synchronization engine coordinates changes between devices and the server.

Together, they form the foundation of robust offline-first mobile applications used every day across industries.

Coming Ahead: Part 4

Our sync engine can now synchronize data efficiently and resolve conflicts between devices.

The next challenge is making synchronization automatic, reliable, and invisible to users.

In Part 4, we’ll build a background synchronization system that works quietly behind the scenes.

We’ll explore:

• Automatic background syncing

• Sync scheduling strategies

• Push notifications for instant updates

• Retry queues and exponential backoff

• Battery and network optimization

• Monitoring synchronization health

• Building a production-ready sync service

By the end of Part 4, our mobile sync engine will behave much like the synchronization systems used in modern note-taking, messaging, and productivity applications, keeping data up to date without requiring users to think about it.

Subscribe Now

Join thousands of developers and master advanced SQLite techniques, tips and best practices. Subscribe now to our newsletter and never miss an update!