ACID Properties - Database Transactions At Lld Level | OneNoughtOne

0/246

System Design (LLD)Database Transactions at LLD Level

Database Transactions at LLD Level

LevelIntermediate

Duration75 mins

TopicDatabase Transactions at LLD Level

1 / 4

ACID Properties

The Foundation of Data Integrity

Every time you transfer money between bank accounts, place an order on an e-commerce site, or update your profile across multiple database tables, you rely on a fundamental guarantee: the operation either completes entirely or doesn't happen at all. There's no acceptable middle ground where half your money disappears into the void or your order is partially recorded.

This guarantee isn't magical—it's the result of decades of database engineering crystallized into four essential properties known by the acronym ACID: Atomicity, Consistency, Isolation, and Durability. These properties aren't abstract academic concepts; they are the contractual promises that database systems make to application developers, and understanding them deeply is essential for any engineer building systems that matter.

What You Will Learn

By the end of this page, you will understand each ACID property at a deep level—not just what they mean, but how they manifest in real code, where they come from historically, how databases implement them, and critically, how violations of these properties lead to catastrophic failures in production systems. You'll gain the vocabulary and mental models to reason about transaction correctness like a database engineer.

The Historical Context

Before we dive into the properties themselves, it's valuable to understand where they came from. The ACID acronym was coined by Andreas Reuter and Theo Härder in 1983, though the underlying concepts had been developing since the 1970s.

The need for these properties emerged from a fundamental problem: how do you maintain data integrity when multiple users access and modify the same data simultaneously, especially when failures can occur at any moment?

Early database systems in the 1960s and 1970s were plagued by data corruption issues. A power failure during an update could leave data in an inconsistent state. Two users modifying the same record could overwrite each other's changes. The database might report success but lose the data before it reached disk.

The pioneers of database theory—particularly Jim Gray, who would later win the Turing Award—developed the transaction concept as the solution. A transaction wraps multiple operations into a single logical unit with specific guarantees. These guarantees became codified as ACID.

Jim Gray's Legacy

Jim Gray's 1981 paper 'The Transaction Concept: Virtues and Limitations' remains foundational reading. His work on transactions, logging, and recovery mechanisms forms the basis of how all major relational databases operate today. Understanding ACID is understanding Gray's vision of reliable data systems.

Atomicity: All or Nothing

Atomicity is derived from the Greek word atomos, meaning "indivisible." In the context of database transactions, atomicity guarantees that a transaction is treated as a single, indivisible unit of work. Either all operations within the transaction complete successfully, or none of them take effect.

This is the property that prevents "half-done" states. Consider a classic bank transfer:

Debit $500 from Account A
Credit $500 to Account B

Atomicity guarantees that you can never end up in a state where Account A has been debited but Account B hasn't been credited. If the system crashes after step 1 but before step 2, atomicity ensures that when the system recovers, Account A still has its original balance.

BankTransfer.java
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
public class TransferService {
    private final DataSource dataSource;
    
    public void transfer(String fromAccount, String toAccount, BigDecimal amount) 
            throws TransferException {
        Connection conn = null;
        try {
            conn = dataSource.getConnection();
            
            // BEGIN TRANSACTION - Atomicity starts here
            conn.setAutoCommit(false);
            
            // Step 1: Debit source account
            int debitedRows = debitAccount(conn, fromAccount, amount);
            if (debitedRows != 1) {
                throw new TransferException("Failed to debit source account");
            }
            
            // Step 2: Credit destination account
            int creditedRows = creditAccount(conn, toAccount, amount);
            if (creditedRows != 1) {
                throw new TransferException("Failed to credit destination account");
            }
            
            // Both steps succeeded - commit the atomic unit
            conn.commit();
            
        } catch (SQLException | TransferException e) {
            // Any failure: rollback ensures ATOMICITY
            // Neither debit nor credit will persist
            if (conn != null) {
                try {
                    conn.rollback();
                } catch (SQLException re) {
                    // Log rollback failure
                }
            }
            throw new TransferException("Transfer failed, no changes made", e);
        } finally {
            closeConnection(conn);
        }
    }
    
    private int debitAccount(Connection conn, String accountId, BigDecimal amount) 
            throws SQLException {
        String sql = "UPDATE accounts SET balance = balance - ? " +
                     "WHERE account_id = ? AND balance >= ?";
        try (PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setBigDecimal(1, amount);
            ps.setString(2, accountId);
            ps.setBigDecimal(3, amount);  // Ensure sufficient balance
            return ps.executeUpdate();
        }
    }
    
