Introduction

Database operations frequently involve several related changes. Creating an order can require inserting the order header, adding order lines, reserving inventory, recording payment state, and updating totals.

If only some of these changes succeed, the database can contain incomplete or contradictory information.

ACID describes four important properties of reliable database transactions:

• Atomicity: The transaction completes as one indivisible unit or has no effect.
• Consistency: A committed transaction preserves the defined integrity rules.
• Isolation: Concurrent transactions are controlled so that unsafe interference is prevented according to the selected isolation level.
• Durability: Once a successful commit is acknowledged, the committed changes survive the failures covered by the database's durability guarantee.

In your System Design curriculum, ACID is Topic 5.4 under Relational Data Modeling & SQL. It follows keys and constraints and precedes transactions, isolation, B-tree and composite indexes, query plans, and connection pools.

Prerequisites

What Is a Database Transaction?

A database transaction is one logical unit of work containing one or more database operations.

Begin transaction
      |
      v
Read required data
      |
      v
Perform related changes
      |
      v
Validate constraints and business rules
      |
      +--> Success:
      |       COMMIT
      |
      +--> Failure:
              ROLLBACK

The transaction boundary should normally represent a complete business operation rather than an arbitrary number of SQL statements.

Order Example

Business operation:

Create order


Database work:

1. Insert order header.
2. Insert order lines.
3. Reserve inventory.
4. Store order total.
5. Record idempotency outcome.


Required result:

All related changes succeed,
or none becomes the committed result.

The Four ACID Properties

Atomicity

Atomicity means that a transaction is treated as one indivisible unit. All its changes are committed, or none of its changes remains as the committed result.

Transfer Example

Transfer 100 units from Account A to Account B

Step 1:
Subtract 100 from Account A.

Step 2:
Add 100 to Account B.

If the system applies the first change but not the second, value disappears from the modeled system. Atomicity prevents this partial result from becoming the committed database state.

SQL Transfer Transaction

BEGIN;

UPDATE accounts
SET balance = balance - 100.00
WHERE account_id = 101
  AND balance >= 100.00;

UPDATE accounts
SET balance = balance + 100.00
WHERE account_id = 202;

COMMIT;

Production logic must verify that the expected rows were updated. If the source account does not exist, has insufficient funds, or another required condition fails, the application should roll back the transaction.

Rollback Example

BEGIN;

UPDATE accounts
SET balance = balance - 100.00
WHERE account_id = 101
  AND balance >= 100.00;

-- Required validation or second update fails.

ROLLBACK;

After rollback, the transaction's uncommitted modifications are discarded.

Atomicity Is Not the Same as Idempotency

An order-creation transaction can be atomic and still create two orders when the complete transaction is executed twice. Retryable operations still need an idempotency strategy.

Consistency

Consistency means that a successful transaction moves the database from one valid state to another valid state while preserving the defined integrity rules.

Consistency rules can include:

• Primary-key uniqueness
• Foreign-key validity
• Required values
• Allowed status values
• Valid numeric ranges
• Correct totals
• Inventory rules
• Account-balance rules
• Valid lifecycle transitions
• Tenant-boundary rules

Constraint-based Consistency

CREATE TABLE accounts
(
    account_id BIGINT PRIMARY KEY,
    balance DECIMAL(18, 2) NOT NULL,

    CONSTRAINT ck_accounts_balance
        CHECK (balance >= 0)
);

The CHECK constraint prevents a committed row from containing a negative balance when that rule accurately represents the domain.

Referential Consistency

CREATE TABLE orders
(
    order_id BIGINT PRIMARY KEY,
    customer_id BIGINT NOT NULL,

    CONSTRAINT fk_orders_customer
        FOREIGN KEY (customer_id)
        REFERENCES customers (customer_id)
);

The foreign key prevents a committed order from referencing a nonexistent customer.

Business Invariant

An order total can be represented by the invariant:

\[ OrderTotal = \sum LineAmount + Tax + Shipping - Discounts \]

The application and database design must ensure that all committed changes preserve the required interpretation of this rule.

