04 - Database Transactions and ACID

Database Transactions and ACID

Mental model

A database transaction groups related changes into one atomic unit. For a single service, this is usually the simplest and strongest consistency boundary.

ACID

Property	Meaning	E-commerce example
Atomicity	All changes happen or none do	Order + order items
Consistency	Rules/constraints remain valid	Quantity cannot become negative
Isolation	Concurrent transactions don’t incorrectly interfere	Two users shouldn’t buy the last item
Durability	Committed data survives failure	Paid order remains after restart

Small Spring Example

@Transactional
public void createOrder(Order order) {
    orderRepository.save(order);
    orderItemRepository.saveAll(order.items());
}

Spring commits the transaction if the method completes successfully and rolls it back when an appropriate exception causes rollback.

Small .NET Example

await using var tx =
    await db.Database.BeginTransactionAsync();
 
db.Orders.Add(order);
db.OrderItems.AddRange(items);
 
await db.SaveChangesAsync();
await tx.CommitAsync();

Why Not One Transaction Across Services?

Imagine:

Order Service
     │
     ├── PostgreSQL transaction
     │
     └── Payment Service
             │
             └── different database

A normal local DB transaction cannot atomically control both databases.

This leads to distributed transaction approaches such as 2PC, or application-level workflows such as Saga.

Important

A Saga does not provide ACID across multiple services. It coordinates a sequence of local transactions and compensating actions.

Concurrency Problem

Two requests:

Stock = 1
 
Request A: read 1
Request B: read 1
 
A: buy item
B: buy item

Both may believe inventory is available.

Possible solutions include:

• row-level locking
• optimistic concurrency
• atomic SQL updates
• appropriate isolation levels

A particularly useful approach is an atomic update:

UPDATE inventory
SET quantity = quantity - 1
WHERE product_id = ?
  AND quantity > 0;

Then check affected rows.

Interview shortcut

Don’t just say “use transactions.” Explain what the transaction boundary is, what can race, and which isolation/concurrency mechanism protects it.

Docker PostgreSQL

Yes — PostgreSQL should be part of our Docker Compose development stack.

services:
  postgres:
    image: postgres:17
    environment:
      POSTGRES_DB: ecommerce
      POSTGRES_USER: ecommerce
      POSTGRES_PASSWORD: ecommerce
    ports:
      - "5432:5432"

Start it with:

docker compose up -d postgres

Interview Questions

When is a local transaction enough?

When all changes that must be atomic belong to the same transactional resource, such as one PostgreSQL database.

What happens if the application crashes after COMMIT?

The committed transaction is durable. PostgreSQL’s recovery mechanisms ensure the committed state can be recovered.

What if DB commit succeeds but Kafka publish fails?

The database and event stream are now inconsistent. This is the problem Transactional Outbox solves.