Full Stack • Java • System Design • Cloud • AI Engineering

Transactions and ACID in System Design

Learn database transactions and ACID properties with Spring Boot, PostgreSQL, MySQL, Oracle, and distributed systems. Understand Atomicity, Consistency, Isolation, Durability, transaction lifecycle, commit, rollback, isolation levels, and real-world banking examples.

Introduction

Imagine you're transferring $10,000 from Account A to Account B.

The system performs two operations:

  1. Debit Account A
  2. Credit Account B

What happens if the server crashes after debiting Account A but before crediting Account B?

Without transactions:

  • Customer loses money
  • Database becomes inconsistent
  • Banking system cannot be trusted

This is why every enterprise application relies on Database Transactions.

Transactions ensure a group of operations executes completely or not at all.

Learning Objectives

In this article you'll learn:

  • What is a Transaction?
  • Why Transactions are Required
  • Transaction Lifecycle
  • ACID Properties
  • Commit
  • Rollback
  • Isolation Levels
  • Spring Boot Transactions
  • Banking Examples
  • Best Practices

What is a Transaction?

A transaction is a single logical unit of work.

Example

Transfer Money

↓

Debit Account

↓

Credit Account

↓

Commit

If any step fails

Rollback

↓

Database Restored

Real World Example

Imagine transferring money.

Account A = $10,000

↓

Transfer $2,000

↓

Account B

The database must ensure

  • Debit succeeds
  • Credit succeeds

Otherwise

Everything is rolled back.

Transaction Flow

flowchart TD
    A[Client]
    B[Spring Boot Service]
    C[Begin Transaction]
    D[Execute SQL]
    E{Success?}
    F[Commit]
    G[Rollback]
    H[(Database)]

    A --> B
    B --> C
    C --> D
    D --> E
    E -->|Yes| F
    E -->|No| G
    F --> H
    G --> H

Transaction Lifecycle

flowchart LR
    A[Begin]
    B[Execute SQL]
    C[Commit]
    D[Rollback]

    A --> B
    B --> C
    B --> D

Spring Boot Transaction

sequenceDiagram
    participant Client
    participant Controller
    participant Service
    participant Database

    Client->>Controller: Transfer Money
    Controller->>Service: transfer()
    Service->>Database: BEGIN
    Service->>Database: Debit Account
    Service->>Database: Credit Account
    Service->>Database: COMMIT
    Service-->>Controller: Success
    Controller-->>Client: Transaction Completed

ACID Properties

Every enterprise relational database guarantees ACID.

A

Atomicity

C

Consistency

I

Isolation

D

Durability

Atomicity

Atomicity means

Either everything happens or nothing happens.

Money Transfer

flowchart TD
    A[Begin Transaction]
    B[Debit Account]
    C[Credit Account]
    D[Commit]
    E[Rollback]

    A --> B
    B --> C
    C --> D

    C -. Failure .-> E

If Credit fails

Debit is automatically reversed.

Banking Example

Before Transfer

Account Balance
A $10,000
B $5,000

Transfer

$2,000

If transaction succeeds

Account Balance
A $8,000
B $7,000

If transaction fails

Account Balance
A $10,000
B $5,000

Consistency

Consistency ensures the database always moves from one valid state to another.

Example

Before Transfer

Total Money

$15,000

After Transfer

Still

$15,000

Money is never created or destroyed.

Consistency Diagram

flowchart LR
    A[Valid State]
    B[Transaction]
    C[Valid State]

    A --> B
    B --> C

Isolation

Suppose

Two users transfer money simultaneously.

Without Isolation

User 1

↓

Updates Balance

User 2

↓

Reads Half Updated Data

Wrong result.

Isolation prevents transactions from interfering.

Isolation Flow

sequenceDiagram
    participant T1 as Transaction 1
    participant DB as Database
    participant T2 as Transaction 2

    T1->>DB: Update Balance
    Note over DB: Lock Row
    T2->>DB: Read Balance
    DB-->>T2: Wait
    T1->>DB: Commit
    DB-->>T2: Read Updated Balance

Durability

After COMMIT,

the transaction is permanently stored.

