Database Consistency

Database Consistency Definition

Consistency in database systems refers to the requirement that any given database transaction must change affected data only in allowed ways. For a database to be consistent, data written to the database must be valid according to all defined rules, including constraints, cascades, triggers, or any combination. Consistency does not guarantee correctness of the transaction in all ways an application programmer might expect (that is the responsibility of application-level code). Instead, consistency merely guarantees that programming errors cannot result in the violation of any defined database constraints.
[See the discussion of database consistency in Martin Kleppmann’s Designing Data Intensive Applications book (free PDF)]

Image depicting database consistency by showing a database query returning consistent results faster with low latency.

Database Consistency FAQs

How does ACID Database Consistency compare with BASE Database Consistency?

In general, relational databases that support strong consistency provide ‘ACID guarantees’. ACID is an acronym designed to capture the essential elements of a strongly consistent database.

  • Atomicity: If any part of the transaction fails, the entire operation rolls back.
  • Consistency: The database remains structurally sound with every transaction.
  • Isolation: Each transaction is independent of other transactions.
  • Durability: All transaction results are permanently preserved.

ACID compliance is a complex and often contested topic. In essence, it designates the simple guarantee that a READ should return the result of the latest successful WRITE. While it might sound simple, such a guarantee is incredibly difficult to deliver in a globally distributed database topology that involves multiple clusters each containing many nodes.

For this reason, ACID compliant databases are generally very expensive and difficult to scale. Since SQL databases are all ACID compliant to varying degrees, they also share these downsides. Some RDBMS systems, it should be noted, enable ACID guarantees to be relaxed in the interest of offsetting these downsides. Achieving resilient, distributed SQL database deployments can be extraordinarily difficult and expensive For this reason, ACID compliant databases are generally used for financial transactions that must be accurately recorded.

In contrast to SQL’s ACID guarantees, NoSQL databases provide BASE guarantees.

  • Basic Availability: Data is available most of the time, even during a partial system failure.
  • Soft state: Replicas are not consistent all the time.
  • Eventual consistency: Data will become consistent at some point in time, with no guarantee when.

NoSQL databases sacrifice a degree of consistency in order to increase availability. Rather than providing strong consistency, consistency in NoSQL databases is more eventual than strong.

In a globally distributed NoSQL database deployment, there is the possibility that some transactions might overlap. In the classic example, a NoSQL database might permit a hotel room to be reserved on the same night by two different customers, one in Hong Kong and the other New York City. For many business applications, this is not a problem. The resilience, availability, and low-latency afforded by NoSQL is well worth the tradeoff. The hotel can provide perks to a disgruntled customer, but a regional outage could create significant churn.


Learn how the Raft consensus infrastructure enables radical new NoSQL capabilities: immediate consistency with zero overhead

Watch Video

How is Database Consistency related to the CAP Theorem?

Another meaning of Database Consistency is captured in the CAP Theorem. Originally formulated by Eric Brewer, the CAP theorem states that it is impossible for a distributed data store to simultaneously provide more than two of the following three guarantees:

  • Consistency: The same response is given to all identical requests
  • Availability: Requests receive responses even during a partial system failure
  • Partition Tolerance: Operations remain intact even when some nodes are unavailable.

According to the CAP theorem, any data store brings with it a trade-off. The critical trade-off that distinguishes relational and non-relational data stores is between availability and consistency. (Partition tolerance is often considered a requirement to achieve a compromise that allows eventual consistency and better scalability.)

Does ScyllaDB provide solutions for Database Consistency?

Like most non-relational databases, ScyllaDB implements a model known as “eventual consistency.” Eventual consistency supports the rapidly growing number of modern workloads that depend heavily on availability and are less dependent on guarantees of strong consistency. For example, during partitioning caused by an outage, it is often preferable for an isolated data center to continue to accept reads and writes.

Consistency in ScyllaDB is tunable — users can allow their transactions to have different degrees of consistency. Here are a few examples:

Consistency Levels

  • ONE: Successfully writing an update to any node is sufficient (the system will eventually replicate it to other nodes)
  • QUORUM: A majority of replicas (based on the replication factor) need to acknowledge an update
  • ALL: All replicas must acknowledge an update

While ScyllaDB does lean towards availability over consistency, it also offers an API for stronger consistency that leverages lightweight transactions (LWTs). LWTs enable database operators to align database consistency behavior with the requirements of a variety of workloads.

Lightweight transactions are limited to a single conditional statement, which enables an atomic “compare and set” operation. Such an operation checks whether a condition is true before it conducts the transaction. If the condition is not met, the transaction is not executed. (For this reason LWTs are sometimes called ‘conditional statements’.)

Rather than implementing locks, LWTs leverage a consensus protocol, Raft, to ensure that all nodes in the cluster agree on the change that has been committed. In this way, ScyllaDB can provide sufficient consistency for applications that also require the high availability, predictable low latency, and resilience that ScyllaDB offers.

Trending NoSQL Resources

ScyllaDB University Mascot

ScyllaDB University

Get started on your path to becoming a ScyllaDB expert.