Database Management SystemsACID Properties

ACID Properties: The Foundation of Reliable Transactions

LevelIntermediate

Duration60 mins

TopicACID Properties

1 / 5

Atomicity: The All-or-Nothing Guarantee

When Half a Transaction Is Worse Than No Transaction

Imagine you're transferring $1,000 from your savings account to your checking account. The database successfully deducts $1,000 from savings—and then the power goes out. Your checking account was never credited. You've just lost $1,000 to the void. This scenario represents one of the most fundamental problems in database systems: partial execution . Without proper safeguards, any interruption—power failure, hardware crash, network timeout, or software bug—can leave data in an inconsistent, corrupted state. Money disappears. Inventory counts become wrong. Orders exist without payments, or payments exist without orders. Atomicity is the ACID property that prevents this nightmare. It guarantees that a transaction is an indivisible unit of work: either every operation within the transaction completes successfully, or none of them take effect. There is no middle ground, no partial state, no 'half-executed' transaction polluting your data.

What You Will Learn

By the end of this page, you will understand atomicity at a deep, implementation-aware level. You'll learn how databases achieve the all-or-nothing guarantee through logging and rollback mechanisms, why atomicity is essential for correctness, and how to reason about atomicity in your application design.

Defining Atomicity: The Indivisible Transaction

The term atomicity derives from the Greek word atomos, meaning 'indivisible' or 'uncuttable.' In the context of database transactions, atomicity means that a transaction is treated as a single, indivisible unit of execution—even if it consists of multiple individual operations. Formal Definition: A transaction exhibits atomicity if and only if either all of its operations are executed and their effects are permanently recorded in the database, or no operations take effect and the database remains exactly as it was before the transaction began. This binary outcome is absolute. There is no spectrum of partial completion. A transaction with 100 operations succeeds completely or fails completely—never 47 out of 100, never 'mostly done.'

Atomicity as a Contract

Think of atomicity as a contract between the application and the database. The application promises to group related operations into a transaction. The database promises that this group will be treated as one unit—either honored in full or rejected in full. This contract allows developers to reason about correctness without worrying about arbitrary failure points.

The Two Possible Outcomes: Every transaction that begins will eventually reach one of exactly two terminal states: Committed — All operations completed successfully. All changes are permanently recorded in the database. The transaction's effects are now visible to other transactions and will survive any future system failures. Aborted (Rolled Back) — The transaction did not complete. Either an explicit ROLLBACK was issued, or a failure occurred. All changes made by the transaction have been undone. The database state is identical to what it was before the transaction began. There is no third state. There is no 'pending,' 'partially committed,' or 'unknown' outcome that persists indefinitely. The database system guarantees resolution to one of these two states.

Atomicity: Possible Transaction Outcomes
Outcome	Database State	Changes	Visibility
Committed	Reflects all transaction changes	Permanently recorded	Visible to all other transactions
Aborted	Unchanged from before transaction	Completely undone	As if transaction never happened

Why Atomicity Matters: The Consequences of Partial Execution

Without atomicity, database systems would be fundamentally unreliable. Every operation would risk leaving data in a state that violates application invariants—those essential truths that must always hold for the data to be meaningful. Consider a banking funds transfer: BEGIN TRANSACTION; UPDATE accounts SET balance = balance - 1000 WHERE account_id = 'savings'; UPDATE accounts SET balance = balance + 1000 WHERE account_id = 'checking'; COMMIT; This transaction has a crucial invariant: the total money across both accounts must remain constant . If only the first UPDATE executes, $1,000 vanishes from the system. The invariant is violated. The bank's books no longer balance. Customer trust is destroyed.

Consequences of Missing Atomicity

•Financial Losses — Money, inventory, credits, or other quantifiable assets can appear or disappear, creating real financial liability and accounting nightmares.
•Referential Integrity Violations — Foreign key relationships break when parent records are created without children or vice versa. Orders exist without customers. Line items reference non-existent products.
•Orphaned or Dangling Records — Partial inserts leave records that lack required related data, polluting the database with unusable entries that complicate future queries.
•Corrupted Application State — Applications relying on database consistency can enter undefined states, triggering cascading bugs that are extremely difficult to diagnose.
•Audit and Compliance Failures — Regulatory requirements often mandate that transactions be complete and auditable. Partial transactions create gaps that auditors flag as control failures.

