Database Transactions: ACID, Isolation Levels, and Locks

Databases Databases 8 min read 1571 words Beginner ExcellentWiki Editorial Team

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.

What Is a Transaction?

A transaction is a sequence of database operations treated as a single logical unit. In SQL, transactions are 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.

Atomicity

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

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.

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)
);

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.

Isolation

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

Read Phenomena

Dirty Read: reading uncommitted data from another transaction that may roll back.

Non-Repeatable Read: the same row returns different values when read twice within the same transaction because another transaction committed an update.

Phantom Read: new rows matching a query condition appear between two executions of the same query, inserted by another transaction.

Transaction Isolation Levels

Level	Dirty Read	Non-Repeatable Read	Phantom Read
READ UNCOMMITTED	Possible	Possible	Possible
READ COMMITTED	Prevented	Possible	Possible
REPEATABLE READ	Prevented	Prevented	Possible
SERIALIZABLE	Prevented	Prevented	Prevented

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
BEGIN;
-- ...
COMMIT;

READ COMMITTED is the default in PostgreSQL, SQL Server, and Oracle. It prevents dirty reads and is sufficient for most applications.

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.

SERIALIZABLE is the strictest level. It prevents all anomalies by executing transactions as if they ran one after another. Use it for financial reconciliation and inventory management.

How Isolation Is Implemented

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;
UPDATE inventory SET quantity = quantity - 1 WHERE product_id = 100;
COMMIT;

Optimistic concurrency (MVCC — Multi-Version Concurrency Control) gives each transaction 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 before acknowledging the commit.

In practice, durability has nuances: write-ahead logging, fsync, synchronous replication, and asynchronous commits that trade durability for speed.

Locking in Practice

Row-Level Locks

Most databases default to row-level locking within transactions. A row lock prevents concurrent writes to the same row but allows reads (with MVCC).

Table-Level Locks

Explicit table locks prevent concurrent access to the entire table:

LOCK TABLE orders IN ACCESS EXCLUSIVE MODE;

Deadlocks

Deadlocks occur when two transactions each hold a lock the other needs. Databases detect deadlocks and abort one transaction so the other can proceed. Applications must retry aborted transactions.

-- Transaction A             Transaction B
BEGIN;                       BEGIN;
UPDATE accounts SET          UPDATE accounts SET
  balance = balance - 100      balance = balance + 100
  WHERE id = 1;                WHERE id = 2;
UPDATE accounts SET          UPDATE accounts SET
  balance = balance + 100      balance = balance - 100
  WHERE id = 2;                WHERE id = 1;
-- Deadlock!                 -- Deadlock!

Acquire locks in a consistent order across all transactions to prevent deadlocks.

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');
ROLLBACK TO SAVEPOINT order_saved;
INSERT INTO order_items (order_id, product) VALUES (1, 'Tablet');
COMMIT;

Understanding transaction management is essential for building reliable database applications. The right isolation level depends on your concurrency requirements and tolerance for stale reads. Start with READ COMMITTED, measure, and increase isolation only when your application requires it.

Optimistic vs Pessimistic Locking

Optimistic Locking

Optimistic locking assumes conflicts are rare. Transactions read data without locks and check for conflicts before committing. Implemented with version numbers or timestamps:

-- Read with version
SELECT id, balance, version FROM accounts WHERE id = 1;

-- Update — version check ensures no concurrent modification
UPDATE accounts
SET balance = balance - 100, version = version + 1
WHERE id = 1 AND version = 5;

-- If affected rows = 0, another transaction modified the data — retry

Optimistic locking works well when contention is low — most transactions succeed on the first try. Applications must handle the conflict retry logic explicitly.

Pessimistic Locking

Pessimistic locking prevents conflicts by locking rows or tables before modification. SELECT ... FOR UPDATE locks rows until the transaction completes. Pessimistic locking is simpler at the application level (no retry logic) but reduces concurrency and risks deadlocks.

Choose optimistic locking for read-heavy workloads with low write contention. Choose pessimistic locking when write conflicts are frequent or when retries are expensive (long-running transactions).

Lock Escalation

Databases automatically escalate row-level locks to table-level locks when too many rows in a table are locked (configurable threshold, typically several thousand rows). Lock escalation reduces memory usage on the database server but can dramatically reduce concurrency. Monitor lock escalation events and investigate queries that trigger them.

Distributed Transactions

Transactions spanning multiple databases require distributed transaction coordination. The XA standard defines a two-phase commit protocol supported by most relational databases and transaction monitors. Distributed transactions add significant latency and complexity — in practice, avoid them by keeping related data in the same database or by using saga patterns.

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.