Introduction

Many business operations require several related database changes. Creating an order can involve inserting the order header, creating order lines, reserving inventory, recording an audit entry, and adding an event to an outbox table.

If each statement commits independently, a failure can leave the database with only part of the intended operation.

A transaction groups related SQL statements into one logical unit of work. The application commits the transaction when every required step succeeds or rolls it back when a required step fails.

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

Prerequisites

What Is a Database Transaction?

A database transaction is a sequence of related operations treated as one logical unit of work.

Start transaction
      |
      v
Read required state
      |
      v
Validate business conditions
      |
      v
Apply related changes
      |
      v
Verify affected rows and constraints
      |
      +-- Success --> COMMIT
      |
      +-- Failure --> ROLLBACK

A transaction can contain reads and writes. Its exact visibility, locking, and conflict behaviour depends on the database engine and selected isolation level.

Transaction Boundary

A transaction boundary identifies which operations must succeed or fail together.

Order-creation Boundary

One logical operation:

Create an order


Required database changes:

- Insert order
- Insert order lines
- Reserve inventory
- Store calculated total
- Insert audit record
- Insert outbox event


Required outcome:

All changes commit,
or all changes roll back.

Transaction 1:
Insert order and commit.

Transaction 2:
Insert order lines and commit.

Transaction 3:
Reserve inventory and commit.


Failure:

Order and lines exist,
but inventory was not reserved.

One transaction:

Insert order
Insert order lines
Reserve inventory
Insert audit record
Insert outbox event

Commit only after all required work succeeds.

Starting a Transaction

Transaction-start syntax varies between database systems. Common forms include BEGIN, BEGIN TRANSACTION, and START TRANSACTION.

BEGIN;

INSERT INTO departments
(
    department_id,
    department_name
)
VALUES
(
    10,
    'Engineering'
);

COMMIT;

Use the syntax and transaction semantics documented for the selected database and application driver.

COMMIT

COMMIT successfully ends the current transaction and makes its changes final according to the database's durability and visibility guarantees.

BEGIN;

UPDATE orders
SET order_status = 'confirmed'
WHERE order_id = :order_id
  AND order_status = 'pending';

COMMIT;

The application should not commit merely because SQL execution produced no exception. The application must verify affected-row counts and required business conditions first.

ROLLBACK

ROLLBACK ends the transaction and discards its uncommitted changes.

BEGIN;

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

-- Application discovers that no row was updated.

ROLLBACK;

A rollback cannot normally undo changes that were already committed. Recovery after an incorrect commit requires another corrective transaction, a compensating operation, or restoration from an appropriate recovery source.

COMMIT vs ROLLBACK

Autocommit

Many tools and database drivers use autocommit by default. In autocommit mode, each statement can execute as its own transaction unless an explicit transaction is started.

Autocommit enabled:

UPDATE statement
      |
      v
Statement succeeds
      |
      v
Statement commits automatically

Autocommit is convenient for independent statements but unsafe when several changes need one shared atomic boundary.

Autocommit Risk

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

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

If each statement commits separately and the second statement fails, the transfer remains incomplete.

Funds-transfer Transaction

BEGIN;

UPDATE accounts
SET balance = balance - :amount
WHERE account_id = :source_account_id
  AND currency = :currency
  AND balance >= :amount;

-- Verify exactly one source row was updated.

UPDATE accounts
SET balance = balance + :amount
WHERE account_id = :target_account_id
  AND currency = :currency;

-- Verify exactly one target row was updated.

INSERT INTO account_transfers
(
    transfer_id,
    source_account_id,
    target_account_id,
    amount,
    currency,
    transfer_status,
    created_at
)
VALUES
(
    :transfer_id,
    :source_account_id,
    :target_account_id,
    :amount,
    :currency,
    'completed',
    CURRENT_TIMESTAMP
);

COMMIT;

If either account update fails, the transaction should be rolled back. The transfer operation also needs authentication, authorization, auditability, and idempotency.

