DATA AT THE CROSSROADS

The transactional tightrope: Balancing consistency and availability with ACID, BASE, CAP, and PACELC

Navigating the World of Database Transaction Models

14 min readJan 14, 2023

Database transactions are an important part of database systems that ensure that data is correct and reliable. Transactions are used to organize many database operations into a single unit of work, and they are meant to be atomic, which means that they are finished in their entirety, or they are not finished at all. To avoid data corruption or incomplete updates, the transaction concept keeps the database in a consistent state at all times.

Database transactions act in a certain way, and trade-offs must be considered when designing a database that follows different theorems and principles.

The Evolution of Transaction Models: ACID, BASE, CAP, and PACELC

At first, the designed systems worked well with relational databases that followed the ACID model.

The development of technologies and the requirements of systems (especially websites) caused that, after 2005, the capabilities of relational databases in terms of meeting the requirements of websites reached a limit.

As a result, new systems were created called NoSQL.

This was when Oracle was replaced by MySQL. At first, MySQL didn’t have many ways to grow, but a few tricks were used to get around these problems:

Memcached technology,
MySQL system replication,
sharding.

However, sharding has its drawbacks, and one, in particular, was to the detriment of the ACID model: the loss of operational integrity.

In other words, in most cases, ACID transactions are unreachable for many segments. This can be done with the Two-Phase Commit (2PC) protocol, but it takes a lot of work to resolve conflicts with this method.

Going further, Oracle tried to ensure that their database had the same scalability as MySQL with sharding without sacrificing the ACID model and operational capabilities. This led to RAC technology, a transparent, scalable relational cluster compliant with the ACID model. But this kind of cluster showed that a database that was compliant with ACID transactions wouldn't be able to meet the needs of websites. This was the milestone that led to the formulation of the CAP theory.

This is briefly about the history of the two key transaction models, ACID and CAP. There are also modifications to this, such as BASE and PACELC. I will discuss all of them in more detail below.

ACID

The acronym ACID stands for atomism, consistency, isolation, and durability. A database transaction’s properties are the traits that characterize its operation.

The ACID properties are as follows:

Atomicity: This attribute ensures that a transaction is either finalized in full or not at all and is, therefore, atomic. As a result, the database will be returned to its original state, and the entire transaction will be rolled back if even a single step fails.
Consistency: It is the responsibility of the database to ensure that each transaction that leaves the database does so in a consistent state, which means that it adheres to all of the rules and constraints that have been established for the database.
Isolation: This feature ensures that independent transactions will not conflict with one another. Thus, until a transaction is committed, its outcomes will remain hidden from all other transactions.
Durability: This feature ensures that every committed transaction will remain in the database even if there is a power outage or other system failure.

BASE

Basic Availability, Soft State, and Eventually Consistent are the BASE.

Regarding database transactions, the ACID model is a collection of properties that should always be met to guarantee the integrity and accuracy of data. However, implementing the ACID model in some database systems, such as distributed databases that need high availability and scalability, can be rather costly.

The BASE model was designed as an alternative to the ACID model for these sorts of systems.

One way to summarize the key distinction is as follows:

Both the BASE and ACID models guarantee reliable operations. In order to scale better and be more widely available, it compromises certain of the ACID model’s guarantees, perhaps at the expense of consistency.

Basic Availability: BASE-modelled NoSQL databases will maintain data availability by spreading and replicating it among the database nodes in a cluster rather than imposing instant consistency.
Soft State: Due to the absence of instant consistency, data values may alter over time. A developer should be concerned if the database isn’t consistent with itself.
Eventually Consistent: Consistency is achieved in the long run, even though the BASE model does not enforce it immediately. Until then, though, data reading remains a viable option.

CAP

Partitioning resilience was a serious issue in website systems, and what to do in the case of a network partition failure remained an open concern.

In light of this realization, the CAP theorem (also known as Brewer’s theorem) was formulated. A distributed database system cannot provide consistency, availability, and partition tolerance simultaneously. Since a distributed database system involves all three, compromises must be made among them.

Consistency: All nodes in the network receive the same information simultaneously, ensuring consistency.
Availability: Every request receives a response, even if the response is an error.
Partition tolerance: Tolerating disruptions in a distributed system’s communication due to partitions is called partition tolerance (a lost or temporarily delayed connection between two nodes). Any communication failures between nodes in a system shouldn’t affect the cluster’s ability to function. Hence the term partition tolerance describes this need.

