ACID Properties

 9 ACID 

ACID is a set of properties that guarantee the reliability of database transactions.

The acronym ACID stands for Atomicity, Consistency, Isolation, and Durability. These

properties ensure that database transactions are processed reliably, even in the event

of errors, power failures, or other disruptions.

Definition of Atomicity, Consistency, Isolation, Durability

Let's explore each of the ACID properties in detail:

1 - Atomicity

Atomicity ensures that a transaction is treated as a single, indivisible unit of work.

Either all of the transaction's operations are completed successfully, or none of them

are applied.

If a transaction fails, the database is left unchanged, as if the transaction had never

been started.

2 - Consistency

Consistency ensures that a transaction brings the database from one valid state to another. 

Any data written to the database must be valid according to all defined rules, including constraints, cascades, triggers, and any combination thereof.

This maintains data integrity and ensures that any corruption or inconsistency in the data is avoided. 

Note that consistency in ACID is not the same concept as consistency from a CAP perspective.

Example of Consistency in a Bank Transaction:

Let’s say there is a bank transaction where $500 is transferred from Account A to Account B:

• The consistency rule might state that the total amount of money in the system (across all accounts) should remain the same after any transaction.

• The transaction involves debiting $500 from Account A and crediting $500 to Account B.

• If any part of this transaction violates the consistency rule (e.g., if the debit occurs but the credit doesn’t), the entire transaction would fail, and the system would revert to the previous consistent state, leaving both accounts unchanged.

3 - Isolation

Isolation ensures that concurrent transactions do not interfere with each other. Each transaction must be executed in isolation from other transactions so that the intermediate state of one transaction is not visible to other transactions.

This prevents issues such as dirty reads, non-repeatable reads, and phantom reads.

Example of Isolation in a Bank Transaction:

Let’s say there is a bank transaction where $500 is transferred from Account A to Account B.

Another user is attempting to transfer $200 from Account A to Account C at the same time. 

• Isolation Ensures: The two transactions won’t interfere with each other. 
• Even though they are executed simultaneously, isolation ensures that one transaction will complete before the other one starts affecting the database. 

• The final balance in Account A will reflect the correct outcome of both transactions, avoiding race conditions or incorrect balances.

4 - Durability

Durability ensures that once a transaction has been committed, it will remain committed even in the event of a system failure. 

The changes made by the transaction must be permanently stored in the database and not be lost due to any failure.

This is typically achieved through the use of transaction logs and database backups.

Example:

After successfully transferring $500 from Account A to Account B, the system crashes due to a power outage.

Durability Ensures: Once the transaction is committed, the changes to both Account A and Account B (i.e., debit and credit of $500) are permanently saved. When the system is restored, the database will reflect the completed transaction. The data is safe and won’t be lost or reverted.

Complete Example with ACID Properties:

Let’s assume:

• Account A has a balance of $1,000.

• Account B has a balance of $500.

A transaction is initiated to transfer $500 from Account A to Account B:

1. Atomicity:

• If the debit from Account A ($1,000 → $500) succeeds but the credit to Account B ($500 → $1,000) fails, the entire transaction is rolled back, and Account A still has $1,000, Account B still has $500.

2. Consistency:

• After the transaction, Account A has $500 and Account B has $1,000, maintaining the rule that the total balance (sum of both accounts) is still $1,500. No integrity rules are violated.

3. Isolation:

• If another transaction is trying to withdraw money from Account A at the same time, the isolation property ensures that one transaction will complete before the other starts, so there is no inconsistency in the account balances.

4. Durability:

• After the transaction is committed, even if the system crashes or shuts down, the changes to Account A and Account B will persist when the system comes back online.

Relevance of ACID to Transactional Databases

ACID properties are essential for transactional database systems, such as relational databases (e.g., MySQL, PostgreSQL, Oracle) and some NoSQL databases (e.g., MongoDB, FoundationDB). 

These systems are designed to handle critical data and ensure data integrity, consistency, and reliability.

Transactional database systems are used in applications where data accuracy is paramount, such as financial systems, e-commerce platforms, and healthcare applications. 

By guaranteeing ACID properties, these systems ensure that data remains consistent and reliable, even in the face of failures or concurrent access by multiple users.

Challenges of Maintaining ACID Compliance in Distributed Databases

Maintaining ACID compliance in distributed databases can be challenging due to the nature of distributed systems. 

Some of the key challenges include:

• Network Partitions and Failures: Distributed systems are prone to network partitions and node failures, which can make it difficult to maintain consistency and availability simultaneously.
• Latency: Ensuring ACID properties across multiple nodes can introduce latency, as transactions may need to be coordinated and synced across the network.
• Scalability: As the number of nodes and transactions increases, maintaining ACID properties can become more complex and resource-intensive.