The Cascade Effect

Partial transactions don't just corrupt data—they corrupt trust in data. Once users or applications suspect that the database might contain inconsistencies, every query result becomes suspect. Teams start building workarounds and validation layers. The database loses its role as the authoritative source of truth.

Real-World Example: E-Commerce Order Processing An order placement transaction might include: Insert order header record Insert order line items (potentially multiple) Deduct inventory for each item Calculate and insert tax records Create payment authorization record Update customer's order history Without atomicity, a failure at step 4 could leave: An order that exists but has no tax calculation (legal compliance violation) Inventory already deducted for an order that won't ship (inventory discrepancy) Payment never authorized, but order appears placed (fulfillment confusion) The cleanup cost for such corrupted states often exceeds the original transaction's value by orders of magnitude. Atomicity prevents the problem entirely.

How Databases Achieve Atomicity: The Log-Based Approach

Atomicity seems almost magical: if a power failure can happen mid-operation, how can the database promise to completely undo changes it already wrote to disk? The answer lies in one of the most important mechanisms in database systems: the transaction log (also called the Write-Ahead Log or WAL). The Core Insight: Before modifying any actual data page, the database first writes a log record describing what it's about to do. This log record is forced to stable storage (disk) before the data modification is applied. If a failure occurs, the database can use the log to understand exactly what was in progress and either complete it (redo) or undo it (rollback).

transaction-log-entries.txt
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
Log Sequence Number (LSN): 1001
Transaction: T1 BEGIN
Timestamp: 2024-01-15 10:30:00.001
 
LSN: 1002
Transaction: T1 UPDATE accounts
Before Image: {account_id: 'savings', balance: 5000}
After Image:  {account_id: 'savings', balance: 4000}
 
LSN: 1003
Transaction: T1 UPDATE accounts
Before Image: {account_id: 'checking', balance: 2000}
After Image:  {account_id: 'checking', balance: 3000}
 
LSN: 1004
Transaction: T1 COMMIT
Timestamp: 2024-01-15 10:30:00.045

Key Components of Log Records: Each log record contains critical information: Log Sequence Number (LSN) — A unique, monotonically increasing identifier that orders all log records. This ordering is essential for recovery. Transaction Identifier — Which transaction generated this log record. Multiple transactions may have interleaved entries. Before Image — The original value of data before modification. Essential for UNDO operations during rollback. After Image — The new value after modification. Essential for REDO operations during recovery. Operation Type — Whether this is a BEGIN, INSERT, UPDATE, DELETE, COMMIT, or ABORT record.

Write-Ahead Logging Rule

The WAL rule is inviolable: the log record for a modification MUST be written to stable storage BEFORE the modified data page is written to stable storage. This guarantee is what makes recovery possible. If you see the data change, the log proving how to undo it already exists on disk.

The Rollback Process: When a transaction must be aborted—either due to failure or explicit ROLLBACK—the database uses the log to undo all changes: Find all log records belonging to the aborted transaction Process them in reverse order (most recent first) For each modification, replace the current value with the before image Write an ABORT/COMPENSATION log record to document the rollback Release all locks held by the transaction After rollback, the database state is exactly as if the transaction never began. Other transactions observing the data before the abort saw the modifications; after the abort, those modifications cease to exist. Memory of the aborted transaction exists only in the log (for audit and recovery purposes).

Converting Mermaid diagram...

Atomicity During Crashes: Recovery Guarantees

The true test of atomicity isn't normal execution—it's behavior after unexpected failures. What happens when the power cuts out mid-transaction? What about operating system crashes, hardware failures, or kernel panics? The database's guarantee: After recovery from any failure, the database will be in a consistent state where every transaction is either fully committed or fully aborted. No partial transactions will exist. How crash recovery works: When the database restarts after a failure, the recovery manager performs a systematic process:

Crash Recovery Phases (ARIES-style)

