ACID Transactions in Databases: A Complete Guide

Transactions are the foundation of reliable data operations in relational databases. They group multiple operations into a single unit that either succeeds completely or fails without leaving partial changes. The ACID properties — Atomicity, Consistency, Isolation, Durability — define what makes a transaction reliable. Without ACID, a banking transfer could debit one account without crediting another, or an e-commerce order could charge a customer without reserving inventory.

What Is a Transaction?

A transaction is a sequence of database operations treated as a single logical unit. In SQL, transactions are typically delimited by BEGIN, COMMIT, and ROLLBACK:

BEGIN;

UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;

COMMIT;

If either UPDATE fails, the entire transaction is rolled back — both accounts remain unchanged. Partial updates never leak to other readers.

Auto-Commit Mode

By default, most databases operate in auto-commit mode where each statement is its own implicit transaction. Explicit transactions become necessary when multiple statements must succeed or fail together.

Atomicity

Atomicity guarantees that all operations within a transaction complete successfully, or none are applied. There is no partial execution.

BEGIN;

-- If this fails, the INSERT is also rolled back
DELETE FROM orders WHERE id = 100;
INSERT INTO order_log VALUES (100, 'deleted', NOW());

COMMIT;

Atomicity is implemented through write-ahead logging (WAL). The database writes changes to a log before applying them to the data files. If a crash occurs mid-transaction, the recovery process uses the log to undo partial changes.

Atomicity in Practice

Consider an email system: when you move a message to a folder, the database must update the folder reference and the folder’s message count atomically. Without atomicity, a crash between the two updates would leave the count inconsistent with the actual contents.

Consistency

Consistency ensures that a transaction brings the database from one valid state to another valid state. All defined rules — constraints, cascades, triggers, and any combination thereof — must be satisfied:

CREATE TABLE accounts (
    id SERIAL PRIMARY KEY,
    owner VARCHAR(100) NOT NULL,
    balance NUMERIC(10, 2) CHECK (balance >= 0)
);

BEGIN;
UPDATE accounts SET balance = balance - 200 WHERE id = 1;
UPDATE accounts SET balance = balance + 200 WHERE id = 2;
COMMIT;
-- If account 1 had only 150, the CHECK constraint
-- makes the UPDATE fail and the transaction rolls back

Consistency is the broadest ACID property because it encompasses all application-specific rules encoded in the schema. Foreign key constraints, unique indexes, and CHECK constraints are all consistency mechanisms.

Referential Integrity

BEGIN;
DELETE FROM departments WHERE id = 10;
-- Fails if employees still reference department 10
COMMIT;

-- Instead, handle it explicitly:
BEGIN;
UPDATE employees SET dept_id = NULL WHERE dept_id = 10;
DELETE FROM departments WHERE id = 10;
COMMIT;

Isolation

Isolation determines how concurrent transactions interact. Without proper isolation, concurrent transactions can experience three classic anomalies:

Read Phenomena

-- Dirty Read: reading uncommitted data
-- Transaction A                Transaction B
BEGIN;                          BEGIN;
UPDATE products SET price = 0
WHERE id = 1;
                                SELECT price FROM products WHERE id = 1;
                                -- Returns 0 (uncommitted!)
ROLLBACK;                       -- A rolled back, but B used bad data

-- Non-Repeatable Read: same row, different values
-- Transaction A                Transaction B
BEGIN;                          BEGIN;
SELECT price FROM products
WHERE id = 1;  -- 10
                                UPDATE products SET price = 20
                                WHERE id = 1;
                                COMMIT;
SELECT price FROM products
WHERE id = 1;  -- 20 (different!)
COMMIT;

-- Phantom Read: new rows appear
-- Transaction A                Transaction B
BEGIN;                          BEGIN;
SELECT COUNT(*) FROM orders
WHERE total > 100;  -- 5
                                INSERT INTO orders (total)
                                VALUES (200);
                                COMMIT;
