The Dangers of Timestamp Based Change Tracking

All sync solutions have to somehow track the changes to records/rows on the originating DB, which we call logical transactions, and apply the changes to the destination DB.

The most popular change tracking method is based on timestamps. The approach is very straightforward. You add a timestamp column to your table and update the timestamp column whenever you change a row. This can be done in your SQL statements or through triggers. By the way, deletions have to be tracked separately, e.g. using a companion table. You track the changes on server this way and then at sync time, the sync client would come in with a last sync timestamp and you select all the server changes that have a newer timestamp than the last sync timestamp.

A lot of sync solutions put the burden of change tracking on app developers and the timestamp approach is the number one recommended technique for change tracking. This is also a widely used technique when people have to implement their own sync logic. However, be aware of its pitfalls.

One obvious pitfall is system time. Timestamps are generated using system time so messing up system time would cause problems. Don’t adjust system time even if it is off. Do not sync during time shifts between daylight saving time and standard time.

There is a more serious problem with this technique which could cause change loss. We know that the default isolation level for MySQL with Innodb and Oracle is “Read Committed”, which means that others cannot see the changes before a transaction is committed. Let’s say at time T1 you modified a record R1. The timestamp column would have a value of T1. Then before you commit, someone synced at T2 (T2 > T1). This sync could not pick up the change to record R1 since the transaction was not committed yet and the change was invisible. At T3 you committed the transaction and at T4 (T4>T3>T2>T1) you do another sync. Guess what, you still won’t get the change to R1! It was committed but it has a timestamp T1 that is older than last sync time T2. The client will forever miss the change no matter how many times you sync.

This problem is not so well known and is very hard to work around in a production environment where you can’t control when a sync and transaction would occur, and how long they would last etc.

Fortunately Pervasync has an innovatively designed sync engine that can take care of this kind of sync issues. You don’t need to have any timestamp column to worry about. Just do your normal DB DML (Data Manipulation Language) operations and the Pervasync system would track the changes for you and guarantee no change loss.


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s