Comment by samwillis | Hacker News Reader

Comment by samwillis | Hacker News Reader

Parent Full thread

I don't think it does with a patchset but maybe could with a changeset:

  > When processing an UPDATE change, the following conflicts may be detected:
  > The target database may contain a row with the specified PRIMARY KEY values, but the current values of the fields that will be modified by the change may not match the original values stored within the changeset. This type of conflict is not detected when using a patchset.

Also, if you have A and B, both initially the same, modify both with an UPDATE on the same existing row, and send and apply the patchiest to each other, I think they will overate each other and have a different state...

Edit:

I wander if there is a way to add a last_mod column to the tables and so only a more recent UPDATE would be executed from a patch set? It would only work at the row level but for most cases that should be ok. You could always create other tables for more granular control.

It looks like you can provide a conflict hander too that can replace the update statement, that could be useful for merging conflicting changes.

This could be exactly what I want for something!

Please don’t ever use time stamps for synchronization. You will have a bad time.

Clock skew is an insidious problem: things may look like they’re working, but they’re not. Given all of the clock bugs out there it’s sometimes not even safe to rely on the clock on a single machine. Across multiple machines it’s hopeless.

Please note that this doesn’t apply if you have a fancy time source like Google uses in their fancy cloud DB (whose name escapes me at the moment).

It’s called Spanner and TrueTime. Their docs are a good read: https://cloud.google.com/spanner/docs/true-time-external-con...

This is nice if you can afford to have atomic clocks in your data centers and whole infrastructure teams working on keeping your clocks in sync (read: if you are Google) but otherwise you're probably gonna have a bad time.

I think those clocks are coming down in price but the bigger challenge is the logistics - for whatever reason shared data centers don’t run in a GPS signal from the outside that all their clients can share.

Well within the price range of smaller data centers, ~$1500 to build the open source reference card published by the open compute project: Open Time Server

http://www.opentimeserver.com/

$1500 should be nothing, even to small data centers.

While I agree that 99% of the time you are right. There are edge cases where a time stamp is best.

I’m looking for a system using SQLite that gives you the same eventual consistency guarantees as CouchDB/PouchDB on consumer devices. You need to be able to have nodes disconnect from each other and the internet for long periods of time before syncing. Hours or days not seconds or milliseconds.

If two nodes overwrite the same row while disconnected you need a deterministic way to decide which is the winner. This could be though a hash of the values in the row (lowest value wins) or using a timestamp. Pros and cons for each and it depends on use case. So for example if you are building a notebook application you almost certainly want the most recent update to win. The transaction rate would be low and devices with an internet connection will be “relatively” accurate on time.

Obviously this could be open to abuse, someone changing their clock to force their edit to win. But again pros and cons. The use case I’m looking for is within a trusted group where this wouldn’t be a problem.

You can accomplish this by avoiding updates: https://kyle.marek-spartz.org/posts/2015-09-08-eventual-cons...

Reply on news.ycombinator.com