What is a transaction and what are the ACID properties?

Question

Accepted Answer

Section 8: Transactions & Concurrency



  59
   What is a transaction and what are the ACID properties?
  Level: Advanced — The cornerstone of reliable databases; asked in virtually every senior interview.

  A transaction is a single logical unit of work made up of one or more SQL statements that must execute together as an all-or-nothing operation. If any statement fails, the entire transaction is rolled back, leaving the database unchanged.

  The classic example is a bank transfer: debiting one account and crediting another must both succeed, or neither should happen.

  
     ACID is the acronym for the four properties that guarantee reliable transactions: Atomicity, Consistency, Isolation, and Durability. InnoDB is fully ACID-compliant; MyISAM is not.
  

   The Four ACID Properties
  
     Atomicity: All statements succeed or all fail. There is no partial completion — it's "all or nothing".
     Consistency: The database moves from one valid state to another, respecting all constraints and rules.
     Isolation: Concurrent transactions don't interfere with each other; each behaves as if running alone.
     Durability: Once committed, the changes are permanent — they survive crashes and power failures.
  

   ACID at a Glance
  
    
      
        
          Property
          Guarantees
          Achieved By
        
      
      
        
          Atomicity
          All or nothing
          Undo logs / ROLLBACK
        
        
          Consistency
          Valid state to valid state
          Constraints, triggers
        
        
          Isolation
          No interference
          Locking, isolation levels
        
        
          Durability
          Permanent changes
          Redo logs, disk flush
        
      
    
  

   Quick Example — A Bank Transfer
-- Start a transaction
START TRANSACTION;

-- Debit account A
UPDATE accounts SET balance = balance - 1000 WHERE acc_id = 1;

-- Credit account B
UPDATE accounts SET balance = balance + 1000 WHERE acc_id = 2;

-- If both succeed, make changes permanent (Durability)
COMMIT;

-- If something went wrong, undo everything (Atomicity)
-- ROLLBACK;

  
     Auto-commit note: By default MySQL runs in autocommit = 1 mode, where each statement is its own transaction. Use START TRANSACTION (or SET autocommit = 0) to group multiple statements.
  

  
     Interviewer tip: The one-liner they want — "A transaction is an all-or-nothing unit of work governed by the ACID properties: Atomicity (all or nothing), Consistency (valid states), Isolation (no interference), and Durability (permanent once committed)."

Property	Guarantees	Achieved By
Atomicity	All or nothing	Undo logs / ROLLBACK
Consistency	Valid state to valid state	Constraints, triggers
Isolation	No interference	Locking, isolation levels
Durability	Permanent changes	Redo logs, disk flush

What is a transaction and what are the ACID properties?

Answer with Explanation

Section 8: Transactions & Concurrency

What is a transaction and what are the ACID properties?

The Four ACID Properties

ACID at a Glance

Quick Example — A Bank Transfer