Even if

  • Server crashes
  • Power fails
  • Application restarts

the committed transaction remains.

Durability Flow

flowchart TD
    A[Commit]
    B[Transaction Log]
    C[Disk Storage]
    D[Recovery]

    A --> B
    B --> C
    C --> D

ACID Summary

Property Meaning
Atomicity All or Nothing
Consistency Valid Data Always
Isolation Concurrent Transactions Don't Interfere
Durability Data Survives Crashes

Commit

Commit permanently saves changes.

COMMIT;

Flow

flowchart LR
    A[Execute SQL]
    B[Commit]
    C[(Database Updated)]

    A --> B
    B --> C

Rollback

Rollback cancels changes.

ROLLBACK;

flowchart LR
    A[Execute SQL]
    B[Failure]
    C[Rollback]
    D[(Original Data)]

    A --> B
    B --> C
    C --> D

Spring Boot Example

@Transactional
public void transfer(Long from, Long to, BigDecimal amount) {

    accountRepository.debit(from, amount);

    accountRepository.credit(to, amount);

}

If credit() throws an exception,

Spring automatically performs a rollback.

Spring Transaction Architecture

flowchart TD
    U[Client]
    C[REST Controller]
    S[Service Layer]
    TM[Transaction Manager]
    R[Repository]
    DB[(PostgreSQL)]

    U --> C
    C --> S
    S --> TM
    TM --> R
    R --> DB

Real-Time Banking Architecture

flowchart TD
    Customer[Customer]

    API[Transfer API]

    Service[Transaction Service]

    DB[(Core Banking Database)]

    Customer --> API
    API --> Service
    Service --> DB

Every transfer runs inside a transaction.

Common Use Cases

Transactions should always be used for:

  • Money Transfer
  • Order Placement
  • Inventory Update
  • Ticket Booking
  • Loan Approval
  • Insurance Claim
  • Payroll Processing
  • Wallet Recharge

Best Practices

  • Keep transactions as short as possible.
  • Avoid external API calls inside transactions.
  • Update only required rows.
  • Handle exceptions correctly.
  • Monitor long-running transactions.
  • Use optimistic locking where appropriate.
  • Avoid unnecessary table locks.

Common Mistakes

❌ Long-running transactions

❌ Calling REST APIs inside a transaction

❌ Holding locks for too long

❌ Ignoring rollback behavior

❌ Updating unrelated tables in one transaction

Transaction Isolation Levels

Modern databases allow multiple users to access data simultaneously.

Without proper isolation, concurrent transactions can corrupt data.

Isolation Levels control how one transaction can see changes made by another transaction.

Why Isolation Levels?

Imagine two ATMs accessing the same account.

Current Balance

$10,000

ATM-1 withdraws

$2,000

ATM-2 withdraws

$9,000

If both read the same balance simultaneously, the account may become negative.

Isolation levels prevent these problems.

Isolation Level Hierarchy

flowchart TD
    A[Read Uncommitted]
    B[Read Committed]
    C[Repeatable Read]
    D[Serializable]

    A --> B
    B --> C
    C --> D

Higher isolation provides better consistency but lower concurrency.

Isolation Comparison

Isolation Level Dirty Read Non-Repeatable Read Phantom Read
Read Uncommitted ❌ Possible ❌ Possible ❌ Possible
Read Committed ✅ Prevented ❌ Possible ❌ Possible
Repeatable Read ✅ Prevented ✅ Prevented ❌ Possible
Serializable ✅ Prevented ✅ Prevented ✅ Prevented

Read Uncommitted

Lowest isolation level.

Transactions can read uncommitted data.

sequenceDiagram
    participant T1
    participant DB
    participant T2

    T1->>DB: Update Balance = 8000
    Note over DB: Not Committed
    T2->>DB: Read Balance
    DB-->>T2: 8000
    T1->>DB: Rollback

Problem

T2 read data that never actually existed.

Dirty Read

Definition

A transaction reads another transaction's uncommitted data.

Example

Account Balance

$10,000

Transaction A

Updates Balance

↓

$8,000

Before Commit

Transaction B reads

$8,000

Then Transaction A rolls back.

