Posted in

ACID (SQL) and BASE (NoSQL) in databases

Why, in the cloud era, do we still keep these monolithic IBM Power Machines in our data centers? Isn’t it cheaper to rewrite everything into microservices and scatter it across thousands of cheap x86 instances?

This question comes up at almost every infrastructure modernization meeting. From the perspective of an Excel spreadsheet, the answer seems obvious. However, from the perspective of someone who knows what happens when two processes try to modify the same account balance simultaneously, the matter looks completely different. It is not a question of sentiment for “old iron,” but a fundamental choice between two philosophies that rule the data world: ACID and BASE. Understanding this IT “chemistry” is the only way to avoid an explosion in the production environment.

ACID: The Foundation of Security

In the world of transaction systems, the term ACID is synonymous with reliability. Its etymology stems from the English idiom “acid test,” a historical method for verifying the authenticity of gold. In IT, this means a ruthless test that a system must pass before we can entrust it with customers’ money. This model is based on four pillars:

1. Atomicity: All or Nothing The transaction must be executed in its entirety or not at all. There is no room for intermediate states.

  • Failure Scenario: Imagine a bank transfer without atomicity. The system deducts 1000 PLN from the sender’s account, but a fraction of a second later, a server failure occurs before the funds are credited to the recipient’s account.
  • Consequence: After a restart, the system considers the debit operation complete, but the credit operation never happened. Money simply vanishes from the system—neither the sender nor the recipient has it. Atomicity guarantees that in such a situation, the system will perform a “rollback” and undo the first operation as if it never happened.

2. Consistency: The Guardian of Rules This is often the most misunderstood element. It’s not about compliance with the actual state of the real world (the database doesn’t know if a transfer is “justified”), but about ironclad compliance with internal database rules (e.g., data types, foreign keys).

  • Failure Scenario: The database has a rule that a balance cannot be negative. Without enforced consistency, due to an application error, the database might accept a withdrawal operation of 200 PLN from an account holding only 100 PLN.
  • Consequence: Logical chaos ensues. We have a negative balance where the system doesn’t anticipate it, or two users with the same identifier. The data becomes technically “dirty” and unprocessable by further processes.

3. Isolation: The Illusion of Exclusivity Every transaction takes place in separation, even with hundreds of parallel processes. The system pretends to process requests one after another.

  • Failure Scenario: Two people simultaneously click “Buy Now” for the last available cinema ticket. Without isolation, the system checks availability for both processes in the same millisecond and returns a “free” status to both.
  • Consequence: Two tickets are sold for the same seat (a so-called Race Condition). In banking, this leads to situations where financial reports generated during the day include data from transactions that are about to be rolled back.

4. Durability: Written in Stone A guarantee that once an operation is confirmed, the data is physically safe and will survive a power failure.

  • Failure Scenario: A cash deposit machine accepts cash and displays a “Posted” message, but the data resides only in the server’s volatile RAM. A second later, the machine restarts.
  • Consequence: The customer has physical confirmation of the deposit, but there is no trace of it in the database. Durability forces a costly operation of physically writing to the disk before sending a confirmation to the client.

Are IBM Power and Mainframe Servers Still in the Game?

Meeting all four ACID requirements is extremely computationally expensive. Isolation requires record locking, and durability forces waiting for slow disk I/O operations. This makes ACID systems scale poorly horizontally (it is difficult to maintain this rigor when data is scattered across 1,000 cheap servers).

This is where the role of IBM Power architecture comes in. Banks, in accordance with the CAP theorem, always choose Consistency at the expense of Availability. We prefer the banking system to stop responding for a moment rather than show an incorrect balance. Mainframe and Power class machines are designed for vertical scaling (a large amount of CPU and RAM resources can be allocated to a single VM/LPAR, and the architecture allows for extreme I/O performance). They can handle a gigantic volume of transactions in a single, coherent memory environment, minimizing the overhead associated with synchronization—overhead that would kill the performance of a distributed x86 cluster under such a consistency regime.

Not Just Banking

While banking is the prime example, the ACID model is equally critical in Supply Chain Management and ERP systems.

Imagine a large, automated warehouse for a pharmaceutical or automotive company. The operation of moving a pallet of goods from “Location A” to “Location B” is a classic transaction that must be atomic. If the system removes the goods from inventory at point A but fails to add them at point B due to a failure, “phantom inventory” is created—it physically exists, but the system doesn’t see it.

