Eric Campbell 6 min read Databases

SQL Transactions: ACID Properties Explained

A database transaction is a sequence of operations treated as a single logical unit of work. Either all operations succeed and the changes are saved, or if any operation fails, all changes are...

Key Insights

  • ACID properties guarantee database reliability by ensuring transactions are atomic (all-or-nothing), consistent (maintain data integrity), isolated (concurrent transactions don’t interfere), and durable (committed changes survive failures)
  • Isolation levels create trade-offs between data consistency and performance—choosing the wrong level leads to concurrency bugs like dirty reads or unnecessary locking that kills throughput
  • Most transaction failures in production stem from holding locks too long, improper error handling, or nesting transactions incorrectly rather than database engine problems

Introduction to Database Transactions

A database transaction is a sequence of operations treated as a single logical unit of work. Either all operations succeed and the changes are saved, or if any operation fails, all changes are discarded. This fundamental concept prevents partial updates that would leave your data in an inconsistent state.

Consider transferring money between bank accounts. You need to debit one account and credit another. If the debit succeeds but the credit fails due to a system crash, money disappears from the system. Transactions prevent this nightmare scenario.

BEGIN TRANSACTION;

UPDATE accounts 
SET balance = balance - 100 
WHERE account_id = 'A123';

UPDATE accounts 
SET balance = balance + 100 
WHERE account_id = 'B456';

COMMIT;

If anything goes wrong between BEGIN TRANSACTION and COMMIT, you can execute ROLLBACK to undo all changes made during the transaction. The database handles this automatically if your connection drops or the server crashes.

Atomicity: All or Nothing

Atomicity guarantees that transactions are indivisible. Every operation within a transaction must succeed for the transaction to succeed. If any operation fails, the entire transaction fails and the database state remains unchanged.

The database engine maintains atomicity through its transaction log. Before modifying data, it records what the data looked like before the change. If a rollback occurs, it uses this log to restore the original state.

BEGIN TRANSACTION;

INSERT INTO orders (order_id, customer_id, total) 
VALUES (1001, 500, 250.00);

-- This will fail due to foreign key constraint if customer 500 doesn't exist
INSERT INTO order_items (order_id, product_id, quantity) 
VALUES (1001, 999, 2);

COMMIT;

If customer 500 doesn’t exist, the foreign key constraint on the orders table fails. The database automatically rolls back both the order insertion and any order items that might have been inserted. You don’t get orphaned records.

When a constraint violation occurs, the database engine:

  1. Detects the violation before committing
  2. Marks the transaction for rollback
  3. Reverses all changes made during the transaction
  4. Returns an error to the application

Consistency: Maintaining Database Integrity

Consistency ensures transactions can only bring the database from one valid state to another valid state. The database enforces all constraints, cascades, triggers, and any combination thereof during the transaction.

This goes beyond just foreign keys. Check constraints, unique constraints, and custom business logic implemented in triggers all participate in maintaining consistency.

CREATE TABLE accounts (
    account_id VARCHAR(50) PRIMARY KEY,
    balance DECIMAL(15,2) NOT NULL,
    CONSTRAINT positive_balance CHECK (balance >= 0)
);

BEGIN TRANSACTION;

UPDATE accounts SET balance = balance - 500 WHERE account_id = 'A123';
-- If account A123 has $300, this transaction fails
-- The CHECK constraint prevents negative balances

COMMIT;

The consistency property means you can define your business rules once in the database schema, and the transaction system enforces them automatically. You don’t need defensive checks scattered throughout application code.

Consistency also interacts with atomicity. If a consistency rule is violated, atomicity ensures no partial changes persist. The database state before the transaction is preserved.

Isolation: Concurrent Transaction Handling

Isolation determines how transaction integrity is visible to other concurrent transactions. This is the most complex ACID property because it involves trade-offs between data consistency and performance.

SQL defines four isolation levels:

Read Uncommitted: Transactions can see uncommitted changes from other transactions (dirty reads). Fastest but least safe.

Read Committed: Transactions only see committed data. Prevents dirty reads but allows non-repeatable reads.

Repeatable Read: Guarantees that if a transaction reads a row, subsequent reads return the same data. Prevents non-repeatable reads but allows phantom reads.

Serializable: Full isolation. Transactions execute as if they ran sequentially. Prevents all concurrency anomalies but has performance costs.

-- Session 1
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
BEGIN TRANSACTION;
SELECT balance FROM accounts WHERE account_id = 'A123';
-- Returns 1000

-- Session 2 (concurrent)
BEGIN TRANSACTION;
UPDATE accounts SET balance = 500 WHERE account_id = 'A123';
COMMIT;

-- Back to Session 1
SELECT balance FROM accounts WHERE account_id = 'A123';
-- Returns 500 (non-repeatable read - value changed)
COMMIT;

With REPEATABLE READ isolation, the second SELECT in Session 1 would still return 1000, maintaining a consistent view of the data throughout the transaction.

