Database ACID: Four Guarantees That Make Transactions Reliable

8 min readdatabasetransaction

Imagine you are building a money transfer feature for a banking app or a digital wallet.

User A sends 100,000 IDR to User B. This needs at least two data operations:

1. Reduce the balance of User A by 100,000 IDR.
2. Add 100,000 IDR to the balance of User B.

If both operations run as two separate SQL queries without a transaction, serious problems can happen at any time. Imagine the application server or the database dies right after the first query finishes. The balance of User A is already reduced, but the balance of User B has not increased yet. The money is lost from the system records.

This kind of situation is the main reason why we need transactions and the ACID guarantees in a database.

What is a transaction?

A transaction is one logical unit of work that contains one or more SQL statements. The main characteristic of a transaction is that it is all-or-nothing: all statements inside it must finish together, or all of them are canceled as if they never happened.

In a standard SQL database, a transaction is controlled with three main commands:

• BEGIN (or START TRANSACTION): marks the start of the operations in the transaction.
• COMMIT: saves all data changes permanently to the database.
• ROLLBACK: cancels all operations that ran since the BEGIN command and returns the data to its previous state.

Mental Model

ACID is an acronym for the four core guarantees that relational databases give to every transaction: Atomicity, Consistency, Isolation, and Durability. These guarantees keep the data integrity safe even when the system fails, the hardware crashes, or many users access the database at the same time.

Loading diagram...

Atomicity: All succeed or nothing happens

Atomicity makes sure that all statements inside one transaction are treated as one unit that cannot be split. If one statement fails or an error happens before COMMIT, the database automatically cancels all changes already made inside that transaction.

Here is a transfer transaction with rollback handling:

BEGIN;

-- Step 1: Reduce the sender balance
UPDATE accounts
SET balance = balance - 100000
WHERE account_id = 'ACC-001';

-- Step 2: Add to the receiver balance
UPDATE accounts
SET balance = balance + 100000
WHERE account_id = 'ACC-002';

-- If both steps finish without errors, save permanently
COMMIT;

If a network problem or a syntax error happens at the second step, the application or the database calls ROLLBACK. The balance reduction of User A, which already happened inside the working memory of the database, is canceled right away. The balance of User A returns to the same value as before the transaction started.

Consistency: Keep data rules valid

Consistency guarantees that a transaction only moves the database from one valid state to another valid state. Every data change must follow all the rules, integrity constraints, and the schema defined on the tables.

Rules that the database enforces include:

• NOT NULL: the column must have a value.
• UNIQUE: the column value cannot be duplicated, like an email or an account number.
• CHECK constraint: the column value must match an expression, for example CHECK (balance >= 0).
• FOREIGN KEY: relations between tables must always be valid and point to data that really exists.

Here is a schema example with a safety constraint:

CREATE TABLE accounts (
  account_id VARCHAR(32) PRIMARY KEY,
  owner_name VARCHAR(100) NOT NULL,
  balance NUMERIC(12, 2) NOT NULL,
  CONSTRAINT positive_balance CHECK (balance >= 0)
);

If User A only has 50,000 IDR and tries to transfer 100,000 IDR, the balance reduction query would produce a negative value (-50000). The database detects a violation of the CHECK (balance >= 0) constraint and rejects the query with an error. The transaction fails before the invalid data can be saved.

The database only enforces rules that are declared explicitly in the table schema. High-level business rules that are not written as database constraints are still the responsibility of your application code.

Isolation: Handle transactions that run at the same time

In production, thousands of transactions can run in the same second. The Isolation guarantee makes sure that transactions running at the same time (concurrent) do not damage each other or produce wrong data.

The lost update problem

The most common concurrency problem at work is a lost update. Imagine two cashier processes reading the same product stock at the same time:

1. Transaction 1 reads the stock: 10 units.
2. Transaction 2 reads the same stock: 10 units.
3. Transaction 1 sells 1 unit, calculates 10 - 1 = 9, and saves 9 to the database.
4. Transaction 2 sells 2 units, calculates 10 - 2 = 8, and overwrites the same row with 8.

The final stock is 8 units, but 3 units were sold in total (the correct stock should be 7). The change from Transaction 1 is overwritten and lost because both transactions read the same initial data at the same time.

Default isolation level in popular databases

Databases provide several transaction isolation levels with a trade-off between data safety and throughput:

• PostgreSQL: the default level is READ COMMITTED. A transaction can only read data that is already committed by other transactions.
• MySQL (InnoDB): the default level is REPEATABLE READ. Reads inside a transaction stay consistent with the snapshot of the data from the moment the transaction first reads the row.

Row locking with SELECT FOR UPDATE

To prevent the lost update problem in critical transactions like balance or stock changes, you can use row locking (pessimistic locking):

BEGIN;

-- Lock the sender and receiver rows so other transactions must wait
SELECT balance
FROM accounts
WHERE account_id IN ('ACC-001', 'ACC-002')
ORDER BY account_id
FOR UPDATE;