    private int creditAccount(Connection conn, String accountId, BigDecimal amount) 
            throws SQLException {
        String sql = "UPDATE accounts SET balance = balance + ? WHERE account_id = ?";
        try (PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setBigDecimal(1, amount);
            ps.setString(2, accountId);
            return ps.executeUpdate();
        }
    }
}

How Databases Implement Atomicity

Databases implement atomicity through a technique called Write-Ahead Logging (WAL). Before any change is applied to the actual data files, the intended change is first written to a transaction log. This log is the source of truth for what should happen.

The sequence is:

Begin Transaction: Transaction gets an ID, starts recording changes
Execute Operations: Changes are written to the WAL (not yet to data pages)
Commit: A commit record is written to the WAL; changes are now durable
Background Apply: Database lazily applies WAL changes to data pages

Recovery after crash:

Committed transactions: The WAL contains the commit record, so the database replays any changes that weren't yet applied to data pages
Uncommitted transactions: No commit record exists, so the database ignores (or explicitly undoes) partial changes

This mechanism ensures that even if the system crashes mid-transaction, recovery will restore the database to a consistent state where only complete, committed transactions are visible.

Atomicity Doesn't Mean Performance Isolation

A common misconception is that atomicity prevents other transactions from seeing in-progress changes. It doesn't—that's the job of Isolation (the 'I' in ACID). Atomicity only guarantees that if you roll back, no changes persist. Without proper isolation, other transactions might read your uncommitted changes before you decide to roll back.

Consistency: Rules Must Hold

Consistency is perhaps the most misunderstood ACID property. In the database context, consistency means that a transaction transforms the database from one valid state to another valid state, preserving all integrity constraints, invariants, and business rules defined on the data.

These constraints include:

Schema constraints: Data types, NOT NULL, UNIQUE, PRIMARY KEY
Referential integrity: Foreign key relationships
Check constraints: Business rules like "balance >= 0" or "age > 0"
Triggers: Automatic rules that maintain complex invariants
Application-level invariants: Rules not enforced by the database but required by the business

Critical insight: While the database enforces many consistency rules automatically, application-level consistency is YOUR responsibility.

Types of Consistency Constraints
Constraint Type	Enforced By	Example	Failure Behavior
NOT NULL	Database	email column cannot be null	SQL error, transaction fails
UNIQUE	Database	email must be unique per user	Constraint violation error
FOREIGN KEY	Database	order.customer_id must exist in customers	Referential integrity error
CHECK	Database	CHECK (balance >= 0)	Check constraint violation
Application Rule	Your Code	User cannot order more than inventory	Must be enforced in application logic
Cross-Aggregate	Your Code	Order total must match sum of line items	Application must maintain invariant

ConsistencyExample.java
Java
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
public class OrderService {
    /**
     * This method demonstrates maintaining APPLICATION-LEVEL consistency.
     * The database can't enforce that order total equals sum of line items;
     * that's our responsibility.
     */
    public void addLineItem(Connection conn, UUID orderId, LineItem item) 
            throws SQLException, BusinessRuleException {
        conn.setAutoCommit(false);
        
        try {
            // Step 1: Verify the order exists and is in valid state
            Order order = findOrderForUpdate(conn, orderId);
            if (order.getStatus() != OrderStatus.DRAFT) {
                throw new BusinessRuleException(
                    "Cannot modify order in status: " + order.getStatus()
                );
            }
            
            // Step 2: Check inventory availability - APPLICATION CONSISTENCY
            int availableStock = getAvailableStock(conn, item.getProductId());
            int alreadyReserved = getOrderedQuantity(conn, orderId, item.getProductId());
            
            if (availableStock - alreadyReserved < item.getQuantity()) {
                throw new BusinessRuleException(
                    "Insufficient stock for product: " + item.getProductId()
                );
            }
            
            // Step 3: Insert line item
            insertLineItem(conn, orderId, item);
            
            // Step 4: Recalculate order total - MAINTAINING INVARIANT
            BigDecimal newTotal = calculateOrderTotal(conn, orderId);
            updateOrderTotal(conn, orderId, newTotal);
            
            // Step 5: Reserve inventory - ANOTHER APPLICATION INVARIANT
            reserveInventory(conn, item.getProductId(), item.getQuantity());
            
            // All consistency rules maintained, commit
            conn.commit();
            
        } catch (SQLException | BusinessRuleException e) {
            conn.rollback();  // Restore to previous consistent state
            throw e;
        }
    }
    
