Understanding ACID in Database

ACID is a set of important properties in databases that ensure data integrity and reliability in transactions. The term ACID stands for four fundamental properties:

• Atomicity
• Consistency
• Isolation
• Durability

1. Atomicity

Atomicity ensures that a transaction is processed as an indivisible unit. If one part of the transaction fails, the entire transaction is rolled back, and the database returns to its state before the transaction began.

• Definition: A transaction is an indivisible unit. It is either completed entirely or not at all.
• Meaning: If an error occurs during transaction execution, all changes made before the error will be rolled back.
• Example: Transferring money from account A to B:
  • Deduct money from account A
  • Add money to account B → If either step fails, the entire transaction is cancelled.

Example of Atomicity:

BEGIN TRANSACTION;
    UPDATE accounts SET balance = balance - 100 WHERE account_id = 1;
    UPDATE accounts SET balance = balance + 100 WHERE account_id = 2;
COMMIT;

In this example, if either UPDATE command fails, the entire transaction is rolled back, ensuring account balances are not lost or partially updated.

Detailed Example of Failed Transaction:

-- Suppose we have an accounts table with initial data:
-- account_id | balance
-- 1         | 1000
-- 2         | 500

BEGIN TRANSACTION;
    -- Step 1: Deduct money from account 1
    UPDATE accounts SET balance = balance - 100 WHERE account_id = 1;
    -- After this step, account 1 has 900

    -- Step 2: Add money to account 2
    -- Suppose an error occurs here (e.g., account 2 doesn't exist)
    UPDATE accounts SET balance = balance + 100 WHERE account_id = 2;
    -- This command fails because account 2 doesn't exist

    -- Due to the error, the transaction automatically rolls back
    -- Account 1 returns to its initial balance of 1000
ROLLBACK; -- This command is executed automatically when there's an error

-- Check balance after rollback
SELECT * FROM accounts;
-- Result:
-- account_id | balance
-- 1         | 1000    -- Returned to initial balance

In the above example:

1. The transaction begins by deducting 100 from account 1
2. When trying to add 100 to account 2, the command fails
3. The system automatically rolls back the entire transaction
4. Account 1's balance returns to 1000 as initially
5. No changes are saved to the database

This ensures the atomicity of the transaction - either all operations succeed, or none are performed.

2. Consistency

Consistency ensures that the database is always in a valid state before and after each transaction. Constraints, foreign keys, and business rules must be followed.

• Definition: After a transaction completes, data must transition from one consistent state to another consistent state.
• Meaning: Transactions must comply with all database constraints, rules, and laws (such as primary keys, foreign keys, unique constraints...).
• Example:
  • Cannot add an order record without a corresponding customer in the customers table (due to foreign key constraint)
  • Total_amount must be positive
  • Required fields cannot be NULL

Example of Consistency:

Scenario: You have an orders table with a foreign key constraint customer_id referencing the customers table.

CREATE TABLE customers (
  id INT PRIMARY KEY,
  name VARCHAR(100)
);

CREATE TABLE orders (
  id INT PRIMARY KEY,
  customer_id INT,
  total_amount DECIMAL(10,2),
  FOREIGN KEY (customer_id) REFERENCES customers(id)
);

Invalid transaction (violates consistency):

START TRANSACTION;
INSERT INTO orders (id, customer_id, total_amount) VALUES (1, 999, -999); -- customer_id = 999 doesn't exist
COMMIT;

❌ MySQL will reject this transaction due to foreign key constraint violation → Consistency is protected.

When adding a new order, the system checks:

• Customer_id must exist in the customers table
• Total_amount must be positive
• Required fields cannot be NULL

3. Isolation

Isolation ensures that concurrent transactions do not affect each other. Each transaction is executed as if it were the only transaction running in the system.

• Definition: Transactions must execute independently, unaffected by other transactions running simultaneously.
• Meaning: Helps prevent "dirty read", "non-repeatable read", "phantom read" situations.
• Example: If two people are booking tickets simultaneously, the system must process so that each person sees the correct number of remaining tickets, avoiding duplication or discrepancies.

Isolation Levels in MySQL:

1. READ UNCOMMITTED: Allows reading uncommitted data (dirty read)
2. READ COMMITTED: Only allows reading committed data
3. REPEATABLE READ: Ensures repeatable reads for the same data (MySQL default)
4. SERIALIZABLE: Complete isolation, transactions are executed sequentially

Example of Isolation:

Scenario: Two users performing transactions simultaneously on the same data. Using two Sessions to illustrate.

-- Transaction 1
BEGIN;
SELECT balance FROM accounts WHERE account_id = 1; -- Reads 1000
-- Current balance is 1000
UPDATE accounts SET balance = balance - 100 WHERE account_id = 1;

-- Transaction 2
BEGIN;
SELECT balance FROM accounts WHERE account_id = 1; -- Still reads 1000 (REPEATABLE READ)

✅ Isolation ensures each transaction doesn't see changes from other transactions until they commit.

👉 You can control isolation level using:

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;

4. Durability

Durability ensures that once a transaction is committed, the changes are permanently stored, even if the system crashes.

• Definition: When a transaction is committed (completed), all its changes must be permanently recorded, even if the system shuts down unexpectedly.
• Meaning: Committed data will never be lost, even in case of hardware failure or power loss.
• Example: After a money transfer is completed and success message is shown, if the server loses power, the transferred amount will still be saved correctly.

Example of Durability:

Scenario: You record a new order and the system loses power right after showing the success message.

START TRANSACTION;
INSERT INTO orders (id, customer_id) VALUES (10, 1);
COMMIT;

✅ After COMMIT completes, even if the system shuts down unexpectedly, the data will not be lost.

Because:

• MySQL (with InnoDB) writes data to the log (redo log) and stores it safely before completing COMMIT.
• After the system restarts, the data still exists → Durability is ensured.

Mechanisms Ensuring Durability:

1. Write-Ahead Logging (WAL): Write logs before making changes
2. Double-Write Buffer: In InnoDB, data is written twice to prevent corruption
3. Redo Log: Stores changes for recovery after failures

ACID in MySQL

MySQL uses InnoDB as the default storage engine from version 5.5 onwards, and InnoDB is the only storage engine in MySQL that fully supports ACID.

ACID Configuration in MySQL:

-- Check storage engine
SHOW VARIABLES LIKE 'default_storage_engine';

-- Check ACID configuration
SHOW VARIABLES LIKE 'innodb_flush_log_at_trx_commit';

Benefits of ACID

1. Ensures Data Integrity: Data is always in a valid state
2. Better Error Handling: Automatic rollback on errors
3. Supports Complex Transactions: Can perform multiple operations in one transaction
4. Recovery After Failures: Data is protected from system failures

Drawbacks of ACID

1. Performance: ACID properties can impact performance
2. Scalability: Difficult to scale in some cases
3. Complexity: Requires more complex management

When to Use ACID?

ACID is suitable for applications:

• Requiring high data integrity
• Having many concurrent transactions
• Needing to ensure data consistency
• Processing financial, banking data

✅ ACID Summary:

✅ ACID Examples Table

Conclusion

ACID is a crucial foundation in database design, especially in systems requiring high data integrity. Understanding and correctly applying ACID principles helps build reliable and secure applications.

However, not all applications need to fully comply with ACID. In some cases, you may need to balance data integrity with system performance.

References

1. MySQL Documentation - ACID Compliance
2. InnoDB Transaction Model
3. Database System Concepts
4. High Performance MySQL