3.2 ACID Properties

Level: 3 | Version: v1.1 | Author: Meptrasoft

Overview

ACID properties help ensure that database transactions are reliable, correct, and safe.

ACID stands for:

A → Atomicity
C → Consistency
I → Isolation
D → Durability

A simple way to remember them:

Atomicity → All or nothing
Consistency → Valid data
Isolation → Transactions work independently
Durability → Committed changes stay saved

Atomicity

Atomicity means a transaction is treated as one complete unit of work.

Either all required operations succeed, or the transaction is rolled back.

Consider a bank transfer:

Subtract ₹1,000 from Ravi
        +
Add ₹1,000 to Anjali
        ↓
   ONE TRANSACTION

If the second operation fails, the first change should also be undone.

Example

BEGIN;

UPDATE accounts
SET balance = balance - 1000
WHERE account_id = 1;

UPDATE accounts
SET balance = balance + 1000
WHERE account_id = 2;

ROLLBACK;

After ROLLBACK, both changes are undone.

Atomicity = All or nothing.

Consistency

Consistency means a transaction should move the database from one valid state to another valid state while preserving defined rules and constraints.

For example, suppose an account cannot have an invalid negative balance according to the application's rules.

A successful transaction should not leave the database in an invalid state.

VALID STATE
    ↓
TRANSACTION
    ↓
VALID STATE

Consistency is maintained through database constraints, transaction rules, application logic, and other controls.

Consistency = The database remains valid before and after the transaction.

Isolation

Isolation means concurrent transactions should not improperly interfere with each other.

Imagine two transactions running at the same time:

Transaction A
    ↓
Database

Transaction B
    ↓
Database

The database system manages their interaction so that intermediate work from one transaction does not incorrectly affect another.

For example, a transaction may make changes internally before it commits. Other transactions should see data according to the database's isolation rules, rather than seeing incomplete work.

Isolation = Concurrent transactions are controlled so they do not produce incorrect interference.

Durability

Durability means that once a transaction is successfully committed, its changes are preserved even if a system failure occurs afterward.

For example:

TRANSACTION
    ↓
COMMIT
    ↓
DATA SAVED
    ↓
SYSTEM RESTART
    ↓
COMMITTED DATA REMAINS

Database systems use mechanisms such as persistent storage and recovery techniques to provide durability.

Durability = Once committed, changes are not lost because of a normal system restart or crash.

ACID in One Example

Consider transferring ₹1,000 from Ravi to Anjali.

BEGIN
  ↓
Subtract ₹1,000 from Ravi
  ↓
Add ₹1,000 to Anjali
  ↓
COMMIT

The four properties apply as follows:

Practice Table

This topic uses the accounts table from 3.1 Database Transactions.

If the table is not already available, use:

CREATE TABLE accounts (
    account_id INT PRIMARY KEY,
    account_holder VARCHAR(50),
    balance NUMERIC(10, 2)
);

INSERT INTO accounts (account_id, account_holder, balance) VALUES
(1, 'Ravi Kumar', 5000.00),
(2, 'Anjali Mehta', 3000.00);

Example: Atomicity in Action

Start a transaction and perform both account updates:

BEGIN;

UPDATE accounts
SET balance = balance - 1000
WHERE account_id = 1;

UPDATE accounts
SET balance = balance + 1000
WHERE account_id = 2;

ROLLBACK;

Because the transaction is rolled back, both updates are undone.

The balances remain:

This demonstrates the all-or-nothing nature of atomicity.

COMMIT vs ROLLBACK

The difference is important:

COMMIT
→ Make the transaction's changes permanent

ROLLBACK
→ Undo uncommitted changes

Example:

BEGIN;

UPDATE accounts
SET balance = balance - 500
WHERE account_id = 1;

COMMIT;

The change is committed and becomes part of the database state.

With:

ROLLBACK;

uncommitted changes made during the transaction are undone.

Why ACID Matters

ACID properties are especially important when incorrect or partial updates could cause serious problems.

Examples include:

• Banking
• Payment processing
• Ticket booking
• Inventory management
• E-commerce orders

For example, a payment system should not record a successful payment while failing to correctly update the related order state.

ACID is important wherever database operations must be reliable and consistent.

Common Beginner Mistakes

Placement Quick Points

A → ATOMICITY
  → ALL OR NOTHING

C → CONSISTENCY
  → VALID STATE BEFORE AND AFTER

I → ISOLATION
  → CONTROLLED CONCURRENT TRANSACTIONS

D → DURABILITY
  → COMMITTED DATA PERSISTS

• Atomicity ensures a transaction is treated as one unit of work.
• Consistency keeps the database within its defined valid rules and constraints.
• Isolation controls how concurrent transactions interact.
• Durability preserves committed changes after system failures.
• ACID properties are fundamental to reliable transaction processing.
• COMMIT saves a transaction's changes.
• ROLLBACK undoes uncommitted changes.

Interview Questions

What does ACID stand for?

ACID stands for:

TEXT

Atomicity
Consistency
Isolation
Durability

These properties help make database transactions reliable.

What is Atomicity?

Atomicity means that all required operations in a transaction succeed together, or the transaction is rolled back.

What is Consistency?

Consistency means a successful transaction leaves the database in a valid state that satisfies its defined rules and constraints.

What is Isolation?

Isolation means concurrent transactions are controlled so that one transaction does not improperly interfere with another.

What is Durability?

Durability means that once a transaction is committed, its changes remain stored even if a system failure occurs afterward.

Give a real-world example of Atomicity.

A bank transfer is a common example. The money must be deducted from one account and added to another as one logical operation.

Why is Isolation important?

Without proper isolation, concurrent transactions could see or depend on incomplete changes and produce incorrect results.

What happens to a transaction after COMMIT?

Its changes are committed to the database and become part of the durable database state.

What is the difference between COMMIT and ROLLBACK?

COMMIT saves the transaction's changes. ROLLBACK undoes uncommitted changes made by the transaction.

Practice & Hands-On Exercises

Beginner Practice

1. What does each letter in ACID stand for?
2. Explain Atomicity using a bank-transfer example.
3. Explain Consistency in your own words.
4. Why is Isolation important when many users access the same database?
5. What does Durability guarantee after a successful COMMIT?

Hands-On

Using the accounts table:

6. Start a transaction and transfer ₹500 from Ravi to Anjali:

BEGIN;

UPDATE accounts
SET balance = balance - 500
WHERE account_id = 1;

UPDATE accounts
SET balance = balance + 500
WHERE account_id = 2;

COMMIT;

7. Verify both balances:

SELECT * FROM accounts;

8. Start another transaction and transfer ₹1,000.
9. Roll back the transaction and check whether the balances changed.
10. Explain which ACID property is demonstrated by the rollback.

Think and Answer

11. What could happen if Atomicity were not maintained during a bank transfer?
12. Why should a committed payment remain available after a server restart?
13. Give one example where two transactions may run concurrently.

💡 Tip: Test your queries using the in-browser interactive runner above.

Key Takeaway

A → ALL OR NOTHING
C → VALID DATABASE STATE
I → CONTROLLED CONCURRENCY
D → COMMITTED DATA PERSISTS

ACID properties make database transactions reliable by ensuring complete execution, valid database states, controlled concurrent access, and persistence of committed changes.

End of ACID Properties