ACID Consistency vs Distributed Consistency

ACID consistency and distributed-system consistency are related terms with different emphasis.

Saying that a database is ACID does not by itself fully describe replica visibility, cross-region behaviour, or every distributed guarantee.

Isolation

Isolation controls how concurrent transactions affect one another and which intermediate or committed changes each transaction can observe.

Transaction A
      |
      | reads and writes shared data
      v
Database concurrency control
      ^
      | reads and writes shared data
      |
Transaction B

Without suitable isolation, concurrent transactions can produce outcomes that neither transaction intended.

Last-seat Example

Available seats:

1


Transaction A reads:

1 seat available.


Transaction B reads:

1 seat available.


Transaction A books the seat.

Transaction B also books the seat.


Incorrect result:

Two bookings for one seat.

A correct design uses appropriate locking, atomic conditional updates, uniqueness constraints, or stronger isolation to prevent both bookings from succeeding.

Atomic Conditional Update

UPDATE events
SET available_seats = available_seats - 1
WHERE event_id = :event_id
  AND available_seats > 0;

The application must verify that exactly one row was updated before creating the confirmed booking.

Common Concurrency Anomalies

Dirty-read Example

Transaction A:

Updates balance from 500 to 100.
Has not committed.


Transaction B:

Reads balance as 100.


Transaction A:

Rolls back.


Problem:

Transaction B acted on a value
that never became committed.

Non-repeatable-read Example

Transaction A:

Reads order status as pending.


Transaction B:

Updates status to confirmed.
Commits.


Transaction A:

Reads the same order again.
Now sees confirmed.

Phantom-read Example

Transaction A:

Counts 10 pending orders.


Transaction B:

Inserts another pending order.
Commits.


Transaction A:

Repeats the same query.
Now counts 11 pending orders.

Isolation Levels

Relational database systems commonly provide several isolation levels. Stronger isolation can prevent more anomalies but can increase blocking, retries, or concurrency-control work.

Exact isolation behaviour differs among database engines. Verify the selected platform's documentation and test the actual transaction pattern.

Durability

Durability means that after the database acknowledges a successful commit, the committed transaction survives failures covered by the database's durability configuration and storage architecture.

Transaction changes data
        |
        v
Database records durable recovery information
        |
        v
Database confirms COMMIT
        |
        v
System or process fails
        |
        v
Database recovers committed transaction

Durability is commonly supported through mechanisms such as:

• Transaction logs
• Write-ahead logging
• Durable storage
• Checkpoints
• Recovery processing
• Storage redundancy
• Replication where configured

Write-ahead Logging

With write-ahead logging, recovery information describing a change is written to the transaction log before the modified data page is considered safely persisted.

Change requested
      |
      v
Log record created
      |
      v
Log becomes durable
      |
      v
Commit acknowledged
      |
      v
Data pages can be written later

After a crash, the database can use its log and recovery rules to reapply committed changes and undo incomplete work.

Durability Is Not Backup

ACID Order-creation Example

BEGIN;

INSERT INTO orders
(
    order_id,
    customer_id,
    order_status,
    total_amount
)
VALUES
(
    :order_id,
    :customer_id,
    'pending',
    :total_amount
);

INSERT INTO order_lines
(
    order_id,
    line_number,
    product_id,
    quantity,
    unit_price
)
VALUES
(
    :order_id,
    1,
    :product_id,
    :quantity,
    :unit_price
);

UPDATE inventory
SET available_quantity =
    available_quantity - :quantity
WHERE product_id = :product_id
  AND available_quantity >= :quantity;

-- Verify one inventory row was updated.

COMMIT;

Atomicity

The order, line, and inventory reservation commit together or are rolled back together.

Consistency

Foreign keys, quantity checks, totals, and inventory rules remain valid.

Isolation

Concurrent orders cannot both consume inventory that is available to only one order.

Durability

After commit is acknowledged, the created order remains stored according to the database's durability guarantee.

ACID Payment Example

Payment transaction:

1. Verify payment does not already exist.
2. Insert payment record.
3. Update order payment status.
4. Insert accounting entry.
5. Store idempotency outcome.
6. Commit.