•Analysis Phase — Scan the log from the last checkpoint forward. Identify which transactions were active at crash time (in-progress, not committed) and which pages might have uncommitted modifications.
•Redo Phase — Replay the log forward, reapplying all modifications from committed transactions. This ensures durability: committed work is not lost even if it wasn't yet written to data files.
•Undo Phase — Process the log backward for all transactions that were active but not committed at crash time. Apply before images to reverse their modifications. This ensures atomicity: uncommitted work leaves no trace.

Recovery Action Based on Transaction State at Crash
Transaction State at Crash	Log Contains	Recovery Action	Final State
Committed (COMMIT record present)	COMMIT record	Redo all operations	Fully committed, changes preserved
Active (no COMMIT record)	Only BEGIN and operations	Undo all operations	Fully rolled back, no trace
Preparing (2PC participant)	PREPARE but no COMMIT	Wait for coordinator decision	Depends on distributed protocol

The Commit Point

A transaction is considered committed when its COMMIT log record is successfully written to stable storage—not when the application receives acknowledgment, and not when data pages are written. This single moment, the commit point, is the boundary between 'will be undone' and 'will survive any failure.'

Example: Crash Recovery Scenario Consider this timeline: 10:30:00.001 T1: BEGIN 10:30:00.010 T1: UPDATE accounts (savings -= 1000) 10:30:00.020 T1: UPDATE accounts (checking += 1000) 10:30:00.025 T1: COMMIT written to log 10:30:00.030 Data page for savings written to disk 10:30:00.035 CRASH (checking page not yet written) During recovery: Analysis finds T1 completed (COMMIT record exists) Redo phase detects checking page is stale (LSN indicates it needs update) Checking page is updated from the after image in the log Result: Both accounts reflect the transfer, atomicity preserved Now consider if the crash occurred at 10:30:00.023 (before COMMIT): Analysis finds T1 active (no COMMIT record) Undo phase reverses the savings deduction using the before image Result: Both accounts have original values, atomicity preserved

Atomicity in Practice: Application Patterns

Understanding atomicity conceptually is essential, but applying it correctly in application code requires specific patterns and awareness of common pitfalls. Pattern 1: Explicit Transaction Boundaries The most common pattern is explicitly declaring transaction boundaries around related operations:

explicit-transactions.sql
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
-- SQL: Explicit transaction for order processing
BEGIN TRANSACTION;
 
    -- Insert order header
    INSERT INTO orders (order_id, customer_id, order_date, status)
    VALUES ('ORD-2024-001', 'CUST-500', CURRENT_TIMESTAMP, 'pending');
    
    -- Insert order line items
    INSERT INTO order_items (order_id, product_id, quantity, unit_price)
    VALUES ('ORD-2024-001', 'PROD-100', 2, 29.99);
    
    INSERT INTO order_items (order_id, product_id, quantity, unit_price)
    VALUES ('ORD-2024-001', 'PROD-205', 1, 149.99);
    
    -- Deduct inventory
    UPDATE inventory SET quantity = quantity - 2 
    WHERE product_id = 'PROD-100';
    
    UPDATE inventory SET quantity = quantity - 1 
    WHERE product_id = 'PROD-205';
    
    -- Create payment authorization
    INSERT INTO payment_auth (order_id, amount, auth_code)
    VALUES ('ORD-2024-001', 209.97, 'AUTH-789456');
 
COMMIT;
 
-- If any statement fails, the entire transaction rolls back
-- Either the complete order exists, or nothing exists

Pattern 2: Error Handling with Rollback Application code must handle errors and explicitly trigger rollback when business logic fails:

error-handling-rollback.py
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
# Python: Transaction with error handling
def transfer_funds(from_account: str, to_account: str, amount: Decimal) -> bool:
    connection = get_db_connection()
    
    try:
        connection.begin_transaction()
        
        # Check sufficient funds
        from_balance = connection.execute(
            "SELECT balance FROM accounts WHERE account_id = %s FOR UPDATE",
            (from_account,)
        ).fetchone()['balance']
        
        if from_balance < amount:
            connection.rollback()  # Explicit rollback on business rule violation
            raise InsufficientFundsError(f"Balance {from_balance} < {amount}")
        
        # Execute transfer
        connection.execute(
            "UPDATE accounts SET balance = balance - %s WHERE account_id = %s",
            (amount, from_account)
        )
        
        connection.execute(
            "UPDATE accounts SET balance = balance + %s WHERE account_id = %s",
            (amount, to_account)
        )
        
        connection.commit()
        return True
        
    except Exception as e:
        connection.rollback()  # Ensure rollback on any exception
        logger.error(f"Transfer failed: {e}")
        raise
    
    finally:
        connection.close()