-- Session 1 with REPEATABLE READ
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
BEGIN TRANSACTION;
SELECT balance FROM accounts WHERE account_id = 'A123';
-- Returns 1000

-- Session 2 completes the same update as before
-- ...

-- Back to Session 1
SELECT balance FROM accounts WHERE account_id = 'A123';
-- Still returns 1000 (consistent snapshot)
COMMIT;

Choose your isolation level based on your use case. Financial transactions typically need SERIALIZABLE or REPEATABLE READ. Read-heavy analytics might use READ COMMITTED to avoid locking overhead.

Durability: Permanent Changes

Durability guarantees that once a transaction commits, the changes persist even if the system crashes immediately afterward. This is accomplished through write-ahead logging (WAL).

Before modifying data pages in memory, the database writes a record of the change to a sequential log file on disk. When you commit, the database ensures the log entries are flushed to disk before returning success. Even if the database crashes before the modified data pages are written to disk, it can replay the log during recovery.

BEGIN TRANSACTION;

INSERT INTO audit_log (event_time, user_id, action) 
VALUES (CURRENT_TIMESTAMP, 1001, 'LOGIN');

UPDATE users 
SET last_login = CURRENT_TIMESTAMP 
WHERE user_id = 1001;

COMMIT;
-- At this point, changes are guaranteed to survive a crash

When COMMIT returns successfully, the database has written the transaction log to persistent storage. The actual data pages might still be in memory, but the changes are durable because they can be reconstructed from the log.

Database administrators configure durability through settings like synchronous commit. In PostgreSQL, for example, synchronous_commit = on forces log flushes to disk before commit returns. Setting it to off improves performance but risks losing recent commits in a crash.

Real-World Implementation Patterns

Application code needs proper transaction management with error handling and resource cleanup. Here’s a robust pattern in Python using PostgreSQL:

import psycopg2
from contextlib import contextmanager

@contextmanager
def transaction(connection):
    """Context manager for safe transaction handling"""
    try:
        yield connection
        connection.commit()
    except Exception as e:
        connection.rollback()
        raise
    finally:
        connection.close()

def transfer_funds(from_account, to_account, amount):
    conn = psycopg2.connect("dbname=bank user=app")
    
    with transaction(conn):
        cursor = conn.cursor()
        
        # Debit source account
        cursor.execute(
            "UPDATE accounts SET balance = balance - %s WHERE account_id = %s",
            (amount, from_account)
        )
        
        if cursor.rowcount == 0:
            raise ValueError(f"Account {from_account} not found")
        
        # Credit destination account
        cursor.execute(
            "UPDATE accounts SET balance = balance + %s WHERE account_id = %s",
            (amount, to_account)
        )
        
        if cursor.rowcount == 0:
            raise ValueError(f"Account {to_account} not found")
        
        # Record transaction
        cursor.execute(
            "INSERT INTO transfers (from_acct, to_acct, amount, transfer_time) "
            "VALUES (%s, %s, %s, CURRENT_TIMESTAMP)",
            (from_account, to_account, amount)
        )

This pattern ensures the connection commits only if all operations succeed. Any exception triggers a rollback. The finally clause guarantees connection cleanup.

Common Pitfalls and Best Practices

Don’t hold transactions open during user interaction. This is the most common transaction anti-pattern. Every second a transaction stays open, it holds locks and prevents other transactions from proceeding.

-- BAD: Transaction spans user interaction
BEGIN TRANSACTION;
SELECT * FROM products WHERE product_id = 123;
-- Application displays product to user
-- User takes 5 minutes to decide
UPDATE products SET quantity = quantity - 1 WHERE product_id = 123;
COMMIT;

-- GOOD: Transaction only during the update
-- Read product data outside transaction
SELECT * FROM products WHERE product_id = 123;
-- User makes decision
BEGIN TRANSACTION;
UPDATE products SET quantity = quantity - 1 
WHERE product_id = 123 AND quantity > 0;
COMMIT;

Keep transactions small and focused. Large transactions increase lock contention and rollback costs. Break batch operations into smaller transactions when possible.

Set appropriate timeouts. Configure statement and transaction timeouts to prevent runaway queries from blocking other work.

Use the weakest isolation level that meets your requirements. SERIALIZABLE is safe but slow. Most applications work fine with READ COMMITTED.

Avoid nested transactions. Most databases don’t truly support them. Use savepoints instead when you need partial rollback capability.

BEGIN TRANSACTION;

INSERT INTO orders (order_id, customer_id) VALUES (1, 100);

SAVEPOINT before_items;

INSERT INTO order_items (order_id, product_id) VALUES (1, 500);
-- If this fails, rollback to savepoint instead of entire transaction
ROLLBACK TO SAVEPOINT before_items;

COMMIT;

Understanding ACID properties transforms you from someone who uses databases to someone who uses them correctly. These guarantees are why we trust databases with critical data, but they come with performance costs. Know when to rely on full ACID guarantees and when you can relax requirements for better performance.

Liked this? There's more.

Every week: one practical technique, explained simply, with code you can use immediately.