Transaction States

Savepoints

A savepoint creates a named position inside a transaction. Where supported, the application can roll back work performed after the savepoint without abandoning all earlier work.

BEGIN;

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

SAVEPOINT before_optional_note;

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

ROLLBACK TO SAVEPOINT before_optional_note;

COMMIT;

The order remains part of the open transaction, while the note insertion is undone.

Releasing a Savepoint

Some databases support releasing a savepoint when it is no longer needed.

BEGIN;

UPDATE orders
SET order_status = 'confirmed'
WHERE order_id = :order_id;

SAVEPOINT after_confirmation;

INSERT INTO order_audit
(
    order_id,
    action_name
)
VALUES
(
    :order_id,
    'confirmed'
);

RELEASE SAVEPOINT after_confirmation;

COMMIT;

Savepoint commands and behaviour vary between database platforms.

Nested Transactions

Not every database supports true independently committed nested transactions. Frameworks can simulate nesting through reference counting or savepoints.

Outer service starts transaction
        |
        v
Inner service requests transaction
        |
        +--> Framework reuses outer transaction
        |
        +--> Framework creates savepoint
        |
        +--> Platform provides another documented behaviour

An inner method returning successfully does not guarantee that its work will remain committed if the outer transaction later rolls back.

Read-only Transactions

A transaction can contain only reads. An explicit read transaction can be useful when several queries need a consistent view according to the database's isolation model.

BEGIN;

SELECT
    order_id,
    order_status,
    total_amount
FROM orders
WHERE customer_id = :customer_id;

SELECT
    payment_id,
    payment_status,
    payment_amount
FROM payments
WHERE customer_id = :customer_id;

COMMIT;

Whether both queries observe one consistent snapshot depends on the database engine, transaction mode, and isolation level.

Conditional Updates

Conditional updates combine checking and changing data in one database statement.

UPDATE orders
SET order_status = 'cancelled'
WHERE order_id = :order_id
  AND order_status IN
  (
      'pending',
      'confirmed'
  );

The application must verify the affected-row count. No updated row can mean that the order does not exist, is not authorized, or is no longer in a cancellable state.

Inventory Reservation

BEGIN;

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

-- Verify exactly one row was updated.

INSERT INTO inventory_reservations
(
    reservation_id,
    product_id,
    order_id,
    reserved_quantity,
    reservation_status
)
VALUES
(
    :reservation_id,
    :product_id,
    :order_id,
    :quantity,
    'active'
);

COMMIT;

Combining the availability condition with the update prevents a separate read-before-write race from overselling the available quantity.

Explicit Row Locking

Some workflows read rows with update-intent locks before changing them.

BEGIN;

SELECT
    available_quantity
FROM inventory
WHERE product_id = :product_id
FOR UPDATE;

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

COMMIT;

Locking syntax and behaviour vary between database engines. Locks should be held only for the shortest practical transaction duration.

Optimistic Concurrency

Optimistic concurrency assumes conflicts are uncommon and verifies that the resource has not changed before committing an update.

UPDATE customers
SET
    display_name = :display_name,
    row_version = row_version + 1
WHERE customer_id = :customer_id
  AND row_version = :expected_version;

If no row is updated, another transaction may have changed the customer after the application read it.

Read customer:

version = 7


Submit update:

WHERE version = 7


Database currently contains:

version = 8


Result:

No row updated.
Return or resolve concurrency conflict.

Pessimistic vs Optimistic Concurrency

Keep Transactions Short

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

Begin transaction

Read order
Wait for user input
Call payment provider
Generate PDF
Send email
Update database

Commit

Perform safe preparation outside transaction

Begin transaction

Reread required current state
Validate conditions
Apply database changes
Insert outbox event

Commit

Perform asynchronous follow-up

Any condition checked before the transaction can become stale. Reread or revalidate critical state inside the transaction before committing.

