← Back to Databases

ACID Properties Explained: Database Transactions for Beginners

ACID properties are four guarantees that ensure database transactions are reliable and predictable. They stand for Atomicity, Consistency, Isolation, and Durability—and they're what keep your data safe when things go wrong.

Imagine transferring $100 between bank accounts. You need the withdrawal from one account and deposit into another to happen together, completely or not at all. That's what ACID promises. Without these guarantees, you could lose money or end up with duplicate transactions. Let's break down each property and see why they matter.

What Is a Database Transaction?

A transaction is a sequence of database operations grouped into a single logical unit. It's all-or-nothing: either every operation succeeds, or the entire transaction rolls back as if nothing happened.

Think of a transaction as a checkpoint. You start a transaction, make several changes, and then either commit them (save permanently) or rollback (undo everything). This prevents partial updates that could corrupt your data.

Here's a simple transaction in SQL:

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

If anything fails between the BEGIN and COMMIT, the database rolls back both updates automatically. Neither account balance changes.

The Four ACID Properties

1. Atomicity: All or Nothing

Atomicity means a transaction is atomic—indivisible. It either completes fully or doesn't execute at all. There's no in-between state.

Suppose your transaction has three steps: debit account A, credit account B, and log the transaction. If step two fails, atomicity guarantees that steps one and three are also rolled back. You won't have a debit without a credit, which would be a nightmare for accounting.

Here's an example of what atomicity prevents:

-- Without atomicity, this could fail mid-way:
BEGIN;
  INSERT INTO transactions (id, amount, date) VALUES (5, 100, NOW());
  UPDATE accounts SET balance = balance - 100 WHERE id = 1; -- What if this fails?
  UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;

With atomicity, if the second UPDATE fails, all three operations are undone. You won't have an orphaned transaction record.

2. Consistency: Valid State to Valid State

Consistency guarantees that the database moves from one valid state to another valid state. All rules, constraints, and triggers are enforced before a transaction commits.

For example, if you have a constraint that account balance can't be negative, a transaction that would violate this gets rejected outright. The database won't let you commit an invalid state.

Let's say you're enforcing a foreign key constraint:

-- This transaction would fail due to consistency rules:
BEGIN;
  INSERT INTO orders (customer_id, amount) VALUES (999, 50);
  -- If customer_id 999 doesn't exist, the foreign key constraint prevents this
COMMIT;

The database rejects this before committing, maintaining consistency. You're never left with an order pointing to a nonexistent customer.

3. Isolation: No Interference

Isolation ensures that concurrent transactions don't interfere with each other. Each transaction executes as if it's the only one running, even though others might be happening simultaneously.

Without isolation, Transaction A could read data that Transaction B is currently modifying, leading to "dirty reads." Or Transaction A could see different values for the same row at different points, causing "phantom reads."

Here's a scenario where isolation matters:

-- Transaction 1
BEGIN;
  SELECT balance FROM accounts WHERE id = 1; -- Returns 1000
  SLEEP(5); -- Simulate work
  SELECT balance FROM accounts WHERE id = 1; -- Should still be 1000
COMMIT;

-- Transaction 2 (happens during Transaction 1)
BEGIN;
  UPDATE accounts SET balance = 500 WHERE id = 1;
COMMIT;

Isolation levels control how much transactions can see each other's changes. Most databases support four levels:

• Read Uncommitted: Can read uncommitted data (dirty reads allowed)
• Read Committed: Only reads committed data (prevents dirty reads)
• Repeatable Read: Data doesn't change within a transaction (prevents non-repeatable reads)
• Serializable: Transactions execute completely isolated, as if in sequence

PostgreSQL and MySQL let you set isolation levels per transaction:

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
BEGIN;
  -- Your queries here
COMMIT;

4. Durability: Permanent Once Committed

Durability guarantees that once a transaction commits, the data is permanent—even if the database crashes immediately after. It survives hardware failures, power outages, and software crashes.

This is why databases use write-ahead logging (WAL). Before committing, the database writes changes to a durable storage medium (usually disk). If it crashes, it recovers from this log.

You don't have to do anything special for durability; the database handles it. But it's why committing takes slightly longer than just updating memory—the data has to hit disk first.

BEGIN;
  UPDATE accounts SET balance = balance + 50 WHERE id = 1;
COMMIT; -- Data is now guaranteed to survive crashes

After COMMIT returns successfully, that update is durable. Period.

Real-World Example: Money Transfer

Let's tie this together with a complete banking example:

BEGIN TRANSACTION;
  -- Debit from source account
  UPDATE accounts SET balance = balance - 100 WHERE id = 1;
  
  -- Credit to destination account
  UPDATE accounts SET balance = balance + 100 WHERE id = 2;
  
  -- Log the transaction
  INSERT INTO audit_log (source, destination, amount, timestamp)
  VALUES (1, 2, 100, NOW());
COMMIT;

Atomicity: All three operations succeed or all roll back. No partial transfers.

Consistency: Balance constraints are checked. You can't transfer if source balance goes negative (if that's your rule).

Isolation: Other transactions can't see intermediate states. No one reads the balance after the debit but before the credit.

Durability: Once committed, the transfer survives any failure.

When ACID Properties Break Down

Not all databases enforce strict ACID properties. NoSQL databases like MongoDB and Cassandra often trade ACID guarantees for scalability and performance.

That doesn't make them bad—it makes them different. Use them when you need horizontal scaling and can tolerate eventual consistency. Use relational databases (PostgreSQL, MySQL, Oracle) when you need strong ACID guarantees.

Modern databases let you configure ACID levels. You might use READ COMMITTED isolation in high-throughput systems to reduce lock contention, trading some safety for speed. The key is understanding the tradeoffs.

Testing ACID Compliance

If you're building applications, you should test transaction behavior. Create scenarios where operations might fail—network timeouts, constraint violations, concurrent updates—and verify your database handles them correctly.

For PostgreSQL, use psql to experiment with isolation levels and see how concurrent transactions behave. For MySQL, try similar tests with multiple client connections. Understanding ACID in practice beats memorizing theory.

Next Steps

Now that you understand ACID properties, explore database normalization to design better schemas. Learn about indexes and query optimization to keep transactions fast. And dive into locks and deadlocks to handle concurrent transactions safely.

Practice writing transactions in your database of choice. Create test scenarios where things fail and verify your assumptions about atomicity and consistency. That hands-on experience is where ACID concepts truly click.