SELECT COUNT(*) FROM orders
WHERE total > 100;  -- 6 (phantom!)
COMMIT;

Transaction Isolation Levels

SQL defines four isolation levels that trade consistency for performance:

-- Set isolation level for a transaction
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
BEGIN;
-- ...
COMMIT;

-- Or per-session
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;

Choosing an Isolation Level

READ COMMITTED is the default in PostgreSQL, SQL Server, and Oracle. It prevents dirty reads and is sufficient for most applications. It is the best balance of consistency and concurrency for general-purpose workloads.

REPEATABLE READ prevents non-repeatable reads within a transaction. Use it when a transaction reads the same row multiple times and must see consistent values — for example, calculating a sum over a set of rows while preventing concurrent updates to those rows.

SERIALIZABLE is the strictest level. It prevents all anomalies by executing transactions as if they ran one after another. Use it for financial reconciliation, inventory management, or any scenario where even phantom reads are unacceptable. The trade-off is reduced concurrency and more transaction retries.

-- PostgreSQL: SERIALIZABLE detects conflicts and may return
-- "could not serialize access" error — application must retry
BEGIN ISOLATION LEVEL SERIALIZABLE;
UPDATE inventory SET quantity = quantity - 1 WHERE product_id = 100;
INSERT INTO order_items (product_id, quantity) VALUES (100, 1);
COMMIT;

How Isolation Is Implemented

Databases use two primary mechanisms:

Pessimistic locking (SELECT FOR UPDATE) — Locks rows when they are read, preventing other transactions from modifying them:

BEGIN;
SELECT quantity FROM inventory
WHERE product_id = 100
FOR UPDATE;  -- Locks this row until COMMIT
-- Other transactions block here
UPDATE inventory SET quantity = quantity - 1 WHERE product_id = 100;
COMMIT;

Optimistic concurrency (MVCC — Multi-Version Concurrency Control) — Each transaction sees a snapshot of the data at the time it started. Writers do not block readers, and readers do not block writers. PostgreSQL, MySQL (InnoDB), and Oracle use MVCC.

Durability

Durability guarantees that once a transaction is committed, its changes survive permanently — even if the database crashes immediately after. The database ensures this by writing the transaction’s changes to non-volatile storage (disk) before acknowledging the commit.

BEGIN;
INSERT INTO audit_log (event) VALUES ('user_deleted');
COMMIT;  -- After this returns, the INSERT is durable

In practice, durability has nuances:

• Write-ahead log (WAL) — Changes are written to a sequential log file before the data files are updated. On recovery, the WAL replays committed transactions.
• fsync — The operating system’s fsync call forces data from the OS cache to the disk controller. The default PostgreSQL fsync=on ensures this happens.
• Synchronous replication — Some configurations consider a transaction durable only after it is written to at least one replica.
• Asynchronous commits — Trade durability for speed by acknowledging the commit before the WAL is flushed to disk. Acceptable for non-critical data (session state, analytics).

-- PostgreSQL: synchronous vs asynchronous commit
SET synchronous_commit = OFF;  -- faster, less durable
SET synchronous_commit = ON;   -- fully durable (default)

Practical Transaction Patterns

Savepoints

Savepoints allow partial rollback within a transaction:

BEGIN;
INSERT INTO orders (id, total) VALUES (1, 100);
SAVEPOINT order_saved;

INSERT INTO order_items (order_id, product) VALUES (1, 'Laptop');
-- Something goes wrong with items
ROLLBACK TO SAVEPOINT order_saved;

-- Try different items
INSERT INTO order_items (order_id, product) VALUES (1, 'Tablet');
COMMIT;

Retry Logic

With SERIALIZABLE isolation, transactions may fail due to serialization conflicts. Applications must retry:

import psycopg2
from psycopg2 import errors

def execute_with_retry(cursor, sql, params, max_retries=3):
    for attempt in range(max_retries):
        try:
            cursor.execute(sql, params)
            return
        except errors.SerializationFailure:
            if attempt == max_retries - 1:
                raise
            # Backoff and retry
            time.sleep(0.1 * (2 ** attempt))

