I understand that the consistency property specifies what values can be read, depending on what has been written before. For instance, if we consider a simple abstract read/write service. It enjoys the stronger form of consistency a.k.ak linearizability: one always read the last write. In weaker forms of consistency, you may read arbitrary old values.
In terms of implementation, I picture this as a leader / replica architecture where writes are performed on the leader, and are propagated to the replica. And read are performed on any replica. Because of the replication lag, we only get a weaker form of consistency.
NoSql DB are known to not enjoy linearizability, but only weaker form of consistency such as eventual consistency. I believe this is a simple consequence of the replication lag we get from the leader/replica architecture often used in these systems.
Here is where I'm confused. Common knowledge is:
- SQL ⇒ strict consistency
- NoSQL ⇒ eventual consistency
But SQL database can also be set up in a leader / replica to get better read performance. In that case, I assume they suffer from the same replication lag, and are thus eventual consistent.
Conversely, a NoSQL DB running on one node will be consistent.
I also understand that "consistent" is overriden, and is also used in the context of transactions. It hasn't the same meaning in ACID (characterizing transactions) and CAP.
Is the real dichotomy between SQL and NoSQL are the ability to perform ACID transactions, rather than the consistency model?