Two CAP characteristics are used to categorize NoSQL databases:

CP Database (MongoDB): Availability is sacrificed in favor of consistency and partition tolerance while using a database. In the event of a partition between two nodes, the inconsistent node must be taken down until the partition is rectified.
AP Database (Cassandra): Nodes are still accessible when a partition occurs, but those on the incorrect end of the partition may return an outdated data set. When errors are fixed, the system is resynchronized to fix any remaining problems.
CA Database (PostgreSQL): It has replication capabilities and may be deployed across several nodes. It’s not logical since a system can’t have both consistency and availability if it isn’t partition-tolerant.

The CAP Theorem and its Justification (specifically applied to CA combination)

All nodes in the same sequence should get the same set of updates. A client may read from an out-of-date partition after reading from an up-to-date partition if the network loses a partition in between.

The only way to deal with the issue is to cease responding to requests from the outdated partition. However, this makes the service unavailable to users.

So… When a network partition is not there, what happens? If there is no partition, what options does a distributed system have?

The answer is PACELC.

PACELC

The PACELC theorem is a variation of the CAP theorem that adds the additional factor of latency. It states that the trade-off between consistency and latency in a distributed database system can be summed up as follows:

If the network is partitioned, the system can prioritize availability and sacrifice consistency, or the system can prioritize consistency and sacrifice availability; if the network is not partitioned, the system can prioritize low latency and sacrifice consistency, or the system can prioritize consistency and sacrifice low latency.

PACELC presumes high availability is maintained by replication. Hence in the event of a failure, CAP takes precedence, but if that doesn’t happen, we’ll still have to consider the trade-off between consistency and latency of the replicated system.

Dynamo and Cassandra

Both Dynamo and Cassandra are PA/EL systems, meaning that they prioritize availability over consistency during partitions, or else they choose lower latency.

BigTable and HBase

You may always pick consistency in PC/EC systems like BigTable and HBase, but then you’ll have to forego higher availability and lower latency.

MongoDB

MongoDB can be considered in the default config as PA/EC:

works in primary/secondary configuration,
DEFAULT CONFIG: all writes and reads are performed on the primary. As all replication is done asynchronously (from primary to secondaries), when there is a network partition in which the primary is lost on becomes isolated on the minority side, there is a chance of losing data that is unreplicated to secondaries. Thus there is a loss of consistency during partitions. In the case of a network partition, MongoDB chooses availability but otherwise guarantees consistency. Alternatively, when configured to write the majority replicas and read from the primary, it could be categorized as PC/EC.

Scenarios

ACID Scenarios

Here are some examples of ACID transactions.

One typical ACID transaction is moving money from one bank account to another. A debit operation takes money out of one account, and a credit operation puts money into another account in this transaction.

The accompanying list describes the ACID characteristics of this exchange:

Atomicity: If the debit or credit operation fails, the entire transaction is canceled, and the accounts are returned to their original statuses.
Consistency: This transaction will only go through if there is enough money in the account being debited, and if the credit operation goes through, the accounts will be left in a consistent condition (i.e., the total balance of the accounts will not change).
Isolation: With transaction isolation, numerous concurrent transactions cannot affect one another, and a transaction’s consequences are hidden from other transactions until it is committed.
Durability: A committed transaction will remain in the database even if there is a power outage or other disruption to the system.

The second example of what ACID transactions look like includes online shopping carts. Several steps are involved in the purchase process, including adding products to the shopping cart, changing the amount, and determining the final price.

The following are the ACID characteristics of this transaction:

Atomicity: The entire transaction will be rolled back, and the cart will be returned to its original state if even a single step fails, such as an item being out of stock or a mistake in the computation of the total cost.
Consistency: All products must be present for the purchase to be finalized, the right price must be computed, and the shopping cart must be left unchanged (i.e., the total cost will accurately reflect the items and quantities in the cart).
Isolation: A transaction’s outcomes are not visible to other transactions until after it has been committed, ensuring that even if numerous transactions occur simultaneously, they will not interfere (same rule as the previous example).
Durability: A committed transaction will be safely kept in the database and remain there even if there is a power outage or another system problem (same rule as the previous example).

BASE Scenarios

A cloud backup service utilizes a BASE transaction model-compliant distributed storage system.