Isolation is equally crucial here. In the case of critical parts (e.g., aircraft engines), we cannot allow a situation where the same unique serial number is assigned to two different service orders simultaneously. In such environments, “approximate knowledge” of inventory status is worthless. Engineers maintaining these systems choose IBM Power architecture because the cost of a production line stoppage due to data inconsistency often exceeds the cost of the infrastructure itself.


BASE: The Answer to Internet Scale

However, when we move beyond critical systems and enter the world of e-commerce giants (Amazon, Google), priorities flip. Here, scale is key. The requirement to serve millions of users forced the abandonment of rigid ACID in favor of the BASE model—an acronym that serves as a technological joke on “acid” chemistry. BASE stands for:

Basically Available The system always responds to a user request, even if part of the infrastructure has failed or the data is incomplete. The priority is keeping the system “alive,” not the correctness of the answer.

  • Failure Scenario: Imagine a failure of part of the database cluster in an online store during Black Friday. In the ACID model, the store would have to pause sales to prevent inventory inconsistency.
  • Consequence: In the BASE model, the system switches to survival mode. The customer sees the product as “Available” (using a local copy of data or cache), even though the item might have sold out 5 minutes ago. The store prefers to risk having to cancel the order later (“sorry, warehouse error”) than to show the customer a 503 error page and lose guaranteed traffic.

Soft State The system state is fluid and can change without any interaction from the user, resulting solely from the passage of time and background synchronization processes.

  • Scenario: A user puts a product in the cart in a mobile app and minimizes it without performing any action. Meanwhile, in the background, the node the phone is connected to receives delayed information from the rest of the cluster that the session has expired or the price has changed.
  • Consequence: Even though the user didn’t click any button, the content of their cart or the price changes “by itself” upon reopening the app. In ACID systems, the state is “hard” (changes only upon explicit transaction request); in BASE, the state evolves, striving for consistency, making the data seem “soft” and unstable in the short term.

Eventual Consistency This is the heart of the model. We do not guarantee consistency “here and now.” We only promise that if we stop making changes, after some time, all servers in the world will agree on one version of the truth.

  • Scenario: A popular influencer publishes a post on social media. In the first second, 1,000 people see the post and click “Like.” A user in Poland sees “5 likes” under the post, while a user in the USA sees “300 likes.”
  • Consequence: Every user operates on a different version of reality. The system does not lock the counter for every update (which would kill performance) but allows for a temporary “lie.” Only after a moment (when the servers “gossip” the changes among themselves) will everyone see the same number. In banking, such a situation (seeing a different balance depending on the server) would be unacceptable; in social media, it is the standard.

In social media, a delayed counter is just a minor inconvenience. In banking or ERP systems, such eventual consistency is unacceptable.

Engineering Pragmatism Over Dogma

The decision between ACID and BASE should stem from an analysis of business requirements, not the technological habits of the team. If you are designing an accounting system, warehouse inventory, or payment processing, the ACID model remains unrivaled because the cost of error or data inconsistency is unacceptable here. No amount of flexibility compensates for the lack of certainty regarding an account balance. Platforms like IBM Power support this model perfectly, offering the computational power necessary to quickly process complex, locking transactions.

Conversely, the BASE model is the natural choice for big data analytics, caching systems, or content aggregators, where a single lost record or a few seconds of delay in synchronization causes no financial loss. In such cases, the overhead associated with locking resources and enforcing immediate consistency would be a waste of computational resources and a bottleneck for the end user.

The most effective strategy in modern IT, especially in banking and e-commerce, is becoming hybrid architecture. It is the answer to today’s market needs, where we demand pharmaceutical precision from ourselves (ACID) while customers expect immediate 24/7 availability (BASE). In practice, this means building two-tier systems:

  1. Core: Based on solid relational databases running on reliable IBM Power servers. This is where the “accounting of truth,” financial operations, inventory states, and critical data take place—data that must be consistent and durable, regardless of performance costs.
  2. Edge: The customer service layer based on lightweight NoSQL databases (BASE). Data from the core is replicated to this layer in an “eventual consistency” model. A customer logging into a mobile app uses a fast copy of the data (BASE view), guaranteeing instant reaction time and continuity of operation even under heavy load.

Such a division of roles allows us to “have our cake and eat it too”: we meet the rigorous requirements of auditors thanks to ACID at the heart of the system, while simultaneously offering customers the agility and responsiveness they have learned to expect from tech giants. An effective architecture is one that does not fight this duality but utilizes the strengths of both worlds.

Sources: