Topic 45 of 52
ACID Database Transaction
Overview
ACID is the set of properties that guarantee reliable database transactions: Atomicity, Consistency, Isolation, and Durability. Understanding ACID is essential for backend engineering interviews and production database design.
Syntax
sql
-- A - Atomicity: ALL or NOTHING
BEGIN;
UPDATE accounts SET balance = balance - 1000 WHERE id = 1;
UPDATE accounts SET balance = balance + 1000 WHERE id = 2;
COMMIT; -- Both succeed together
-- or ROLLBACK; -- Both fail together (no partial update)
-- C - Consistency: database stays valid
-- Constraints (CHECK, FK, NOT NULL) enforce consistency
-- A transaction cannot leave the database in an invalid state
-- I - Isolation: concurrent transactions don't interfere
-- Isolation levels: READ UNCOMMITTED < READ COMMITTED < REPEATABLE READ < SERIALIZABLE
-- D - Durability: committed data survives crashes
-- Achieved via WAL (Write-Ahead Logging)
-- After COMMIT, data is on disk even if server crashes immediately
-- Set isolation level
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;Common Pitfalls
- Higher isolation levels (REPEATABLE READ, SERIALIZABLE) prevent more anomalies but reduce concurrency and can cause deadlocks.
- READ COMMITTED is the default in most databases — it prevents dirty reads but allows non-repeatable reads.
- Interview tip: Know all four ACID properties and which isolation level prevents which anomaly: dirty read, non-repeatable read, phantom read.
Real-World Example
ACID properties in a banking transfer scenario:
example
sql
-- ACID in action: Bank transfer between two accounts
-- ATOMICITY: Both debit and credit succeed, or neither does
BEGIN;
UPDATE bank_accounts
SET balance = balance - 5000
WHERE user_id = 101 AND balance >= 5000;
-- If balance check fails (0 rows updated), ROLLBACK
-- Application checks affected rows here
UPDATE bank_accounts
SET balance = balance + 5000
WHERE user_id = 202;
INSERT INTO transfer_log (from_user, to_user, amount, txn_ref)
VALUES (101, 202, 5000, 'TXN-2025-001');
COMMIT;
-- DURABILITY: Once committed, this transfer survives any crash
-- CONSISTENCY: Enforced by constraints
-- CHECK (balance >= 0) prevents negative balances
-- FOREIGN KEY ensures user_ids reference valid accounts
-- ISOLATION: If another transaction reads user 101's balance
-- during this transaction, they see the COMMITTED balance
-- (at READ COMMITTED isolation level)