ACID in Database – C for Consistency

Overview

When we talk about databases, we often mention the ACID properties—Atomicity, Consistency, Isolation, and Durability. These are key principles that help ensure that databases work reliably. Among these, Consistency is crucial for keeping the data correct and reliable. So, what exactly does Consistency mean, and why is it so important? We will discuss about this topic via this blog

What is Consistency?

Consistency ensures that a database remains accurate and valid after every transaction. It simply means that when a change is made, the database stays in a correct and stable state, following all the rules and constraints set up for the data. If a transaction is completed, the database should always be in a state where all its rules are still met.

How it works

Consistency is maintained through rules and constraints. Databases use rules to enforce data integrity. These rules are set up when the database is designed and are applied automatically by the system. Before a transaction is finalized, the database checks to ensure that it doesn’t break any rules. If it does, the system will undo the transaction to keep the database valid. When many transactions happen at the same time, systems use methods to prevent them from interfering with each other in ways that might harm Consistency. Techniques like locking and isolation help manage this.

For example

Think of an online store where customers place orders. If the database requires that every order must be linked to a valid customer and that items must be in stock, Consistency makes sure these rules are followed. If an item is out of stock, Consistency would prevent the system from processing the order until the stock is updated. This prevents mistakes like selling more items than are available.

Furthermore, in Bank transactions, imagine you are transferring money from one bank account to another using an online banking system. This operation involves 2 main actions: deducting the amount from the source account and adding the amount to the destination account. Let’s break down a bit how this process should work.

Initial state:

• Source account balance: $500
• Destination account balance: $300
• Amount to transfer: $100

Desired outcome:

• Source account balance $400
• Destination account balance: $400

We will have the following transaction steps:

1. Begin transaction: The bank starts the process of transferring $100 from the source account to the destination account.
2. Deduct from source account: The system deducts $100 from the source account. Now, the balance of the source account should be $400. An update statement like update account set balance = balance – 100 where id = ? will be executed.
3. Add to destination account: The system adds $100 to the destination account. Now, the balance of the destination account should be $400.
4. Commit transaction: If both steps are completed successfully, the system commits the transaction, making the changes permanent.

To ensure transaction validity, firstly, the transaction must be atomic, meaning that both operations (deduction and addition) must succeed together. If one part fails, the entire transaction is rolled back, and no changes are made. Suppose that, after deducting $100 from the source account, there is a system failure or error while trying to add the $100 to the destination account. In this case, the transaction is rolled back via undo log. The source account remains at $500, and no money is added to the destination account, preventing any inconsistency.

Before and after the transaction, the database must adhere to all defined rules. For instance, it should not allow an account balance to become negative (if such a rule exists). If the source account initially had only $50 and the system attempted to deduct $100, the transaction would be invalid because it violates the rule against negative balances. The system would prevent this transaction and ensure that the accounts remain in a valid state. After the transaction is completed and committed, even if the system crashes immediately afterward, the changes (deducting $100 from the source account and adding $100 to the destination account) will still be present when the system restarts.

Conclusion

Consistency is all about keeping data accurate and correct in a database. It ensures that every transaction maintains the database’s rules and integrity. By following Consistency, databases remain trustworthy and reliable, no matter how many changes or transactions occur.

In short, Consistency is like a rulekeeper for your data, making sure everything stays in order and correct.

Thank you for reading.

You can find related other topics below:

• Atomicity:  https://josysnavi.jp/2024/blog-00166
• Isolation: https://josysnavi.jp/2024/blog-00170