Common Pitfall: Non-Transactional Side Effects

Atomicity only covers database operations. If your transaction sends an email, calls an external API, writes to a file, or triggers a message queue publication, those side effects are NOT rolled back if the transaction aborts. You must design compensating actions or use patterns like the outbox pattern to maintain consistency with external systems.

Pattern 3: Savepoints for Partial Rollback Savepoints allow rolling back to an intermediate point within a transaction without aborting the entire transaction:

savepoints.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
BEGIN TRANSACTION;
 
    INSERT INTO orders (order_id, customer_id) VALUES ('ORD-100', 'CUST-1');
    
    SAVEPOINT before_premium_items;
    
    -- Attempt to add premium items (might fail due to inventory)
    INSERT INTO order_items (order_id, product_id) VALUES ('ORD-100', 'PREMIUM-1');
    UPDATE inventory SET quantity = quantity - 1 WHERE product_id = 'PREMIUM-1';
    
    -- If premium item unavailable, rollback just that part
    -- ROLLBACK TO SAVEPOINT before_premium_items;
    
    -- Continue with regular items
    INSERT INTO order_items (order_id, product_id) VALUES ('ORD-100', 'REGULAR-1');
    UPDATE inventory SET quantity = quantity - 1 WHERE product_id = 'REGULAR-1';
 
COMMIT;  -- Order created with whatever items were available

Atomicity Beyond the Database: Distributed Challenges

A single database can guarantee atomicity for its own operations. But modern systems often involve multiple databases, message queues, caches, external APIs, and file systems. Extending atomicity across these boundaries is fundamentally harder. The Distributed Atomicity Problem: Consider an e-commerce purchase that requires: Charging the customer's credit card (external payment gateway) Deducting inventory (database A) Creating a shipping label (external shipping API) Sending confirmation email (external email service) Publishing event to message queue (message broker) No single transaction can atomically span all these systems. If step 3 fails after step 1 and 2 succeed, you've charged the customer and deducted inventory for an order you can't ship.

Patterns for Distributed Atomicity

•Saga Pattern — Break the workflow into a sequence of local transactions, each with a compensating action. If step N fails, execute compensating actions for steps N-1, N-2, ... down to step 1. Not true atomicity, but achieves eventual consistency through explicit compensation.
•Two-Phase Commit (2PC) — A coordinator asks all participants to prepare, then if all agree, commits everywhere. Provides true atomicity but has significant performance costs and failure mode complexities. Rarely used across organizational boundaries.
•Outbox Pattern — Store external requests (emails, API calls) in a database table within the same transaction as the business data. A separate process reads the outbox and executes external actions, retrying until successful. Guarantees local atomicity; handles external systems eventually.
•Idempotent Operations — Design external calls to be safely retriable. If unsure whether an operation succeeded, retry it. The external system recognizes duplicates and returns success without re-executing.

The Reality of Distributed Systems

Perfect atomicity across truly distributed systems is often impossible or impractical. The goal shifts from 'all-or-nothing' to 'eventually consistent with guaranteed recovery.' This isn't a failure of atomicity—it's an acknowledgment that real-world systems require pragmatic trade-offs. Local atomicity remains essential; global coordination requires different patterns.

The Outbox Pattern in Detail: BEGIN TRANSACTION; -- Business logic INSERT INTO orders (...); UPDATE inventory SET (...); -- Instead of calling external services directly... -- Store the intent in an outbox table INSERT INTO outbox (event_type, payload, status) VALUES ('OrderCreated', '{order_id: 123, ...}', 'pending'); INSERT INTO outbox (event_type, payload, status) VALUES ('SendConfirmationEmail', '{to: customer@..., order_id: 123}', 'pending'); COMMIT; A separate background process polls the outbox table, executes each pending action, and marks it complete. If the process crashes, it resumes from pending entries. The database guarantees that either both the order and outbox entries exist, or neither do—local atomicity is preserved.