Actual balance remains

$10,000

Transaction B has read incorrect information.

Read Committed

Most enterprise databases use Read Committed by default.

A transaction can only read committed data.

sequenceDiagram
    participant T1
    participant DB
    participant T2

    T1->>DB: Update Balance
    T1->>DB: Commit
    T2->>DB: Read Balance
    DB-->>T2: Updated Balance

Dirty Reads are eliminated.

Non-Repeatable Read

A transaction reads the same row twice.

Another transaction updates it between reads.

sequenceDiagram
    participant T1
    participant DB
    participant T2

    T1->>DB: Read Price = 100

    T2->>DB: Update Price = 120
    T2->>DB: Commit

    T1->>DB: Read Price
    DB-->>T1: 120

Same query returned different results.

Repeatable Read

Database guarantees that rows already read cannot change during the transaction.

flowchart TD
    A[Transaction Starts]
    B[Read Product]
    C[Another Transaction Updates]
    D[Original Transaction Reads Again]

    A --> B
    B --> C
    C --> D

The original transaction still sees the old value.

Phantom Read

Rows appear or disappear between two queries.

Example

First Query

SELECT * FROM employees
WHERE department='IT';

Result

100 Rows

Another transaction inserts

New Employee

Second Query

101 Rows

Unexpected extra row.

Phantom Read Diagram

sequenceDiagram
    participant T1
    participant DB
    participant T2

    T1->>DB: SELECT Employees

    T2->>DB: INSERT Employee
    T2->>DB: Commit

    T1->>DB: SELECT Employees

Serializable Isolation

Highest isolation level.

Transactions execute as if they run one after another.

flowchart LR
    T1[Transaction 1]

    T2[Transaction 2]

    T3[Transaction 3]

    T1 --> T2
    T2 --> T3

Advantages

  • Maximum consistency

Disadvantages

  • Lower throughput
  • Higher locking
  • Reduced concurrency

Optimistic Locking

Assumes conflicts are rare.

Each row contains a version number.

Version = 5

Update

Version = 6

If another transaction already updated the row,

the update fails.

Optimistic Locking Diagram

flowchart TD
    A[Read Version 5]

    B[Modify Data]

    C{Version Still 5?}

    D[Update]

    E[Reject]

    A --> B
    B --> C
    C -->|Yes| D
    C -->|No| E

Spring Example

@Version
private Long version;

Hibernate automatically detects concurrent modifications.

Pessimistic Locking

Assumes conflicts are likely.

Locks rows immediately.

flowchart TD
    A[Transaction Starts]

    B[Lock Row]

    C[Update]

    D[Commit]

    A --> B
    B --> C
    C --> D

Other transactions must wait.

Optimistic vs Pessimistic

Optimistic Pessimistic
No Locks Uses Database Locks
Better Performance Better Consistency
Retry on Conflict Wait for Lock
High Read Systems Banking Systems

Spring Transaction Propagation

Propagation determines how nested transactions behave.

Common options

Propagation Description
REQUIRED Join existing transaction
REQUIRES_NEW Create new transaction
SUPPORTS Join if available
NOT_SUPPORTED Execute without transaction
MANDATORY Must have transaction
NEVER Fail if transaction exists
NESTED Nested savepoint transaction

REQUIRED

flowchart LR
    Client --> ServiceA
    ServiceA --> ServiceB

Service B joins Service A's transaction.

Most commonly used.

REQUIRES_NEW

flowchart TD
    A[Transaction A]

    B[Pause]

    C[New Transaction]

    D[Resume A]

    A --> B
    B --> C
    C --> D

Useful for

  • Audit Logging
  • Notifications

Nested Transactions

flowchart TD
    A[Main Transaction]

    B[Nested Transaction]

    C[Commit]

    D[Rollback Nested]

    A --> B
    B --> C
    B --> D

Nested rollback doesn't necessarily rollback the outer transaction.

Distributed Transactions

Modern applications have multiple microservices.

flowchart TD
    CLIENT[Client]

    ORDER[Order Service]

    PAYMENT[Payment Service]
    INVENTORY[Inventory Service]
    SHIPPING[Shipping Service]

    CLIENT --> ORDER

    ORDER --> PAYMENT
    ORDER --> INVENTORY
    ORDER --> SHIPPING

