Does mySQL replication have immediate data consistency?

Question

I am considering a noSQL solution for a current project, but I'm hesitant about the 'eventual consistency' clause in many of these databases. Is eventual consistency different than dealing with a mySQL database where replication lags? One solution I have used in the past with lagging replication is to read from the master when immediate data consistency is needed.

However, I am confused then as to why relational database claim to have strong data consistency. I guess I should use transactions and that will give me strong consistency. Is it a good practice then to write applications assuming mySQL replication may lag?

Butter

I think the more precise technology for your question is InnoDB, not mysql. The storage engine is where replication handling gets comparable to other options – Stromboli

palominodb.com/blog/2012/06/20/… – Stromboli

Thanks. I guess transactions would prevent a write to master and read from slave inconsistency and you would do that with innodb. Editing my question. – Butter

Answer 1

Consistency in the sense it is used in ACID means that all constraints are satisfied before and after any change. When a system assures that you can't read data that is inconsistent, they're saying for example that you will never read data where a child row references a non-existent parent row, or where half of a transaction has been applied but the other half hasn't yet been applied (the textbook example is debiting one bank account but not yet having credited the recipient bank account).

Replication in MySQL is asynchronous by default, or "semi-synchronous" at best. Certainly it does lag in either case. In fact, the replication replica is always lagging behind at least a fraction of a second, because the master doesn't write changes to its binary log until the transaction commits, then the replica has to download the binary log and relay the event.

But the changes are still atomic. You can't read data that is partially changed. You either read committed changes, in which case all constraints are satisfied, or else the changes haven't been committed yet, in which case you see the state of data from before the transaction began.

So you might temporarily read old data in a replication system that lags, but you won't read inconsistent data.

Whereas in an "eventually consistent" system, you might read data that is partially updated, where the one account has been debited but the second account has not yet been credited. So you can see inconsistent data.

You're right that you may need to be careful about reading from replicas if your application requires absolutely current data. Each application has a different tolerance for replication lag, and in fact within one application, different queries have different tolerance for lag. I did a presentation about this: Read/Write Splitting for MySQL and PHP (Percona webinar 2013)

Endoskeleton

Bill. Can you look at this. #56332788 – Demars

@Bill Karwin, Could you please post the new link for your presentation? – Dunham

I think this is the one percona.com/sites/default/files/presentations/… – Dunham

That's the one. I was not aware that it had been removed from Slideshare. I'll contact them and find out why it was suspended. I guess because of the frequent use of the word "slave" in the presentation. – Endoskeleton

Answer 2

For completeness I'll also answer the question with the CAP theorem point of view. Oh and Consistency in ACID is not same as Consistency in CAP.

In terms of Consistency in CAP theorem, which says every read receives the most recent write or an error(this is referred as linearizability, a.k.a strong consistency a.k.a atomic consistency), MySQL is not strongly consistent by default because it uses asynchronous replication. So there is a period of time which some nodes in the group has the most recent write while some nodes still hasn't.

Also if your MySQL version is 8.0.14 or higher, then group_replication_consistency is configurable but still it's default value is EVENTUAL(this isn't configurable and is the default value in previous MySQL versions which I belive most apps running on). Details: https://dev.mysql.com/doc/refman/8.0/en/group-replication-configuring-consistency-guarantees.html

Furthermore if you're using MySQL Cluster(which is a different product/technology and I find it confusing they've called it cluster), MySQL documentation itself says it only guarantees eventual consistency. Details: https://dev.mysql.com/doc/mysql-cluster-manager/1.4/en/mcm-eventual-consistency.html

So we are safe to say that it's an eventually consistent system. And every asynchronously replicated system is eventually consistent by definition.

Eventide