    /**
     * Note the "FOR UPDATE" - this locks the row to prevent concurrent
     * modifications that could break consistency
     */
    private Order findOrderForUpdate(Connection conn, UUID orderId) 
            throws SQLException {
        String sql = "SELECT * FROM orders WHERE id = ? FOR UPDATE";
        // ... execute and map result
    }
    
    private BigDecimal calculateOrderTotal(Connection conn, UUID orderId) 
            throws SQLException {
        String sql = 
            "SELECT COALESCE(SUM(quantity * unit_price), 0) " +
            "FROM order_line_items WHERE order_id = ?";
        // ... execute and return
    }
}

Consistency vs. "C" in CAP Theorem

Don't confuse ACID Consistency with CAP Theorem Consistency! ACID Consistency is about preserving data integrity and business rules. CAP Consistency is about all nodes in a distributed system seeing the same data at the same time. These are entirely different concepts that unfortunately share the same name.

The Responsibility Split

Modern application development creates a shared responsibility for consistency:

Database-Enforced Consistency:

Structural integrity (types, nullability)
Uniqueness and primary keys
Foreign key relationships
Simple value constraints (CHECK)

Application-Enforced Consistency:

Complex business rules
Cross-entity invariants
Aggregate consistency (DDD)
Temporal consistency (events must be in order)

The danger zone: Many consistency violations happen in application code that either doesn't enforce business rules within transaction boundaries, or breaks atomicity by making partial commits.

Isolation: Concurrent Safety

Isolation is the property that determines how and when the changes made by one transaction become visible to other concurrent transactions. In a perfect world, each transaction would execute as if it were the only transaction running—this is called serializability, the strongest isolation level.

However, strict serializability comes with significant performance costs. Real-world databases offer a spectrum of isolation levels that trade off correctness guarantees against performance and concurrency.

Without proper isolation, concurrent transactions can experience various anomalies—situations where the observed data behavior wouldn't be possible if transactions ran one at a time.

Concurrency Anomalies (Without Proper Isolation)

•Dirty Read — Transaction A reads uncommitted changes from Transaction B. If B rolls back, A has read data that never officially existed. Like reading someone's draft email before they decide whether to send it.
•Non-Repeatable Read — Transaction A reads a row, Transaction B modifies and commits it, then A reads the same row again and gets different data. A's two reads of the same row return different results within a single transaction.
•Phantom Read — Transaction A queries a set of rows matching a condition. Transaction B inserts new rows matching that condition. A repeats the query and gets additional 'phantom' rows that weren't there before.
•Lost Update — Both Transaction A and B read a value, compute updates based on it, and write back. The second write blindly overwrites the first, losing one transaction's work entirely.
•Write Skew — Two transactions read overlapping data, make decisions based on it, and write to different rows. Both individual writes are valid, but the combined result violates an invariant.

IsolationAnomalies.java
Java
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
/**
 * Demonstration of concurrency anomalies and how isolation levels prevent them
 */
public class IsolationDemonstration {
    
    // ================================
    // DIRTY READ SCENARIO
    // ================================
    // READ UNCOMMITTED allows this; READ COMMITTED prevents it
    
    void dirtyReadScenario() {
        // Transaction A (Account Holder)
        // Time T1: Account balance is $1000
        // Time T2: Transaction B starts a withdrawal of $500
        //          UPDATE accounts SET balance = 500 WHERE id = 'A'
        //          (not yet committed)
        // Time T3: Transaction A reads balance - sees $500 (DIRTY READ!)
        // Time T4: Transaction B encounters an error and ROLLS BACK
        //          Balance is restored to $1000
        // Time T5: Transaction A still thinks balance is $500
        //          Makes decisions based on PHANTOM DATA
    }
    
    // ================================
    // LOST UPDATE SCENARIO
    // ================================
    // Even READ COMMITTED allows this; REPEATABLE READ prevents it
    