The transaction protects database changes within its transaction boundary. A remote payment provider cannot generally participate in the same ordinary local database transaction. External side effects require additional workflow, reconciliation, and idempotency design.

Local Transactions vs Distributed Workflows

Local database transaction:

Orders table
Payments table
Accounting entries
Idempotency records

All stored in one transactional database.


Distributed workflow:

Order database
Payment provider
Inventory service
Messaging system

Several independent systems.

ACID guarantees from one database do not automatically create one atomic transaction across independent services.

Distributed workflows can require:

• Idempotency keys
• Transactional outbox patterns
• Durable workflow state
• Compensating actions
• Message deduplication
• Retries and reconciliation

Database Commit and Message Publication

1. Commit order to database.
2. Publish OrderCreated message.
3. Application fails before publication.

Result:

Order exists,
but no event was published.

Within one database transaction:

1. Insert order.
2. Insert outbox message.
3. Commit both.


After commit:

A publisher reads the outbox
and delivers the message reliably.

Outbox Table

CREATE TABLE outbox_messages
(
    message_id VARCHAR(100) PRIMARY KEY,
    event_type VARCHAR(100) NOT NULL,
    aggregate_id VARCHAR(100) NOT NULL,
    payload TEXT NOT NULL,
    created_at TIMESTAMP NOT NULL,
    published_at TIMESTAMP NULL
);

Consumers should still process duplicate deliveries idempotently because a publisher can deliver a message and fail before recording successful publication.

PHP Transaction Example

<?php

declare(strict_types=1);

function transferFunds(
    PDO $pdo,
    int $sourceAccountId,
    int $targetAccountId,
    string $amount
): void {
    if (bccomp($amount, '0.00', 2) <= 0) {
        throw new InvalidArgumentException(
            'The transfer amount must be positive.'
        );
    }

    $pdo->beginTransaction();

    try {
        $debit =
            $pdo->prepare(
                '
                UPDATE accounts
                SET balance = balance - :amount
                WHERE account_id = :account_id
                  AND balance >= :amount
                '
            );

        $debit->execute([
            'amount' => $amount,
            'account_id' => $sourceAccountId
        ]);

        if ($debit->rowCount() !== 1) {
            throw new RuntimeException(
                'The source account is unavailable or has insufficient funds.'
            );
        }

        $credit =
            $pdo->prepare(
                '
                UPDATE accounts
                SET balance = balance + :amount
                WHERE account_id = :account_id
                '
            );

        $credit->execute([
            'amount' => $amount,
            'account_id' => $targetAccountId
        ]);

        if ($credit->rowCount() !== 1) {
            throw new RuntimeException(
                'The target account does not exist.'
            );
        }

        $pdo->commit();
    } catch (Throwable $exception) {
        if ($pdo->inTransaction()) {
            $pdo->rollBack();
        }

        throw $exception;
    }
}

This example uses a decimal string rather than a floating-point value for the amount. Production code also needs authorization, idempotency, appropriate isolation, audit records, currency validation, and safe error handling.

Keep Transactions Short

Long-running transactions can retain locks, row versions, connections, log space, and other resources.

Begin transaction

Read order
Call external payment provider
Wait for network response
Generate document
Send email
Update database

Commit

Perform external preparation where safe

Begin transaction

Read required rows
Validate current state
Apply database changes
Insert outbox message

Commit

Process external follow-up asynchronously

Moving work outside a transaction is safe only when the workflow and revalidation rules account for changes that can occur before commit.

Deadlocks

A deadlock can occur when transactions wait for resources held by each other.

Transaction A:

Locks Account 1
Waits for Account 2


Transaction B:

Locks Account 2
Waits for Account 1


Result:

Circular wait

The database can detect the deadlock and abort one transaction. The application should treat the aborted transaction according to its documented retry and idempotency policy.

Deadlock-reduction practices include:

• Access shared resources in a consistent order
• Keep transactions short
• Use suitable indexes
• Avoid unnecessary rows and tables in the transaction
• Implement bounded retries for safe operations