-- Run the balance updates safely
UPDATE accounts
SET balance = balance - 100000
WHERE account_id = 'ACC-001';

UPDATE accounts
SET balance = balance + 100000
WHERE account_id = 'ACC-002';

COMMIT;

The FOR UPDATE clause locks the rows ACC-001 and ACC-002 until the calling transaction runs COMMIT or ROLLBACK. Other transactions that try to access or modify those rows will wait in line safely.

Notice the extra ORDER BY account_id before FOR UPDATE. Rows are locked one by one in the order of the query result. Without a consistent order, two transactions that transfer money in opposite directions (A to B, and B to A) can lock different rows at almost the same time and then wait for each other forever. This condition is called a deadlock. Always lock multiple rows in a consistent order, for example by account_id or id.

Further reading

To understand how a deadlock happens step by step, how the database detects it and picks a victim, and another example case with batch workers, read the article Database Deadlock: When Two Transactions Wait on Each Other Forever.

Durability: Data is safe after commit

Durability makes sure that once a transaction succeeds through the COMMIT confirmation, all data changes are saved permanently and survive even if the database server dies, restarts, or loses power.

The role of Write-Ahead Logging (WAL)

Writing all changed table pages to disk in random positions on every transaction takes a long time. To keep transactions fast and still keep durability, modern databases use a mechanism called Write-Ahead Logging (WAL in PostgreSQL, or redo log in MySQL):

1. When a transaction commits, the database first writes the transaction changes to an append-only log file on disk.
2. After the log record is safely stored on the storage media, the database confirms to the application that the transaction succeeded (COMMIT).
3. The actual writing to the main table file can happen later in the background.

If the server dies a few seconds after a commit, the database process reads the WAL log file again at startup and replays all changes that were not yet synced to the main table.

Further reading

To understand how WAL works in full, including the COMMIT and SELECT flow through the buffer cache, when a checkpoint runs, and why writing to WAL is faster than writing to the main table, read the article Write-Ahead Log: Why Databases Write Twice but Stay Fast.

Transaction example in Node.js

Here is an example of a balance transfer transaction using the PostgreSQL library in Node.js:

const { Pool } = require("pg");
const pool = new Pool({ connectionString: process.env.DATABASE_URL });

async function transferBalance(senderId, receiverId, amount) {
  const client = await pool.connect();

  try {
    // Start the transaction
    await client.query("BEGIN");

    // Validate and lock the sender and receiver rows to prevent lost update
    const lockedRes = await client.query(
      `SELECT account_id, balance
       FROM accounts
       WHERE account_id = ANY($1::text[])
       ORDER BY account_id
       FOR UPDATE`,
      [[senderId, receiverId]],
    );

    const balances = Object.fromEntries(
      lockedRes.rows.map((row) => [row.account_id, Number(row.balance)]),
    );

    if (balances[senderId] === undefined) {
      throw new Error("Sender account not found");
    }
    if (balances[receiverId] === undefined) {
      throw new Error("Receiver account not found");
    }

    if (balances[senderId] < amount) {
      throw new Error("Insufficient sender balance");
    }

    // Run the balance changes
    await client.query(
      "UPDATE accounts SET balance = balance - $1 WHERE account_id = $2",
      [amount, senderId],
    );

    await client.query(
      "UPDATE accounts SET balance = balance + $1 WHERE account_id = $2",
      [amount, receiverId],
    );

    // Save all changes permanently
    await client.query("COMMIT");
    return { success: true };
  } catch (error) {
    // Cancel all changes when something fails
    await client.query("ROLLBACK");
    throw error;
  } finally {
    // Always return the client to the connection pool
    client.release();
  }
}

The try ... catch ... finally block above makes sure of three important things:

• Changes are saved permanently with COMMIT when all queries succeed.
• Changes are canceled with ROLLBACK when a failure happens in the middle of the process.
• The connection is always returned to the pool through client.release() so a connection leak cannot happen.

Where ACID matters at work

ACID guarantees are critical for systems that demand perfect data accuracy, such as:

• Financial systems, digital wallets, and banking transaction records.
• Inventory management and stock reservation systems in e-commerce.
• Accounting books and invoicing.

In large-scale or distributed architectures (microservices), keeping all four ACID guarantees across separate databases often lowers performance and availability. As a compromise, distributed systems often use a gradual consistency model (eventual consistency). In this model, data across services becomes consistent after a short delay through a message broker or asynchronous events.

Summary

• A transaction is a set of data operations treated as one complete unit of work.
• Atomicity makes sure all statements in a transaction succeed together or are canceled together.
• Consistency guarantees every transaction follows the configured schema and data constraints.
• Isolation stops transactions running in parallel from damaging each other through isolation levels and row locking.
• Durability keeps committed data safe through the write-ahead log mechanism even if the system crashes.

References

• PostgreSQL Documentation: Transactions (PostgreSQL Official Docs)
• PostgreSQL Documentation: Transaction Isolation (PostgreSQL Official Docs)
• MySQL Documentation: InnoDB Transaction Model (MySQL Official Docs)