Issues with sync and constraints
(This entry is part of a series. The audience: SQL Server developers. The topic: SQLite on mobile devices.)
Think of a bug as having two parts:
The incorrect code
The visible symptom
The worst bugs are the ones where these two parts are separated.
For example, consider the following function in C:
char* p = NULL;
*p = 5;
This crash will be easy to find and fix, because the incorrect code is very close
to the point where the crash is going to occur.
In contrast, the following code is likely going to waste more time:
int count_decimal_digits(int n)
char* p = malloc(64);
sprintf(p, "%d", n);
One of the several bugs in this function is a memory leak. Whatever symptom arises from
this leak will almost certain occur much later, making it much more difficult to realize
that the incorrect code is right here in this function.
In 1992 I was working at Spyglass (before we joined the browser wars, when our focus
was on scientific data visualization tools). We had a product named Spyglass Format
which had a bug involving our failure to properly dispose of a handle we got
from the Mac palette manager. The visible symptom of that bug was an intermittent, unreproduceable
crash. Bugs like that are so hard to find, but this one was unusually difficult,
because the crash always happened in a different app, not in Spyglass Format. :-)
the affected user started by calling the vendor of the other product (which happened
to be Apple) about this problem. And of course, Apple was unable to help them.
And of course, when they called us to claim that "it seems like MPW only crashes when Spyglass
Format is also running", we were initially rather skeptical.
The whole thing took months to figure out.
Sync and Constraints
Let's talk about situations where you are using SQLite on a mobile device
and synchronizing with SQL Server on the backend.
Compared to an app which does all database operations over REST calls, the advantages
of this "replicate and sync" architecture include offline support and much better performance.
However, one of the potential disadvantages of this approach is that it can move the symptom of a
constraint violation bug far away from the incorrect code that caused it.
In your SQL Server database on the backend, you have constraints which are designed to
protect the integrity of your data.
Suppose you have an app which is trying to INSERT
an invalid row directly, such as through ADO.NET. The constraint violation
will cause an error right away. This is good.
However, in a mobile app which uses "replicate and sync", changes to the data happen in
The row gets INSERTed into a SQLite database on the mobile
Later, the next time that device syncs with the backend, that row will get INSERTed into the actual SQL Server database.
If the new row is invalid (because of, say, a bug in the mobile app), we want the failure to happen when we try the
INSERT into SQLite on the mobile device, not [potentially much] later when the
Or to put this another way: Any transaction successfully committed to the SQLite
database on the mobile device should also succeed when that change is synchronized
to the SQL Server backend.
If SQLite always behaved exactly like SQL Server,
this would not be an issue. But there are differences, and that's what this blog series is all about.
Several of the entries later in this series
will deal with specific cases where SQLite might accept something that
SQL Server would not. In a "replicate and sync" architecture, all of these cases deserve a bit of extra attention.