Inconsistency in Relational Databases

I generally get confused when the term consistency is used. NoSQL tutorials always refer to the reads whereas Relational tutorials refer to a consistent state (ensuring referential integrity constraint)

When the data is distributed across multiple servers (ex
1 master- n slaves configuration)

• Do relational databases ensure consistency in reads? I mean a committed write is immediately available to be read by other transactions? I suspect not given network is involved, making Relational DBs as eventual consistent DBs.

• Do relational databases ensure referential integrity constraint well?

To directly answer your questions:

• "Do relational databases ensure consistency in reads?"

A: In short, when the ACID principals, and specifically the Consistency part, were defined, this was long before distributed data was a concept for relational databases. So the Consistency from that standpoint was automatic because there's only a single server at play. Once a transaction was committed, the server was immediately Consistent with itself, regarding that transaction and all constraints associated with it, there were no other servers to synchronize and commit to. (Note this only focusses on one part to the meaning of Consistency in the ACID principals for the relevancy of OP's question regarding distributed data.)

Nowadays there's a multitude of ACID compliant relational database systems out there that are designed to handle distributed data across multiple servers. But let's take even Microsoft SQL Server and their AlwaysOn Availability Groups feature as an example for a moment. This feature is meant to be a High Availability / Disaster Recovery feature by synchronizing data from your primary server to other secondary servers. It's also ACID compliant because it can be configured to ensure Consistency across servers (when set to Synchronous mode). In a very rudimentary explanation on how it works is it only fully commits a transaction once that transaction has been synchronized to all secondary servers from the primary server. This guarantees Consistency across all servers, at all times, and allows it to remain ACID compliant in a distributed server environment.

• "Do relational databases ensure referential integrity constraint well?"

A: There's not really a way to quantifiably answer that question, but generally speaking, yes they do. Referential Integrity is one of the main points of using a relational database. When there's a well relationally defined schema, with proper constraints defined, such as foreign keys for example, a relational database is guaranteed to always enforce the rules of those constraints to ensure it always enforces the appropriate referential integrity. This ties into the definition of Consistency and how a relational database is ACID compliant.

StackExchange Database Administrators Q#290803, answer score: 4