Non-Relational Transactions

NoSQL databases have varying transaction support:

• MongoDB — Multi-document ACID transactions (since 4.0), but with a 60-second timeout
• DynamoDB — Transactions on up to 25 items across multiple tables (limited ACID)
• Cassandra — Lightweight transactions (compare-and-set) on single partitions only — no multi-row ACID

For workloads that require strong ACID guarantees across multiple records, relational databases remain the correct choice. NoSQL databases trade ACID for scalability and availability in specific use cases.

Two-Phase Commit (2PC)

Distributed transactions across multiple databases use two-phase commit to maintain atomicity. The coordinator sends a prepare request to all participants. If all respond “yes,” the coordinator sends a commit request. If any responds “no,” the coordinator sends an abort.

2PC guarantees atomicity but introduces blocking — if the coordinator fails after sending prepare but before sending commit, participants hold locks indefinitely until the coordinator recovers. This trade-off is why many distributed systems prefer eventual consistency over strict ACID.

Snapshot Isolation

Snapshot isolation gives each transaction a consistent snapshot of the database as of the transaction’s start time. Reads never block writes, and writes never block reads. PostgreSQL’s REPEATABLE READ and SERIALIZABLE levels both use snapshot isolation under the hood. The drawback is increased storage — old row versions must be retained for active snapshots, leading to bloat if not cleaned up.

Transaction Monitoring

Production databases require transaction monitoring to detect long-running transactions, blocking chains, and deadlocks:

-- PostgreSQL: find long-running transactions
SELECT pid, now() - pg_stat_activity.xact_start AS duration,
       state, query
FROM pg_stat_activity
WHERE state = 'active' AND xact_start IS NOT NULL
ORDER BY duration DESC
LIMIT 10;

-- SQL Server: detect blocking
SELECT blocking_session_id, session_id, wait_type, wait_time
FROM sys.dm_exec_requests
WHERE blocking_session_id > 0;

Long transactions increase the chance of conflicts and bloat. Keep transactions focused and short. If a transaction spans user think time, consider redesigning — use optimistic concurrency or deferred processing instead.

Application-Level Transactions

Some applications implement their own transaction-like semantics over eventually consistent stores. The Saga pattern breaks distributed transactions into a sequence of local transactions with compensating actions:

Order Service: Create order → pending
Inventory Service: Reserve items → done
Payment Service: Charge card → done
Order Service: Confirm order → confirmed

If payment fails, compensating transactions release inventory and cancel the order. Sagas provide atomicity without distributed locking, at the cost of increased application complexity.

FAQ

What is the difference between SQL and NoSQL? SQL databases use structured schemas with tables, relationships, and ACID transactions. NoSQL databases offer flexible schemas, horizontal scaling, and various data models (document, key-value, graph, column-family). Choose based on data structure and access patterns.

When should I use an index? Index queries filtering on the indexed column(s), particularly for WHERE clauses, JOINs, and ORDER BY. Avoid over-indexing — each index slows writes and consumes storage. Monitor slow queries to identify indexing opportunities.

What is database normalization? Normalization organizes data to reduce redundancy and improve integrity. First Normal Form (1NF) eliminates duplicate columns. Second Normal Form (2NF) removes partial dependencies. Third Normal Form (3NF) removes transitive dependencies.

How do I choose between PostgreSQL and MySQL? PostgreSQL offers more advanced features (JSONB, full-text search, custom types, better concurrency). MySQL is simpler to configure and widely available. Both are excellent choices; PostgreSQL is often preferred for complex applications.

What is a deadlock in databases? A deadlock occurs when two transactions each hold locks the other needs. The database detects deadlocks and kills one transaction automatically. Reduce deadlocks by accessing tables in the same order across transactions and keeping transactions short.

Related: Learn about SQL joins and database indexing.