MySQL复制是否具有即时数据一致性? [英] Does mySQL replication have immediate data consistency?

问题描述

我正在考虑为当前项目使用noSQL解决方案，但是我对其中许多数据库中的最终一致性"子句犹豫不决.最终的一致性与处理复制滞后的mySQL数据库有所不同吗?我过去用于滞后复制的一种解决方案是在需要即时数据一致性时从主服务器读取数据.

但是，对于为什么关系数据库声称具有强大的数据一致性，我感到困惑.我想我应该使用事务，这将使我具有很强的一致性.然后假设mySQL复制可能会滞后编写应用程序，这是一个好习惯吗?

解决方案

在ACID中使用一致性的含义是，在进行任何更改之前和之后，所有约束均得到满足.当系统确保您无法读取不一致的数据时，他们说，例如，如果子行引用了不存在的父行，或者已经应用了一半事务但仅在子行引用了不存在的情况下，您将永远不会读取数据.另一半尚未应用(教科书示例是从一个银行帐户中扣款，但尚未将收款人银行帐户贷记).

默认情况下，MySQL中的复制是异步的，或者最好是半同步".当然，无论哪种情况，它都滞后.实际上，复制副本始终至少滞后一秒，因为在事务提交之前，主数据库不会将更改写入其二进制日志，因此副本必须下载二进制日志并中继事件. /p>

但是这些变化仍然是原子的.您无法读取部分更改的数据.您要么读取已提交的更改(在这种情况下，所有约束都已满足)，要么还没有提交更改，在这种情况下，您将看到事务开始之前的数据状态.

因此，您可能会在滞后的复制系统中临时读取旧数据，但不会读取不一致的数据.

在最终一致"的系统中，您可能会读取部分更新的数据，其中一个帐户已借记，而第二个帐户尚未记入贷方.因此，您可以查看不一致的数据.

是正确的，如果您的应用程序需要绝对最新的数据，则在读取副本时可能需要格外小心.每个应用程序对复制滞后具有不同的容忍度，实际上，在一个应用程序中，不同的查询对复制滞后具有不同的容忍度.我对此做了一个介绍:针对MySQL和PHP的读/写拆分(Percona网络研讨会2013)

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?

解决方案

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)

这篇关于MySQL复制是否具有即时数据一致性?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！