Retrying Transactions

Transaction fails because of
a transient concurrency conflict
        |
        v
Roll back completely
        |
        v
Is the complete business operation
safe to retry?
        |
        +--> No:
        |       return controlled failure
        |
        +--> Yes:
                wait with backoff and jitter
                start a new transaction
                reread current state
                retry within overall deadline

Do not continue using a transaction after it has failed or been rolled back. Begin a new transaction and reread the data required for the decision.

Savepoints

A savepoint establishes a named point inside a transaction to which selected later work can be rolled back where the database supports that behaviour.

BEGIN;

INSERT INTO orders
(
    order_id,
    customer_id
)
VALUES
(
    :order_id,
    :customer_id
);

SAVEPOINT before_optional_note;

INSERT INTO order_notes
(
    order_id,
    note_text
)
VALUES
(
    :order_id,
    :note_text
);

ROLLBACK TO SAVEPOINT before_optional_note;

COMMIT;

Savepoints do not make independent commits. The outer transaction still commits or rolls back as one transaction.

ACID Observability

Useful transaction metrics include:

• Transaction count
• Commit count
• Rollback count
• Transaction duration
• Deadlock count
• Lock-wait duration
• Serialization or concurrency-conflict failures
• Constraint violations
• Transaction-log growth
• Recovery duration
• Connection time spent inside transactions
• Long-running transaction count

Logs should include safe operation and trace identifiers without exposing credentials, sensitive values, or raw internal database details.

ACID Testing Strategy

ACID behaviour should be tested through failures and concurrency rather than only through successful single-user operations.

1. Identify one complete business transaction.
2. Force failure after each database operation.
3. Verify that no partial committed state remains.
4. Attempt constraint-violating values.
5. Run conflicting transactions concurrently.
6. Test the selected isolation level.
7. Force transaction rollback.
8. Test deadlock or serialization-failure handling.
9. Verify safe retry behaviour.
10. Test recovery after process or database interruption in an approved environment.
11. Verify committed and uncommitted outcomes.
12. Confirm that external side effects are reconciled correctly.

Common ACID Mistakes

Recommended Test Cases

ACID Best Practices

Practice Exercise

Implement an ACID course-enrollment transaction for the online learning platform designed in the previous lessons.

Requirements

1. Verify that the learner exists and is active.
2. Verify that the course exists and accepts enrollment.
3. Prevent duplicate enrollment.
4. Check that capacity remains available.
5. Create the enrollment.
6. Reduce available course capacity.
7. Create an enrollment audit record.
8. Store an outbox event in the same transaction.
9. Commit all changes together.
10. Roll back when any required step fails.
11. Prevent two learners from receiving one remaining seat.
12. Handle deadlock or serialization failures safely.
13. Use an idempotency key for retryable enrollment requests.
14. Verify successful recovery after a lost response.
15. Measure transaction duration and rollback count.

Suggested Transaction

BEGIN;

UPDATE courses
SET available_seats = available_seats - 1
WHERE course_id = :course_id
  AND course_status = 'published'
  AND available_seats > 0;

-- Verify exactly one course row was updated.

INSERT INTO enrollments
(
    enrollment_id,
    learner_id,
    course_id,
    enrollment_status,
    completion_percentage,
    enrolled_at
)
VALUES
(
    :enrollment_id,
    :learner_id,
    :course_id,
    'active',
    0,
    CURRENT_TIMESTAMP
);

INSERT INTO enrollment_audit
(
    audit_id,
    enrollment_id,
    action_name,
    occurred_at
)
VALUES
(
    :audit_id,
    :enrollment_id,
    'enrollment_created',
    CURRENT_TIMESTAMP
);

INSERT INTO outbox_messages
(
    message_id,
    event_type,
    aggregate_id,
    payload,
    created_at
)
VALUES
(
    :message_id,
    'EnrollmentCreated',
    :enrollment_id,
    :payload,
    CURRENT_TIMESTAMP
);

COMMIT;

ACID Review Template

Frequently Asked Questions