ACID Properties

ACID properties ensure that database transactions are processed reliably and help maintain the integrity of the data in the face of failures or errors. Let's dive into each property:

Atomicity​

What is a Transaction?​

Imagine a scenario where Shubh wants to buy a book online. He finds one last copy available and adds it to his cart. During checkout, the payment fails. Now, Shubh hasn't received the book, but the store shows one less book in stock. This results in an inconsistent state of the database.

Atomicity ensures that every transaction is treated as a single unit. This means either all the operations within the transaction are executed, or none are. If any part of the transaction fails, the entire transaction is rolled back, restoring the database to its previous state.

Consistency​

Consistency ensures that the database remains in a valid state before and after a transaction. For example, if a customer decides to delete their account, their orders should also be removed to maintain consistency.

• Foreign Key Constraints: Ensure that the referenced record exists.

• Check Constraints: Validate data before allowing it into the database.

Without these checks, the database could enter an inconsistent state, leading to errors in operations and analysis.

Isolation​

What is Isolation?​

When multiple transactions occur simultaneously, isolation ensures that the execution of one transaction is independent of others. This prevents data from being read or modified in an inconsistent state.

Real-World Example: BookMyShow​

Consider a scenario where two users try to book the last available seat for a movie simultaneously. Proper isolation ensures that only one user can book the seat, maintaining consistency.

Types of Read Violations​

1. Dirty Read: Reading uncommitted data from another transaction.

   

   Dirty Read

2. Non-Repeatable Read: A transaction reads the same data twice but gets different results due to another transaction modifying the data.

   

   Non-Repeatable Read

3. Phantom Read: A transaction reads a set of records that meet certain criteria, but another transaction inserts or deletes records, causing different results in subsequent reads.

   

   Phantom Read

Isolation Levels​

1. Read Uncommitted: Allows dirty reads. Least strict but highest performance.

   SQL

   SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
   

2. Read Committed: Prevents dirty reads but allows non-repeatable reads.

   SQL

   SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
   

3. Repeatable Read: Prevents dirty and non-repeatable reads but allows phantom reads.

   SQL

   SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
   

4. Serializable: Most strict isolation level, ensuring complete isolation. No read violations are possible.

   SQL

   SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
   

Durability​

Durability ensures that once a transaction is committed, it will remain so, even in the case of a system crash. This is achieved through logging and recovery mechanisms:

1. In-memory Logging: Changes made by transactions are logged in memory.

2. Disk Persistence: Logs are periodically written to disk to ensure durability.

3. Recovery Process: In case of failure, the DBMS uses the logs to recover to a consistent state.

4. Transaction Replay: Committed transactions that were not written to the database at the time of failure are replayed to ensure consistency.

5. Rollback of Uncommitted Transactions: Any uncommitted transactions at the time of failure are rolled back.

These ACID properties are fundamental to maintaining the integrity and reliability of databases, ensuring that transactions are handled in a robust and error-free manner.