Basic Availability: The system is built to be basically available (BA) so that users can still back up and get their data even if some servers go down.
Soft State: Because of the dynamic nature of distributed systems, the system’s state may evolve without user intervention, making it a soft state (S).
Eventually consistent: All servers may not have the same information simultaneously, but they will converge on a shared state over time, making it eventually consistent (E).
If a user uploads a new file, for instance, it may take a few minutes for the file to be synchronized across all servers. It may take some time for the data to become available on all servers, but the system can still handle backup and retrieval requests in the meanwhile.

CAP Scenarios

A social media platform’s distributed database operation may be considered a CAP transaction. Information about users, postings, and comments is stored across numerous nodes, and the system must ensure that this information is consistent and immediately accessible. The system’s CAP characteristics would be as follows:

Consistency: The system is consistent in the sense that all nodes have the same view of the data at the same time. Because of this, once a user makes a new post on any given node, that post will immediately be viewable on every other node.
Availability: All requests are answered, even if the answer is an error message. Because of this, if a user attempts to view their feed on a node and that node is unavailable, the request will be forwarded to another node, and the user will still receive a response.
Partition tolerance: This system is partition tolerant, meaning it can continue functioning even if some of its nodes go down. This implies the system will continue to work and respond to queries even if specific nodes are unavailable.

An e-commerce store uses a distributed database system as a CAP transaction. Multiple nodes contain information about items, orders, and stock. The system must guarantee the consistency and availability of this information while also making it quickly accessible.

The system’s CAP characteristics would be as follows:

Consistency: The system is consistent in the sense that all nodes have the same view of the data at the same time. This implies that when the stock of a product is changed on one node, it is updated everywhere in real-time.
Availability: All requests are answered, even if the answer is an error message. This ensures that users will always receive a response to their orders, even if a single node is temporarily unavailable.
Partition tolerance: (same rule as the previous example)

PACELC Scenarios

The PACELC model may be viewed as a ridesharing platform. The system must ensure the consistency of the data it stores among its many nodes, which include information about drivers, passengers, and trips, and make that information quickly accessible.

Here are some of the PACELC characteristics of this setup:

In the case of a partitioned network (P), the system must choose between prioritizing availability (A) above consistency or vice versa (C).
Depending on whether or not the network is partitioned (E), the system may give higher weight to low latency (L) and lower it for consistency, or vice versa (C).
For a ridesharing service, this involves striking a good middle ground between how quickly the client’s request is accepted and processed and how precisely the driver’s position is communicated.

The case of network partitioning (P):

Allowing users to request rides swiftly (A): Even if the closest driver’s position is slightly off, the algorithm prioritizes availability and allows the passenger to request a trip swiftly. Although the system’s reliability may suffer, it will be readily accessible and quickly respond to the user’s needs.
Keeping customers up-to-date on driver locations (C): In contrast, when the system is set to Consistency mode, the most recent information regarding the position of the nearest driver is displayed to the passenger. As a result, the system’s accessibility may be limited, but the user may count on reliable and accurate results.

Assuming there are no network partitions (E):

In a distributed system, the trade-off between high availability and low latency is analogous to that between high consistency and low latency.
Low latency in a ridesharing platform means that the passenger’s request is accepted and handled quickly. While having a short latency is desirable, it may be at the expense of reliability. For instance, if the system values low latency above all else, it may not wait for all nodes to update their data before recognizing the passenger’s request, which might result in inaccuracies regarding the driver’s position.
Alternatively, if the system values consistency above all else, then the passenger may have to wait a little longer for an acknowledgment of their request, but they will have access to the most up-to-date information possible on the whereabouts of their driver.
When a passenger seeks a trip in a crowded location, for instance, the ridesharing platform must balance the needs of the client with those of the drivers. Depending on the situation, the system can either quickly (low latency) acknowledge the client’s request and pair them with the nearest driver, even if the driver’s location information is slightly out of date, or it can wait for all nodes to update their data to guarantee that the passenger is paired with the most accurate driver location possible (high consistency).

Photo by Glenn Carstens-Peters on Unsplash

A PACELC transaction is analogous to a distributed database system for a video streaming. All user, video, and playlist information is stored across numerous nodes, and the system has to make it easy to get that information quickly while keeping it consistent.

In other words, this involves deciding between delivering a high-quality video stream on time (high availability) or ensuring that users receive the highest-quality video stream (high consistency).