    void lostUpdateScenario() {
        // Initial: inventory = 100
        
        // Transaction A (Order #1)
        // T1: SELECT quantity FROM inventory WHERE product_id = 'X'
        //     Result: 100
        // T2: (calculates: 100 - 10 = 90)
        
        // Transaction B (Order #2) - CONCURRENT
        // T3: SELECT quantity FROM inventory WHERE product_id = 'X'
        //     Result: 100 (same as A saw)
        // T4: (calculates: 100 - 5 = 95)
        // T5: UPDATE inventory SET quantity = 95 WHERE product_id = 'X'
        // T6: COMMIT
        
        // Transaction A continues
        // T7: UPDATE inventory SET quantity = 90 WHERE product_id = 'X'
        //     A's update OVERWRITES B's!
        // T8: COMMIT
        
        // Final result: quantity = 90
        // LOST: B's update (should have been 85 after both orders)
    }
    
    // ================================
    // WRITE SKEW SCENARIO
    // ================================
    // Even REPEATABLE READ allows this in some DBs; SERIALIZABLE prevents it
    
    void writeSkewScenario() {
        // Business rule: At least one doctor must be on call
        // Currently: Dr. Alice is on call, Dr. Bob is on call (2 total)
        
        // Transaction A (Dr. Alice wants to go off-call)
        // T1: SELECT COUNT(*) FROM on_call WHERE on_call = true
        //     Result: 2 (Alice and Bob)
        // T2: "2 > 1, so if I leave, Bob is still on call. Safe!"
        // T3: UPDATE doctors SET on_call = false 
        //           WHERE name = 'Alice'
        
        // Transaction B (Dr. Bob wants to go off-call) - CONCURRENT
        // T4: SELECT COUNT(*) FROM on_call WHERE on_call = true
        //     Result: 2 (Alice and Bob - sees snapshot before A's update)
        // T5: "2 > 1, so if I leave, Alice is still on call. Safe!"
        // T6: UPDATE doctors SET on_call = false 
        //                WHERE name = 'Bob'
        
        // Both transactions commit
        // Result: NOBODY is on call!
        // Both individually made valid decisions, but combined: INVARIANT VIOLATED
    }
}

SQL Standard Isolation Levels and Permitted Anomalies
Isolation Level	Dirty Read	Non-Repeatable Read	Phantom Read	Performance
READ UNCOMMITTED	Possible	Possible	Possible	Highest
READ COMMITTED	Prevented	Possible	Possible	High
REPEATABLE READ	Prevented	Prevented	Possible*	Medium
SERIALIZABLE	Prevented	Prevented	Prevented	Lowest

Database Implementations Vary!

The SQL standard defines isolation levels, but database vendors implement them differently. PostgreSQL's REPEATABLE READ actually prevents phantom reads (using MVCC snapshots). MySQL/InnoDB's REPEATABLE READ can have phantom reads with some operations. Oracle doesn't even offer true REPEATABLE READ—just READ COMMITTED and SERIALIZABLE. Always test behavior on YOUR database!

Durability: Committed Means Permanent

Durability provides the guarantee that once a transaction commits, its changes will survive any subsequent failure—including power outages, system crashes, and hardware failures. When your commit call returns success, the data is safe.

This sounds simple, but implementing durability is surprisingly complex. Modern computers have multiple layers of caching (CPU cache, OS buffer cache, disk controller cache), and data isn't truly 'on disk' just because your program wrote it.

The durability challenge:

Application writes data → goes to programming language buffers
Flush to OS → goes to OS page cache (still in RAM!)
OS writes to disk → may go to disk controller cache (still in volatile memory!)
Physical write to platters/cells → finally durable

Databases use several techniques to ensure durability at each layer.

Durability Mechanisms in Databases

•Write-Ahead Logging (WAL) — Before modifying data pages, write the change to a sequential log file. The log is synced to disk before commit returns. Even if data pages are corrupted, the log can replay all committed transactions.
•Forced Write / fsync — Databases issue fsync() or equivalent system calls to force the OS to push data through all caches to physical storage. This is slow but necessary for durability.
•Group Commit — To amortize fsync cost, databases may batch multiple transactions' log records into a single fsync. Slightly increases latency but dramatically improves throughput.
•Checkpointing — Periodically write dirty data pages to disk and record a checkpoint in the log. Limits how much log replay is needed after a crash.
•Battery-Backed Write Cache (BBWC) — Enterprise storage systems use battery-backed caches so that data in controller cache survives power loss, allowing acknowledgment before physical write.
•Replication — For extreme durability, replicate transactions to multiple physical machines. Commit only returns when N replicas have durably stored the data (synchronous replication).

DurabilityConfiguration.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
-- PostgreSQL durability configuration settings
 
