What are transactions and ACID properties?

Question

Accepted Answer

A **transaction** is a group of operations treated as a single, indivisible unit — they all succeed together (`COMMIT`) or all fail together (`ROLLBACK`). Transactions guarantee **ACID** properties, ensuring data stays correct and consistent even with failures or concurrent access.

## A transaction — all-or-nothing

```sql
BEGIN;                                              -- start the transaction
  UPDATE accounts SET balance = balance - 100 WHERE id = 1;   -- debit
  UPDATE accounts SET balance = balance + 100 WHERE id = 2;   -- credit
COMMIT;                                             -- both succeed → save permanently
-- if anything fails: ROLLBACK → undo EVERYTHING (no partial transfer)
```

The classic example: a money transfer. Both the debit and credit must happen, or neither — otherwise money is lost or created. A transaction guarantees this: on error, `ROLLBACK` undoes all changes, leaving the database as if nothing happened.

## ACID — the four guarantees

```text
A — ATOMICITY    → all operations succeed or all fail (no partial transactions)
C — CONSISTENCY  → the database moves from one valid state to another (constraints hold)
I — ISOLATION    → concurrent transactions don't interfere (as if run sequentially)
D — DURABILITY   → once committed, changes survive crashes (persisted permanently)
```

These properties are what make relational databases reliable for critical data.

## Why atomicity matters (the core benefit)

```text
Without transactions, a failure mid-way leaves data in an inconsistent state:
  money debited from one account but not credited to another → data corruption.
Atomicity (all-or-nothing) prevents this — the cornerstone of data integrity for
any multi-step operation (transfers, orders with line items, etc.).
```

## Isolation and concurrency

```text
When many transactions run concurrently, ISOLATION controls what they can see of
each other's uncommitted changes. Isolation LEVELS (Read Committed, Repeatable Read,
Serializable) trade off consistency vs performance — higher isolation = more correct
but more locking/contention. (A deeper topic on its own.)
```

## Why it matters

Transactions and ACID properties are fundamental to reliable databases — they're what guarantee data stays correct and consistent even amid failures and concurrent access, which is essential for any application handling important data, so understanding them is important.

The core value is **atomicity** (all-or-nothing): any operation involving multiple steps (a money transfer, creating an order with line items, updating related records) must succeed completely or not at all — otherwise a mid-operation failure leaves the database in a **corrupt, inconsistent state** (the classic example: money debited but not credited).

Transactions guarantee this with `COMMIT`/`ROLLBACK`, making them essential for data integrity in multi-step operations.

Understanding the full **ACID** properties — **Atomicity** (all-or-nothing), **Consistency** (valid states), **Isolation** (concurrent transactions don't interfere), and **Durability** (committed changes survive crashes) — explains why relational databases are trusted for critical data (finance, orders, inventory).

Knowing that **isolation** controls concurrency behavior (with isolation levels trading consistency for performance) connects to deeper concurrency topics.

Since data integrity is critical for reliable applications, and since transactions (providing atomicity and the ACID guarantees) are the mechanism that ensures multi-step operations don't corrupt data and that the database stays consistent under failures and concurrency, understanding transactions and ACID is important, foundational knowledge for building applications with reliable data — it's a cornerstone of database reliability and a common, important interview topic that demonstrates understanding of how databases guarantee correctness.