ACID Properties Explained Simply

ACID features are used by databases to maintain data integrity and consistency. Atomicity, Consistency, Isolation, and Durability (ACID) are basic concepts ensuring a database’s transactional integrity.

For developers working with relational database systems or NewSQL databases, understanding ACID characteristics is crucial.

What are ACID Properties?

ACID properties refer to four key principles – Atomicity, Consistency, Isolation, and Durability. They act as checks and balances for database transactions to ensure accuracy and reliability. (A transaction is a logical unit of work that accesses and updates the contents of a database.)

• Atomicity: Database transactions must be all or nothing. If the transaction fails, the entire transaction is rolled back. Atomicity prevents partial and incomplete transactions.
• Consistency: Only valid data is written to the database. Consistency enforces integrity constraints to maintain the accuracy and correctness of data.
• Isolation: Transactions are run independently. Changes in one transaction will not impact others until committed. Isolation maintains data integrity across concurrent transactions. 
• Durability: All committed transactions are permanently recorded in the database. They persist even after system failure. Durability provides recoverability.

Adhering to these four properties guarantees database transactions’ reliability, accuracy, and consistency. The ACID principles are core mechanisms to manage the complexities of transactions, failures, and concurrency in database systems.

A – Atomicity 

Atomicity requires transactions to be treated as a single “unit of work.” The entire sequence of transaction operations succeeds or fails as one entity. There is no partial success or failure.  

For example, transferring money from one bank account involves multiple steps:

• Debit amount X from Account A 
• Credit amount X to Account B

As per atomicity, either all debit and credit operations succeed or they all fail. If the debit succeeds, but the credit fails for any reason, the entire transaction is rolled back. Atomicity ensures there are no partial or incomplete transactions.

Atomicity prevents unwanted data updates from unconcluded transactions. Without it, the debit may persist while the credit fails, causing data inconsistency. By reverting partial transactions, atomicity keeps the database consistent.

C – Consistency 

The consistency property ensures that only valid data is written to the database. Before committing a transaction, consistency checks are performed to maintain database constraints and business rules.

For example, a transaction crediting 5000 to a bank account with a current balance of 3000 is invalid if the account has an overdraft limit of 1000. The transaction violates consistency by exceeding the permissible account limit. Hence, it is blocked and aborted.

Consistency enforcement prevents data corruption and invalid entries. Only transactions abiding by consistency rules are committed to upholding data accuracy. Real-world business constraints are modeled into the database via consistency.

I – Isolation

Isolation maintains the independence of database transactions. Uncommitted transactions are isolated with locking mechanisms to prevent dirty reads or lost updates.  

For instance, if Transaction T1 updates a row, Transaction T2 must wait until T1 commits or rolls back. Isolation prevents T2 from reading unreliable data updated by T1 but not committed yet.

Isolation avoids concurrency issues like:

• Dirty reads – Reading uncommitted data from other transactions
• Lost updates – Overwriting another transaction’s uncommitted updates 
• Non-repeatable reads – Same query yielding different results across transactions

By isolating transactions, consistency is maintained despite concurrent execution and updates. Changes remain isolated until permanent.

D – Durability

Durability provides persistence guarantees for committed transactions. The system upholds the changes once a transaction is committed even if it crashes later. Durability is achieved with database backups, transaction logs, and disk storage.

For example, if a transaction updates a customer’s address, durability ensures the updated address is not lost due to a hard disk failure or power outage. The change will persist with the help of storage devices, backups, and logs.

Durability ensures that transactions, once committed, will survive permanently. Failed hardware, power loss, and even database crashes will not undo committed transactions due to durability support.

Real-World Example of ACID Transactions

Imagine a customer is purchasing a book from an online bookstore. Here’s how the transaction unfolds, step by step, with each ACID property highlighted:

1. Atomicity

• Action: The customer clicks the “Buy Now” button to purchase a book.
• ACID Application:
  • The transaction includes deducting the book’s price from the customer’s account and updating the store’s inventory to reflect the sale.
  • Atomicity ensures that either both actions (payment and inventory update) occur, or neither does.
  • If something goes wrong during the transaction (e.g., the payment process fails), the entire transaction is aborted, and the inventory is not updated.

2. Consistency

• Action: The payment is processed, and the inventory is updated.
• ACID Application:
  • Before the transaction, the book is listed as available, and the customer’s account balance is sufficient.
  • Consistency ensures that the database remains in a valid state: the book is now marked as sold, and the customer’s account balance is reduced by the corresponding amount.
  • The system verifies that all business rules are adhered to (like not allowing the account balance to go negative).

3. Isolation

• Action: Multiple transactions are occurring simultaneously (other customers making purchases).
• ACID Application:
  • Isolation ensures that each transaction is isolated from others: the customer’s transaction doesn’t see the intermediate states of other ongoing transactions.
  • For instance, if there’s only one copy of a book left and two customers try to buy it at the same time, isolation prevents the system from selling it twice.

4. Durability

• Action: The transaction completes successfully – the payment is processed, and the book’s inventory status is updated.
• ACID Application:
  • Durability guarantees that once a transaction is committed, it is permanent, even in the event of a system crash.
  • The changes made by the transaction (payment and inventory update) are permanently recorded in the database. The customer can see the updated account balance and order history even if they log out and log back in.

ACID properties work together to ensure that the online shopping transaction is processed reliably, accurately, and efficiently, maintaining the integrity and consistency of the database at all times.