External Calls inside Transactions

A database transaction should not normally remain open while waiting for a remote service.

Database transaction opened
        |
        v
Remote payment call begins
        |
        v
Network delay or timeout
        |
        v
Database connection and locks remain held
        |
        v
Contention and timeout risk increase

A remote system also cannot normally be rolled back merely because the local database transaction rolls back.

Transactional Outbox

The transactional outbox pattern stores a business change and a message record in the same local database transaction.

BEGIN;

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

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

COMMIT;

A separate publisher sends committed outbox messages. The publisher and message consumers must handle repeated delivery safely.

Transactions and Idempotency

A transaction prevents partial changes during one execution. An idempotency key prevents a retry from creating a second successful execution of the same logical operation.

BEGIN;

INSERT INTO idempotency_records
(
    tenant_id,
    idempotency_key,
    operation_name,
    processing_status
)
VALUES
(
    :tenant_id,
    :idempotency_key,
    'CreateOrder',
    'IN_PROGRESS'
);

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

UPDATE idempotency_records
SET
    processing_status = 'COMPLETED',
    resource_id = :order_id
WHERE tenant_id = :tenant_id
  AND idempotency_key = :idempotency_key;

COMMIT;

A unique constraint on the scoped idempotency key must provide atomic ownership when concurrent retries arrive.

Deadlocks

A deadlock occurs when transactions form a circular waiting dependency.

Transaction A:

Locks Account 1.
Waits for Account 2.


Transaction B:

Locks Account 2.
Waits for Account 1.


Result:

Circular wait.

The database can select one transaction as the deadlock victim and roll it back.

Deadlock-reduction Practices

• Access shared rows and tables in a consistent order
• Keep transactions short
• Use selective predicates and suitable indexes
• Avoid user input and remote calls inside transactions
• Update only required rows
• Implement bounded retries for safe operations

Transaction Retries

Deadlocks, serialization conflicts, and selected transient failures can require retrying the complete transaction.

Transaction fails
        |
        v
Roll back completely
        |
        v
Classify the failure
        |
        +--> Permanent:
        |       return controlled failure
        |
        +--> Transient:
                verify retry safety
                apply backoff and jitter
                start a new transaction
                reread current data
                retry within deadline

Do not retry from the middle of a failed transaction. Start a new transaction and repeat the complete safe unit of work.

PHP Transaction Example

<?php

declare(strict_types=1);

function enrollLearner(
    PDO $pdo,
    int $enrollmentId,
    int $learnerId,
    int $courseId
): void {
    $pdo->beginTransaction();

    try {
        $reserveSeat =
            $pdo->prepare(
                '
                UPDATE courses
                SET available_seats =
                    available_seats - 1
                WHERE course_id = :course_id
                  AND course_status = :status
                  AND available_seats > 0
                '
            );

        $reserveSeat->execute([
            'course_id' => $courseId,
            'status' => 'published'
        ]);

        if ($reserveSeat->rowCount() !== 1) {
            throw new RuntimeException(
                'The course is unavailable or full.'
            );
        }

        $insertEnrollment =
            $pdo->prepare(
                '
                INSERT INTO enrollments
                (
                    enrollment_id,
                    learner_id,
                    course_id,
                    enrollment_status,
                    completion_percentage,
                    enrolled_at
                )
                VALUES
                (
                    :enrollment_id,
                    :learner_id,
                    :course_id,
                    :status,
                    0,
                    CURRENT_TIMESTAMP
                )
                '
            );

        $insertEnrollment->execute([
            'enrollment_id' => $enrollmentId,
            'learner_id' => $learnerId,
            'course_id' => $courseId,
            'status' => 'active'
        ]);

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

        throw $exception;
    }
}

A production implementation also needs authentication, authorization, idempotency, duplicate-enrollment handling, safe error mapping, an appropriate isolation level, and a bounded retry policy.

Transaction Helper Pattern

<?php

declare(strict_types=1);

