In response to Eric Day's post on "Eventually Consistent Relational Database?"... I started posting a comment there, but I realized I have my own blog for this sort of thing. :)

I've been thinking the same thing, it's nice to hear I'm not the only one. This is a neglected area of "cloud" development, mostly because it's a big scary problem. Everyone says "use NoSQL", but if we had strategies/systems to give us EC RDBMS solutions, nobody would use key/value storage (except where it actually made sense). NoSQL is a big golden hammer nowadays. It works, but it sure takes a lot of effort to code stuff the storage layer should be able to handle (joins, etc.).

I need to read through the referenced papers, but I've been thinking that some concept of a "transaction" is what we should still use, though the changes within the transaction should not depend on A, C, or I. My thought was that the replication "event", should be bigger, not smaller, we should replicate "add this user" and either succeed or fail, not "insert a row here, update a column there", and that those larger events must not depend on any kind of replication ordering to be successful.

The events, too, should have some kind of unique identifier so in a multi-way replication scenario (imagine DB writers all over the world), all the data eventually gets propagated everywhere, but in no specific order or in any specific direction. DB consistency could be checked by simply comparing this list of accepted identifiers with those on another server. Replication could be one server asking an other (or many others): "what do you have, that I don't?"

In this case, the event could actually be a transaction in the 'BEGIN; ... COMMIT;' sense, but there could be a replication plugin in Drizzle that could handle it from the event perspective.

Unfortunately, I think the problem with my scheme is that it puts all the weight on the application developer. This kind of system could be built easily on MySQL (or Drizzle) today, assuming the application was written to handle it from the start, but it certainly wouldn't be generic. I don't see how to make this generic without inventing a new syntax, perhaps just a subset of SQL.

Anyway, this is a great topic for the MySQL conference next year... :)