-- Ensure WAL is flushed to disk on every commit (default: on)
-- Setting to 'off' risks losing recent transactions after crash
SET synchronous_commit = 'on';
 
-- How much WAL to buffer before forcing a write
-- Larger values improve performance but risk more data loss
SHOW wal_buffers;  -- typically 64MB
 
-- For synchronous replication (extreme durability)
-- Commit blocks until replica confirms WAL receipt
SET synchronous_commit = 'remote_write';  -- replica received WAL
SET synchronous_commit = 'remote_apply';  -- replica applied WAL
 
-- Checkpoint behavior
-- More frequent checkpoints = faster recovery but lower performance
SHOW checkpoint_timeout;  -- default 5 minutes
SHOW max_wal_size;  -- triggers checkpoint when exceeded
 
-- View current WAL position and replication status
SELECT pg_current_wal_lsn();  -- current write-ahead log position
SELECT * FROM pg_stat_replication;  -- replica sync status

Durability Trade-offs Have Real Consequences

Disabling durability settings (async commits, disabled fsync) can improve performance 10-100x but risks real data loss. In 2011, a major cloud provider lost customer data across multiple availability zones because they disabled durability features for performance. The data that was 'committed' but not yet physically written was lost forever when power failed. Understand these trade-offs deeply before touching durability settings.

The ACID Properties Working Together

While we've examined each ACID property individually, they work together as an integrated system of guarantees. Understanding their interplay is crucial for building robust systems.

The lifecycle of a reliable transaction:

ACID Properties in the Transaction Lifecycle
Phase	ACID Property	What Happens	Guarantee Provided
Begin	Isolation	Transaction gets a snapshot or acquires locks	Other transactions won't interfere
Execute	Consistency	Operations are validated against constraints	Only valid state transitions allowed
Execute	Isolation	Changes are invisible to others (or controlled visibility)	No dirty reads (at appropriate level)
Commit	Atomicity	All changes are made permanent together	No partial commits possible
Commit	Durability	Changes survive system failures	Committed data is never lost
Rollback	Atomicity	All changes are undone completely	No partial rollbacks possible

When ACID Breaks Down

Distributed Systems Challenge:

ACID properties are well-defined for single-database transactions. When you need to coordinate transactions across multiple databases, message queues, or microservices, maintaining ACID becomes significantly harder:

Atomicity across services: Two-phase commit (2PC) can provide it but has availability costs
Consistency across services: Each service may have different invariants to maintain
Isolation across services: No global snapshot—each service sees its own timeline
Durability across services: What if one service is durable but another fails?

This is why patterns like Saga, Outbox, and eventual consistency emerged—they provide weaker but more practical guarantees for distributed coordination.

Application-Level Violations:

Even with a fully ACID-compliant database, your application can break guarantees:

Starting too many transactions: Holding transactions open too long causes contention
Inconsistent reads outside transactions: Reading data, computing, then writing in a new transaction
Ignoring return values: Not checking if UPDATE affected the expected number of rows
Suppressing exceptions: Catching and ignoring database errors that indicate constraint violations

The ACID Guarantees Are a Contract

Think of ACID as a contract between you and the database. The database promises to uphold these properties IF you use transactions correctly. If you bypass transactions, mix transactional and non-transactional operations, or ignore errors, the contract is void. ACID is not magic—it requires correct usage.

Summary: ACID Properties

We've explored the foundational guarantees that make reliable database systems possible. Let's consolidate the essential takeaways:

Key Takeaways

•Atomicity guarantees all-or-nothing — Transactions commit completely or have no effect. Implemented via Write-Ahead Logging.
•Consistency ensures valid state transitions — Database constraints are enforced, but many business rules must be enforced by application code.
•Isolation controls concurrent access — Different levels trade correctness for performance. Know your database's specific implementation.
•Durability means committed data survives failures — Implemented via WAL, fsync, and replication. Disabling it for performance has real risks.
•ACID properties work together — They form an integrated guarantee system, not independent features.
•Application code must uphold its part — The database provides tools, but correct usage is your responsibility.

What's next:

Understanding ACID properties is foundational, but the real skill is applying them in code. The next page examines transaction boundaries in code—how to identify where transactions should begin and end, common patterns for transaction management, and the critical mistakes that break ACID guarantees in real applications.

Page Complete

You now understand the four fundamental ACID properties that govern reliable database transactions. These properties aren't just theory—they directly impact how you design and implement data access code. In the next page, we'll see how to draw transaction boundaries correctly in real applications.