function executeInTransaction(
    PDO $pdo,
    callable $operation
): mixed {
    $pdo->beginTransaction();

    try {
        $result =
            $operation(
                $pdo
            );

        $pdo->commit();

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

        throw $exception;
    }
}

The helper centralizes basic commit and rollback handling. It does not automatically provide retry safety, idempotency, isolation selection, or correct business boundaries.

Transactions in Service Layers

Controller

Validates request format
        |
        v
Application service

Defines business transaction boundary
        |
        v
Repositories

Execute SQL using the same transaction
        |
        v
Database

Commits or rolls back the unit of work

Repository methods participating in one business operation should use the same database connection and transaction context. Independently opening new connections can unintentionally place statements in separate transactions.

Connections and Transaction Scope

A database transaction belongs to a database session or connection. Every statement intended to participate in the transaction must execute through the correct connection.

Connection A:
Begin transaction and insert order.

Connection B:
Insert order lines and commit.

Connection A:
Roll back.

Result:

Order lines can remain without
the intended order transaction.

Connection A:
Begin transaction.
Insert order.
Insert order lines.
Reserve inventory.
Commit.

Testing Transactions

Transaction testing should include failure injection and concurrency rather than only successful execution.

1. Run the successful business operation.
2. Force a failure after each required statement.
3. Verify that no partial committed state remains.
4. Trigger primary, foreign, unique, and check violations.
5. Verify affected-row handling.
6. Run competing operations concurrently.
7. Trigger a deadlock or concurrency conflict in an approved environment.
8. Verify rollback before retry.
9. Verify that retries reread current state.
10. Verify idempotency after a lost response.
11. Test outbox publication recovery.
12. Measure transaction duration and lock waits.

Transaction Observability

Useful transaction metrics include:

• Transaction count
• Commit count
• Rollback count
• Transaction duration
• Deadlock count
• Lock-wait duration
• Concurrency-conflict count
• Retry count
• Retry success rate
• Long-running transaction count
• Connection time spent in a transaction
• Constraint violations by operation

Logs should identify the business operation and trace ID without exposing credentials, sensitive values, or raw database details.

Common Transaction Mistakes

Recommended Test Cases

Transaction Best Practices

Practice Exercise

Implement a transaction that records a quiz submission in your online learning platform.

Requirements

1. Verify that the learner is enrolled in the course.
2. Verify that the quiz is active.
3. Prevent duplicate processing of one submission request.
4. Create the quiz-attempt record.
5. Insert the submitted answers.
6. Calculate and store the score.
7. Update the learner's course progress when applicable.
8. Create an audit record.
9. Create an outbox event.
10. Commit all database changes together.
11. Roll back after any required failure.
12. Use one connection for all repository operations.
13. Test two concurrent submissions.
14. Test a lost response after commit.
15. Measure transaction duration and rollback count.

Suggested Transaction

BEGIN;

INSERT INTO quiz_attempts
(
    attempt_id,
    learner_id,
    quiz_id,
    attempt_status,
    started_at
)
VALUES
(
    :attempt_id,
    :learner_id,
    :quiz_id,
    'submitted',
    :started_at
);

INSERT INTO quiz_attempt_answers
(
    attempt_id,
    question_id,
    selected_answer_id,
    awarded_score
)
VALUES
(
    :attempt_id,
    :question_id,
    :selected_answer_id,
    :awarded_score
);

UPDATE quiz_attempts
SET
    total_score = :total_score,
    submitted_at = CURRENT_TIMESTAMP
WHERE attempt_id = :attempt_id;

INSERT INTO learning_audit
(
    audit_id,
    learner_id,
    action_name,
    entity_id,
    occurred_at
)
VALUES
(
    :audit_id,
    :learner_id,
    'quiz_submitted',
    :attempt_id,
    CURRENT_TIMESTAMP
);

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

COMMIT;

Transaction Review Template

Frequently Asked Questions