ACID Properties in DBMS

ACID properties in DBMS ensure reliable and secure transaction processing by maintaining data integrity. They include Atomicity, Consistency, Isolation, and Durability, which together prevent errors and ensure correctness. A transaction is a set of operations that form a logical unit of work, where any failure results in the entire transaction being rolled back to its previous safe state.

Everyday example: transferring money. Withdrawing or transferring funds via ATM/online updates your bank’s database. A transaction captures that whole process as one reliable unit.

How a Transaction Runs (Step-by-Step)

  1. READ: Fetch required rows/pages from the database disk into main memory.

  2. PROCESS (in memory): Apply business logic (e.g., subtract or add amounts).

  3. WRITE (to memory/buffer): Update in-memory copies (not yet permanent).

  4. COMMIT: Persist changes to disk—now they are permanent.

  5. ROLLBACK (if needed): Undo in-progress changes and restore the last committed state.

Note: COMMIT = make changes permanent. ROLLBACK = undo uncommitted work.

Quick Bank Transfer Example (A → B, Rs. 1000)

BEGIN TRANSACTION
READ A.balance -> a
READ B.balance -> b
a = a - 1000
b = b + 1000
WRITE A.balance = a
WRITE B.balance = b
COMMIT
-- If any error before COMMIT: ROLLBACK

Why ACID Matters

To keep the database accurate, complete, and consistent before and after execution, transactions follow the ACID properties:

  1. Atomicity

  2. Consistency

  3. Isolation

  4. Durability

Let’s unpack each one, in simple terms.

1) Atomicity — “All or Nothing”

  • Meaning: Either all operations in the transaction happen, or none do. No half-done results.

  • Behavior: Start at the first step (read), end at the last step (commit). If anything fails—even at 99.9% progress—ROLLBACK must undo everything.

  • Bank transfer: Debiting A must always be paired with crediting B. If credit to B fails, debit to A must be undone.

Key idea: Partial success is not allowed.

2) Consistency — “Valid In, Valid Out”

  • Meaning: A transaction must move the database from one valid state to another, preserving all rules/constraints (like primary keys, foreign keys, checks, and domain rules).

  • Simple rule for transfers:
    Let SUM1 = A + B before; SUM2 = A + B after.
    For a pure transfer, SUM1 = SUM2 (money is neither created nor destroyed).

Example:

  • Before: A = 1500, B = 3000 → SUM1 = 4500

  • Transfer 1000 from A to B

  • After: A = 500, B = 4000 → SUM2 = 4500  Consistent

If A is debited but B isn’t credited, the system is inconsistent. Solution: ROLLBACK.

3) Isolation — “No Cross-Talk”

  • Meaning: Running transactions at the same time must not interfere with each other’s intermediate data.

  • Intuition: It should appear as if transactions ran one by one (serially), even if the system executed them in parallel.

Parallel vs. Serial

  • Parallel schedule: Multiple transactions run at once.

  • Serial schedule: One completes fully, then the next begins.
    Isolation aims to make parallel execution equivalent to some serial order.

Isolation Problems (What we must avoid)

  • Dirty Read: T2 reads uncommitted changes of T1.

  • Non-Repeatable Read: T1 reads a row twice, gets different values because T2 modified/committed in between.

  • Phantom Read: T1 reruns a query and sees new rows inserted by T2 that match the same condition.

Common Isolation Levels (simple view)

  • Read Uncommitted: Allows dirty reads (weakest).

  • Read Committed: No dirty reads; non-repeatable reads still possible (common default).

  • Repeatable Read: Prevents dirty & non-repeatable reads; phantoms may occur (DB-dependent).

  • Serializable: Prevents dirty, non-repeatable, and phantom reads (strongest, but slowest).

Practical tip: If two transactions touch different accounts/tables, they shouldn’t affect each other. If they touch the same account/rows, proper locking/versioning is needed—or run them serially.

4) Durability — “It Stays”

  • Meaning: After a COMMIT, the changes survive crashes, power loss, or restarts.

  • How systems ensure this: Write-ahead logs (WAL), journaling, replication, and flushing to stable storage.

Note: COMMIT makes data permanent. It won’t “auto-revert” unless another new transaction changes it.

Putting It Together: One Clean Flow

  1. Start transaction.

  2. Read all needed data.

  3. Compute updates (in memory).

  4. Write updates (buffers).

  5. Validate constraints (to keep Consistency).

  6. Lock/isolate as needed (to ensure Isolation).

  7. Commit to durable storage (for Durability).

  8. If anything fails at any point → Rollback (to ensure Atomicity).

Common Issues and How to Prevent Them

  • Partial updates: Always wrap multi-step changes in an explicit transaction.

  • Inconsistent totals: Add integrity checks (constraints/triggers) and verify totals where relevant.

  • Concurrency bugs: Choose the right isolation level; use proper locks or MVCC.

  • Data loss on crash: Ensure WAL/journaling is enabled; test recovery procedures.

  • Long transactions: Keep them short to reduce lock contention and deadlocks.

FAQ

Q1. What is a transaction in DBMS?
A logical unit of work that groups multiple operations so they succeed or fail together.

Q2. What does ACID stand for?
Atomicity, Consistency, Isolation, Durability—four properties that keep data safe and correct.

Q3. What’s the difference between COMMIT and ROLLBACK?
COMMIT makes changes permanent; ROLLBACK undoes all uncommitted changes.

Q4. Which isolation level is best?
It depends: Read Committed for common OLTP workloads; Serializable for maximum correctness with higher cost.

Q5. Can two transactions run at the same time safely?
Yes—if isolation is enforced via locks/MVCC so they don’t see each other’s intermediate states.