One database transaction cannot span all services.

Two-Phase Commit (2PC)

Phase 1

Prepare

Phase 2

Commit

2PC Diagram

sequenceDiagram
    participant Coordinator
    participant Payment
    participant Inventory

    Coordinator->>Payment: Prepare
    Coordinator->>Inventory: Prepare

    Payment-->>Coordinator: Ready
    Inventory-->>Coordinator: Ready

    Coordinator->>Payment: Commit
    Coordinator->>Inventory: Commit

Problems

  • Blocking
  • Coordinator failure
  • Slow
  • Not cloud friendly

Saga Pattern

Instead of one global transaction,

every service performs its own transaction.

If one fails,

compensation actions are executed.

Saga Architecture

flowchart LR
    Order --> Payment
    Payment --> Inventory
    Inventory --> Shipping

If Shipping fails

flowchart LR
    Shipping --> Refund
    Refund --> InventoryRollback
    InventoryRollback --> CancelOrder

Choreography Saga

flowchart LR
    Order --> Kafka
    Kafka --> Payment
    Payment --> Kafka
    Kafka --> Inventory
    Inventory --> Kafka
    Kafka --> Shipping

Services communicate through events.

Orchestration Saga

flowchart TD
    CLIENT["Client"]
    ORCH["Saga Orchestrator"]

    ORDER["Order Service"]
    PAYMENT["Payment Service"]
    INVENTORY["Inventory Service"]
    SHIPPING["Shipping Service"]

    CLIENT --> ORCH

    ORCH --> ORDER
    ORDER --> PAYMENT
    PAYMENT --> INVENTORY
    INVENTORY --> SHIPPING

    SHIPPING --> ORCH

A central coordinator controls the workflow.

Amazon Example

Order Placement

Order

↓

Payment

↓

Inventory

↓

Shipping

If inventory fails,

payment is refunded automatically.

Netflix Example

Watching a movie

  • Profile Service
  • Recommendation Service
  • Streaming Service
  • Analytics Service

Failures are isolated.

No global transaction.

Banking Example

Money Transfer

flowchart TD
    Debit

    Credit

    Ledger

    Notification

    Debit --> Credit
    Credit --> Ledger
    Ledger --> Notification

All database operations execute inside one ACID transaction.

Best Practices

  • Keep transactions short.
  • Avoid remote API calls inside database transactions.
  • Prefer optimistic locking for high-read systems.
  • Use pessimistic locking only when necessary.
  • Use Saga Pattern for microservices.
  • Avoid distributed transactions whenever possible.
  • Design services for eventual consistency.

Common Interview Questions

What is a Dirty Read?

Reading uncommitted data from another transaction.

What is a Phantom Read?

A query returns additional or missing rows because another transaction inserted or deleted records.

Difference between Optimistic and Pessimistic Locking?

Optimistic locking detects conflicts using version numbers without locking rows, while pessimistic locking acquires database locks before updates.

Why is 2PC rarely used in Microservices?

Because it is slow, blocks participants during failures, introduces a single coordinator dependency, and does not scale well in cloud-native environments.

Why is Saga preferred?

Saga provides eventual consistency through local transactions and compensating actions, making it better suited for distributed microservice architectures.

Summary

In this part, we covered:

  • Transaction Isolation Levels
  • Dirty Read
  • Non-Repeatable Read
  • Phantom Read
  • Optimistic Locking
  • Pessimistic Locking
  • Spring Transaction Propagation
  • Nested Transactions
  • Distributed Transactions
  • Two-Phase Commit (2PC)
  • Saga Pattern
  • Microservices transaction design
  • Amazon, Netflix, and Banking architectures

Understanding these concepts is essential for designing reliable, scalable, and fault-tolerant enterprise systems. Modern monolithic applications typically rely on ACID transactions, while microservices often favor Saga-based eventual consistency to achieve scalability and resilience.

Loading likes...

Comments

Share a question, correction, or practical insight about this article.

Loading approved comments...