Common Misconceptions About Atomicity

Atomicity is often misunderstood. Let's address the most common misconceptions to ensure a precise understanding:

Misconceptions

•Atomicity means operations happen instantaneously
•Atomicity prevents other transactions from seeing my changes
•A single SQL statement is automatically atomic
•Atomicity protects against application bugs
•If I don't use transactions, I don't need to worry about atomicity

Reality

•Atomicity means all-or-nothing, not instant. Transactions can take milliseconds to hours.
•That's isolation, not atomicity. Atomicity is about completion, isolation is about visibility.
•True for most databases, but the transaction is implicit. Multi-row DML is atomic within the statement but may need explicit transactions for cross-statement atomicity.
•Atomicity undoes database changes, but can't undo incorrect but valid operations. If you subtract 100 when you meant 10, atomicity won't help.
•Every database operation has atomicity implications. Without explicit transactions, each statement is its own atomic unit—which may not match your application's logical units.

Atomicity is Necessary, Not Sufficient

Atomicity guarantees that transactions complete fully or not at all. It does NOT guarantee that concurrent transactions see consistent views (that's Isolation). It does NOT guarantee that committed data survives crashes (that's Durability). It does NOT guarantee that transactions maintain all business rules (that's Consistency). Atomicity is one pillar of ACID—essential, but not the whole structure.

1 / 5

Imagine you're transferring $1,000 from your savings account to your checking account. The database successfully deducts $1,000 from savings—and then the power goes out. Your checking account was never credited. You've just lost $1,000 to the void.

This scenario represents one of the most fundamental problems in database systems: partial execution. Without proper safeguards, any interruption—power failure, hardware crash, network timeout, or software bug—can leave data in an inconsistent, corrupted state. Money disappears. Inventory counts become wrong. Orders exist without payments, or payments exist without orders.

Atomicity is the ACID property that prevents this nightmare. It guarantees that a transaction is an indivisible unit of work: either every operation within the transaction completes successfully, or none of them take effect. There is no middle ground, no partial state, no 'half-executed' transaction polluting your data.

Atomicity: Possible Transaction Outcomes

Outcome

Database State

Changes

Visibility

Committed

Reflects all transaction changes

Permanently recorded

Visible to all other transactions

Aborted

Unchanged from before transaction

Completely undone

As if transaction never happened

Log Sequence Number (LSN): 1001 Transaction: T1 BEGIN Timestamp: 2024-01-15 10:30:00.001 LSN: 1002 Transaction: T1 UPDATE accounts Before Image: {account_id: 'savings', balance: 5000} After Image: {account_id: 'savings', balance: 4000} LSN: 1003 Transaction: T1 UPDATE accounts Before Image: {account_id: 'checking', balance: 2000} After Image: {account_id: 'checking', balance: 3000} LSN: 1004 Transaction: T1 COMMIT Timestamp: 2024-01-15 10:30:00.045

Recovery Action Based on Transaction State at Crash

Transaction State at Crash

Log Contains

Recovery Action

Final State

Committed (COMMIT record present)

COMMIT record

Redo all operations

Fully committed, changes preserved

Active (no COMMIT record)

Only BEGIN and operations

Undo all operations

Fully rolled back, no trace

Preparing (2PC participant)

PREPARE but no COMMIT

Wait for coordinator decision

Depends on distributed protocol

10:30:00.001 T1: BEGIN 10:30:00.010 T1: UPDATE accounts (savings -= 1000) 10:30:00.020 T1: UPDATE accounts (checking += 1000) 10:30:00.025 T1: COMMIT written to log 10:30:00.030 Data page for savings written to disk 10:30:00.035 CRASH (checking page not yet written)

-- SQL: Explicit transaction for order processing BEGIN TRANSACTION; -- Insert order header INSERT INTO orders (order_id, customer_id, order_date, status) VALUES ('ORD-2024-001', 'CUST-500', CURRENT_TIMESTAMP, 'pending'); -- Insert order line items INSERT INTO order_items (order_id, product_id, quantity, unit_price) VALUES ('ORD-2024-001', 'PROD-100', 2, 29.99); INSERT INTO order_items (order_id, product_id, quantity, unit_price) VALUES ('ORD-2024-001', 'PROD-205', 1, 149.99); -- Deduct inventory UPDATE inventory SET quantity = quantity - 2 WHERE product_id = 'PROD-100'; UPDATE inventory SET quantity = quantity - 1 WHERE product_id = 'PROD-205'; -- Create payment authorization INSERT INTO payment_auth (order_id, amount, auth_code) VALUES ('ORD-2024-001', 209.97, 'AUTH-789456'); COMMIT; -- If any statement fails, the entire transaction rolls back -- Either the complete order exists, or nothing exists

# Python: Transaction with error handling def transfer_funds(from_account: str, to_account: str, amount: Decimal) -> bool: connection = get_db_connection() try: connection.begin_transaction() # Check sufficient funds from_balance = connection.execute( "SELECT balance FROM accounts WHERE account_id = %s FOR UPDATE", (from_account,) ).fetchone()['balance'] if from_balance < amount: connection.rollback() # Explicit rollback on business rule violation raise InsufficientFundsError(f"Balance {from_balance} < {amount}") # Execute transfer connection.execute( "UPDATE accounts SET balance = balance - %s WHERE account_id = %s", (amount, from_account) ) connection.execute( "UPDATE accounts SET balance = balance + %s WHERE account_id = %s", (amount, to_account) ) connection.commit() return True except Exception as e: connection.rollback() # Ensure rollback on any exception logger.error(f"Transfer failed: {e}") raise finally: connection.close()

BEGIN TRANSACTION; INSERT INTO orders (order_id, customer_id) VALUES ('ORD-100', 'CUST-1'); SAVEPOINT before_premium_items; -- Attempt to add premium items (might fail due to inventory) INSERT INTO order_items (order_id, product_id) VALUES ('ORD-100', 'PREMIUM-1'); UPDATE inventory SET quantity = quantity - 1 WHERE product_id = 'PREMIUM-1'; -- If premium item unavailable, rollback just that part -- ROLLBACK TO SAVEPOINT before_premium_items; -- Continue with regular items INSERT INTO order_items (order_id, product_id) VALUES ('ORD-100', 'REGULAR-1'); UPDATE inventory SET quantity = quantity - 1 WHERE product_id = 'REGULAR-1'; COMMIT; -- Order created with whatever items were available

BEGIN TRANSACTION; -- Business logic INSERT INTO orders (...); UPDATE inventory SET (...); -- Instead of calling external services directly... -- Store the intent in an outbox table INSERT INTO outbox (event_type, payload, status) VALUES ('OrderCreated', '{order_id: 123, ...}', 'pending'); INSERT INTO outbox (event_type, payload, status) VALUES ('SendConfirmationEmail', '{to: customer@..., order_id: 123}', 'pending'); COMMIT;

Atomicity: The All-or-Nothing Guarantee

When Half a Transaction Is Worse Than No Transaction

Defining Atomicity: The Indivisible Transaction

Why Atomicity Matters: The Consequences of Partial Execution

How Databases Achieve Atomicity: The Log-Based Approach

Atomicity During Crashes: Recovery Guarantees

Atomicity in Practice: Application Patterns

Atomicity Beyond the Database: Distributed Challenges

Common Misconceptions About Atomicity

There’s more to understand.

Atomicity: The All-or-Nothing Guarantee

When Half a Transaction Is Worse Than No Transaction

Defining Atomicity: The Indivisible Transaction

Why Atomicity Matters: The Consequences of Partial Execution

How Databases Achieve Atomicity: The Log-Based Approach

Atomicity During Crashes: Recovery Guarantees

Atomicity in Practice: Application Patterns

Atomicity Beyond the Database: Distributed Challenges

Common Misconceptions About Atomicity

There’s more to understand.