Does mySQL replication have immediate data consistency?

I am considering a noSQL solution for the current project, but I do not dare about the article "final consistency" in many of these databases. Is possible consistency different from working with mySQL database, where replication is lagging? One solution I've used in the past with replication latency is to read from the wizard when consistent data consistency is needed.

However, I am confused about why the relational database claims to have strong data consistency. I think I should use transactions, and this will give me a strong consistency. Is it good to write applications then, assuming mySQL replication might lag?

+12
source share
1 answer

Consistency in the sense in which it is used in ACID means that all restrictions are met before and after any change. When the system assures that you cannot read data that is inconsistent, they, for example, say that you will never read the data when the child row refers to a nonexistent parent row or where half the transaction was applied, except the other half was not yet applied (in the example from the textbook one bank account is debited, but has not yet been credited to the beneficiary's bank account).

Replication in MySQL is asynchronous by default or, at best, “semi-synchronous”. Of course, this is lagging anyway. In fact, the replication replica always lags by at least a split second, because the wizard does not write the changes to its binary log until the transaction is complete, then the replica should load the binary log and send the event.

But the changes are still atomic. You cannot read data that is partially modified. You either read the committed changes, and in this case all the restrictions are fulfilled, or the changes are not yet committed, and in this case you see the state of the data before the start of the transaction.

This way, you can temporarily read old data in a replication system that is lagging, but you will not read conflicting data.

While in a “ultimately consistent” system, you can read data that is partially updated when one account has been debited and the second has not yet been credited. This way you can see conflicting data.

You are correct in that you may need to be careful when reading from replicas if your application requires absolutely current data. Each application has a different tolerance for delayed replication, and in fact, within a single application, different requests have a different tolerance for delayed replication. I made a presentation about this: http://www.slideshare.net/billkarwin/read-write-split

+37
source

Source: https://habr.com/ru/post/974895/


All Articles