Implementing Business Workflows

Checking and Consuming Inventory Safely

ReadingPreview

আপনি একটি free preview lesson দেখছেন।

আমাদের CreateOrderUseCase এখন business workflow হিসেবে পরিষ্কার:

CustomerId
    ↓
requested Products
    ↓
validate Products
    ↓
check Inventory
    ↓
capture priceCents
    ↓
consume Inventory
    ↓
persist Order

কিন্তু আগের lesson-এর straightforward Inventory implementation:

load Inventory
    ↓
check availableQuantity
    ↓
decrease in memory
    ↓
save

production concurrency-এর জন্য যথেষ্ট নয়।

এই lesson-এ আমরা সেই সমস্যা solve করব।

Scenario:

availableQuantity = 1

একই সময়ে দুই Customer request পাঠাল:

Customer A
→ quantity = 1

Customer B
→ quantity = 1

Correct result:

one Order succeeds

one Order fails with
INSUFFICIENT_INVENTORY

Never:

both Orders succeed

কারণ physical stock মাত্র এক unit।

এই lesson-এর goal:

Inventory availability check এবং Inventory consumption-কে একটি concurrency-safe persistence operation-এ পরিণত করা, যাতে “check” এবং “decrease” আলাদা raceable steps না থাকে।

The Problem With Read → Check → Write

Suppose Inventory:

10 units

Request A wants:

Request B wants:

Timeline:

A reads 10

B reads 10

A checks 10 >= 7
→ true

B checks 10 >= 6
→ true

Both requests believe enough stock exists.

Then:

A calculates
10 - 7 = 3

B calculates
10 - 6 = 4

Depending on persistence timing, we can end with incorrect state while both Orders appear successful.

The fundamental problem is:

check

and:

update

are separate operations.

Between them, another transaction can modify the same Inventory.

Database Constraint Alone Does Not Solve It

Our schema already has:

CHECK (
    available_quantity >= 0
)

That protects persisted state from becoming:

-1

But consider both transactions computing independently from:

availableQuantity = 1

and both writing:

The final database value:

satisfies the constraint.

Yet:

2 Orders consumed
1 physical unit

The database value looks valid while the business history is wrong.

So:

A non-negative CHECK constraint reinforces Inventory integrity, but it does not by itself prevent overselling caused by concurrent read-check-write workflows.

The Real Operation

Our business operation is not:

read Inventory

followed by:

decrease Inventory

The real operation is:

Consume N units only if at least N units are currently available.

That is one semantic operation:

consumeIfAvailable(
    productId,
    quantity
)

Once we express the requirement this way, a better persistence design becomes obvious.

Candidate Strategies

There are several legitimate approaches.

We will compare:

1. naive read-check-write

2. pessimistic row locking

3. optimistic locking

4. serializable transaction isolation

5. conditional atomic UPDATE

Then choose one for our application.

Option 1 — Naive Read-Check-Write

Conceptually:

Inventory inventory =
        repository.findByProductId(
                productId
        );

if (
        inventory.availableQuantity()
                < quantity
) {
    throw new InsufficientInventoryException();
}

inventory.decrease(
        quantity
);

repository.save(
        inventory
);

This is easy to understand.

It is also the implementation we have been using to explain the domain model.

But for concurrent Order creation:

not sufficient

because the read and write are separate database actions.

We reject this as our final persistence strategy.

Option 2 — Pessimistic Locking

Another approach:

SELECT *
FROM inventory
WHERE product_id = ?
FOR UPDATE;

Then:

check quantity

decrease quantity

UPDATE row

SELECT ... FOR UPDATE locks the selected row against conflicting concurrent updates until the transaction releases the lock. PostgreSQL supports row locking for exactly these kinds of situations.

Conceptually:

Transaction A
→ locks Inventory Product X

Transaction B
→ tries to lock Product X
→ waits

Then A completes.

B receives the latest usable row state and decides based on that state.

Pessimistic Locking Can Work

For our problem, this can produce correct behaviour.

Example:

Inventory = 1

A locks row:

A sees 1
A consumes 1

B waits.

A commits:

Inventory = 0

B continues and finds:

Then:

B
→ INSUFFICIENT_INVENTORY

Correct.

Why We Are Not Choosing Pessimistic Locking

It is valid, but our requirement is extremely specific:

decrease quantity
only when enough quantity exists

Pessimistic locking would require roughly:

SELECT ... FOR UPDATE
    ↓
application comparison
    ↓
application mutation
    ↓
UPDATE

The database can express the entire condition and mutation directly in one statement.

So explicit pre-locking would add an extra round trip and more persistence mechanics than we need.

We want the Repository to expose the operation itself rather than expose locking to the UseCase.

Option 3 — Optimistic Locking

Another option would be adding:

@Version
private long version;

Then transactions might operate like:

A reads:
quantity = 1
version = 5

B reads:
quantity = 1
version = 5

A updates:

quantity = 0
version = 6

B attempts update based on:

version = 5

and receives an optimistic-lock conflict.

Optimistic Locking Can Also Work

The application could:

catch conflict

reload

retry

and eventually discover:

Inventory = 0

This is a legitimate strategy.

But it introduces:

version column

JPA optimistic-lock semantics

retry behaviour

for an operation that PostgreSQL can represent directly as:

UPDATE if quantity is sufficient

Again, possible—but not our preferred v1 solution.

Option 4 — Serializable Transactions

PostgreSQL also provides SERIALIZABLE transaction isolation, which can reject transactions whose concurrent execution cannot be reconciled with a valid serial execution. Applications using that isolation level must be prepared to retry serialization failures.

That is powerful.

But changing the entire Order workflow to:

SERIALIZABLE

would be a broader concurrency strategy than we need for this specific Inventory counter.

Our problem has a much simpler invariant:

available_quantity >= requested quantity

So we do not raise transaction isolation globally merely to solve this one row-level conditional update.

Option 5 — Conditional Atomic UPDATE

PostgreSQL can express our business persistence operation directly:

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

Read that as business language:

for this Product

decrease available quantity by N

but only if current available quantity
is at least N

This combines:

availability check
+
state mutation

inside one SQL statement.

This is our chosen strategy.

Why This Query Is Powerful

Suppose:

available_quantity = 1

Two transactions execute:

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

At PostgreSQL's default READ COMMITTED isolation, if one transaction is already updating the target row, another updater waits. If the first transaction commits, PostgreSQL applies the second operation against the updated row version and re-evaluates the WHERE condition.

So:

Transaction A
→ sees condition true
→ updates 1 → 0

Transaction B eventually re-evaluates:

available_quantity >= 1

0 >= 1
→ false

Therefore B updates:

0 rows

Only one Customer consumes the final unit.

Updated Row Count Is the Result

PostgreSQL UPDATE reports how many rows were updated; zero affected rows is a normal result when no row satisfies the condition.

For our query:

updated rows = 1
→ Inventory consumed

updated rows = 0
→ could not consume

Because product_id is the primary key, the query can never successfully update:

2 rows

for one ProductId.

So the persistence outcome maps naturally to:

boolean

Application Repository Contract

Our application InventoryRepository evolves:

public interface InventoryRepository {

    Optional<Inventory> findByProductId(
            ProductId productId
    );

    void save(
            Inventory inventory
    );

    PageResult<InventorySummary> findPage(
            PageQuery pageQuery
    );

    boolean consumeIfAvailable(
            ProductId productId,
            int quantity
    );
}

The key method:

boolean consumeIfAvailable(
        ProductId productId,
        int quantity
);

contains no:

SQL

EntityManager

row lock

JPA annotation

UseCase sees only business persistence intent.

What Does `false` Mean?

For Order creation:

false

means:

The requested quantity could not be consumed.

That can happen because:

Inventory row does not exist

or:

available quantity is insufficient

From the Customer Order operation, both map to:

INSUFFICIENT_INVENTORY

That matches the error semantics we already chose.

Repository Preconditions

consumeIfAvailable() should only receive:

quantity > 0

because a negative quantity would reverse the meaning of subtraction.

We already validate positive Order quantity at:

HTTP boundary

and:

application/domain boundary

The persistence adapter should still reject an invalid internal call rather than turning a programming mistake into Inventory growth.

For example:

if (quantity <= 0) {
    throw new IllegalArgumentException(
            "Quantity must be positive"
    );
}

This is defensive boundary protection.

Spring Data Persistence Query

Our Spring Data Repository can expose the modifying SQL:

package io.liveklass.ordermanagement.inventory.persistence;

import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;

import java.util.UUID;

interface InventoryJpaRepository
        extends JpaRepository<
                InventoryEntity,
                UUID
        > {

    @Modifying
    @Query(
            value = """
                    UPDATE inventory
                    SET available_quantity =
                            available_quantity - :quantity
                    WHERE product_id = :productId
                      AND available_quantity >= :quantity
                    """,
            nativeQuery = true
    )
    int consumeIfAvailable(
            @Param("productId")
            UUID productId,

            @Param("quantity")
            int quantity
    );
}

Current Spring Data JPA supports @Modifying query methods for update operations and supports an int return value representing the update count.

Why Native SQL Here?

Normally we prefer ordinary JPA mapping when it expresses the persistence requirement clearly.

But this requirement is explicitly relational:

atomically update a counter
only if the current counter
satisfies a database condition

SQL expresses that directly:

SET available_quantity =
    available_quantity - :quantity

WHERE available_quantity >= :quantity

Using native SQL here is not abandoning JPA.

It is using the right persistence tool behind the Repository boundary.

SQL Still Does Not Leak Into UseCase

Architecture remains:

CreateOrderUseCase
    ↓
InventoryRepository
    ↓
JpaInventoryRepository
    ↓
InventoryJpaRepository
    ↓
PostgreSQL

The SQL belongs at:

persistence boundary

not:

application workflow

JPA Adapter

@Repository
public class JpaInventoryRepository
        implements InventoryRepository {

    private final InventoryJpaRepository
            repository;

    public JpaInventoryRepository(
            InventoryJpaRepository repository
    ) {
        this.repository =
                repository;
    }

    @Override
    public boolean consumeIfAvailable(
            ProductId productId,
            int quantity
    ) {
        if (quantity <= 0) {
            throw new IllegalArgumentException(
                    "Quantity must be positive"
            );
        }

        int updatedRows =
                repository
                        .consumeIfAvailable(
                                productId.value(),
                                quantity
                        );

        return updatedRows == 1;
    }

    // existing methods...
}

Application receives:

true / false

Persistence adapter interprets:

SQL update count

Do We Need to Load `Inventory` First?

For stock consumption:

No.

This is an important change from our earlier straightforward implementation.

We no longer need:

Inventory inventory =
        inventoryRepository
                .findByProductId(
                        productId
                )
                .orElseThrow();

if (
        inventory.availableQuantity()
                < quantity
) {
    ...
}

inventory.decrease(
        quantity
);

inventoryRepository.save(
        inventory
);

Instead:

boolean consumed =
        inventoryRepository
                .consumeIfAvailable(
                        productId,
                        quantity
                );

Why Avoid Loading Inventory Here?

First, it would bring back the raceable:

read
→ decide

pattern.

Second, a native modifying query can leave an already-loaded JPA entity stale in the persistence context. Spring Data JPA deliberately does not automatically clear the EntityManager after every @Modifying query because doing so could discard other pending changes.

So the cleanest Order-consumption path is:

do not load managed InventoryEntity

execute conditional update directly

Then there is no stale Inventory object to accidentally reuse.

What Happens to `Inventory.decrease()`?

We do not delete it from the Domain just because Order persistence uses a specialized atomic query.

The method still expresses a valid domain invariant:

inventory.decrease(quantity);

and can be useful when manipulating a standalone Inventory object.

But:

An in-memory domain method cannot provide cross-request database concurrency guarantees.

So:

Inventory.decrease()
→ local object correctness

while:

InventoryRepository.consumeIfAvailable()
→ persistent concurrency correctness

These solve different problems.

Domain Model Does Not Have to Mirror the SQL Operation

Some developers see:

Inventory.decrease()

and assume every persistence path must be:

load Inventory
call decrease()
save Inventory

Not necessarily.

Domain modelling and efficient persistence are related, but they are not identical.

For a concurrency-sensitive counter, specialized persistence behaviour is appropriate.

Revised Create Order Flow

Our previous flow was roughly:

load Product

load Inventory

check Inventory

build OrderItem

decrease Inventory

save Inventory

Now it becomes:

validate Product state

capture Product priceCents

build OrderItem information

consume Inventory atomically

persist Order

The availability check is no longer a separate read.

Product Validation First

Before consuming any Inventory, we should validate all Product-level conditions:

duplicate Product IDs

Product exists

Product active

and capture:

current priceCents

for each line.

Why?

Suppose:

Product A valid

Product B inactive

There is no reason to start acquiring/updating Inventory rows before discovering that the request is already invalid due to Product B.

So first:

validate Product-level state

then:

consume Inventory

Prepared Order Items

We can build the immutable OrderItem information after Product validation:

List<OrderItem> orderItems =
        command.items()
                .stream()
                .map(item -> {
                    Product product =
                            loadOrderableProduct(
                                    item.productId()
                            );

                    return new OrderItem(
                            product.id(),
                            item.quantity(),
                            product.priceCents()
                    );
                })
                .toList();

At this point we know:

all Products exist

all Products are active

all prices came from server

We do not yet claim stock has been consumed.

Then Consume Inventory

For each requested line:

boolean consumed =
        inventoryRepository
                .consumeIfAvailable(
                        item.productId(),
                        item.quantity()
                );

if (!consumed) {
    throw new InsufficientInventoryException(
            item.productId()
    );
}

This is the business persistence decision:

true
→ continue

false
→ reject entire Order

Multi-Item Orders Introduce Another Concurrency Concern

Suppose Order A wants:

Product X
Product Y

while Order B wants:

Product Y
Product X

If transaction A updates X first and waits for Y, while B updates Y first and waits for X:

deadlock

is possible.

PostgreSQL documents that row-level locks can participate in deadlocks and recommends acquiring multiple locks in a consistent order where possible.

We should therefore make Inventory consumption ordering deterministic.

Consume Product Rows in a Consistent Order

The customer's input order does not define Inventory locking semantics.

So before consuming Inventory, we can sort requested lines by:

ProductId UUID

for persistence purposes.

Conceptually:

List<CreateOrderItemCommand> itemsToConsume =
        command.items()
                .stream()
                .sorted(
                        Comparator.comparing(
                                item ->
                                        item.productId()
                                                .value()
                        )
                )
                .toList();

Then every Order acquires Inventory row updates using the same ordering rule:

lowest ProductId
→ highest ProductId

This does not change:

price

quantity

Order business meaning

It only makes concurrent lock acquisition more predictable.

Does UUID Sorting Have Business Meaning?

No.

Earlier we explicitly said random UUID order has no business chronology.

That remains true.

Here UUID sorting is used for a different reason:

consistent technical lock/update order

not:

customer-facing ordering

Same datatype, different purpose.

Deterministic Ordering Reduces Deadlock Risk

It does not mean:

deadlocks can never happen anywhere

A larger application may have other transactions and other lock sequences.

PostgreSQL can detect a deadlock and abort one transaction when a cycle occurs.

Our design goal is:

avoid obvious self-created deadlock patterns

by acquiring Inventory rows consistently.

The Transaction Is Essential

Consider a three-item Order:

A × 1
B × 2
C × 5

Consumption:

A
→ success

B
→ success

C
→ insufficient

If A and B were already committed independently:

Inventory A decreased

Inventory B decreased

no Order created

That would be incorrect.

Therefore all conditional Inventory updates plus Order persistence must participate in:

one database transaction

Current Spring Data JPA guidance recommends defining a transaction boundary around the unit of work when several repository operations need to participate consistently; an outer transaction determines the transaction used by the repository operations inside it.

CreateOrderUseCase Transaction

The final shape starts becoming:

@Transactional
public Order execute(
        CreateOrderCommand command
) {
    ...
}

Inside:

validate Products

consume Inventory A

consume Inventory B

consume Inventory C

persist Order

If any step throws:

transaction fails

and the local database changes must not be committed as a partially successful Order.

The next lesson focuses specifically on those transaction and rollback semantics.

Revised CreateOrderUseCase Shape

Conceptually:

@Transactional
public Order execute(
        CreateOrderCommand command
) {
    ensureNoDuplicateProducts(
            command.items()
    );

    List<OrderItem> orderItems =
            loadAndBuildOrderItems(
                    command.items()
            );

    List<CreateOrderItemCommand>
            itemsToConsume =
            sortForInventoryConsumption(
                    command.items()
            );

    for (
            CreateOrderItemCommand item :
            itemsToConsume
    ) {
        boolean consumed =
                inventoryRepository
                        .consumeIfAvailable(
                                item.productId(),
                                item.quantity()
                        );

        if (!consumed) {
            throw new InsufficientInventoryException(
                    item.productId()
            );
        }
    }

    Order order =
            Order.create(
                    OrderId.newId(),
                    command.customerId(),
                    orderItems,
                    Instant.now(clock)
            );

    orderRepository.save(
            order
    );

    return order;
}

Now concurrency-sensitive Inventory correctness is delegated to the persistence boundary.

We No Longer Pre-Check Available Quantity

Notice we do not do:

if (
    inventory.availableQuantity()
        >= requestedQuantity
) {
    ...
}

before calling consumeIfAvailable().

Why?

Because such a read can become stale immediately.

Even if it says:

10 available

another transaction may consume stock before our mutation.

The only authoritative decision is:

did the conditional UPDATE succeed?

Product Browse Inventory Is Still Fine

Customer Product browsing uses:

availableQuantity > 0

to show currently orderable Products.

That query is allowed to become stale immediately after response.

Browsing is informational.

Order creation is authoritative.

This is a fundamental difference:

Browse
→ snapshot/information

Create Order
→ correctness boundary

"But the Customer Saw It in Stock"

Suppose Customer browses:

Inventory = 1

Two Customers both see the Product.

Both attempt to buy.

Only one wins the concurrent conditional update.

The other receives:

INSUFFICIENT_INVENTORY

That is normal.

The browsing response was never an Inventory reservation.

No Reservation Model

If the business wanted:

Once a Customer sees/adds an item, hold it for 10 minutes.

that would require a completely different domain model involving concepts such as:

reservation

expiry

release

We do not have that requirement.

Current model:

Inventory is guaranteed
only when Order creation successfully commits

Why Not Redis Lock?

We have:

one application database

one Inventory row per Product

one PostgreSQL transaction

PostgreSQL already controls concurrent updates to those rows.

Adding:

Redis

distributed lock

would introduce another system whose lock state must coordinate correctly with PostgreSQL.

We do not need that complexity.

Why Not Java `synchronized`?

Bad:

synchronized
public Order createOrder(...) {
    ...
}

Even if it worked inside one JVM, it would tie correctness to a particular application process.

As soon as the application runs multiple instances:

Instance A

Instance B

their Java monitors do not coordinate.

Inventory correctness belongs where the authoritative state lives:

PostgreSQL

Why Not an In-Memory Lock Map?

Same problem:

Map<ProductId, Lock>

would introduce:

memory lifecycle

lock cleanup

multi-instance inconsistency

process restart issues

while PostgreSQL already serializes conflicting row updates.

Do not create application-level locking infrastructure without need.

Why Not `SELECT` Then Atomic `UPDATE`?

Another pattern would be:

SELECT available_quantity

show nice error

then conditional UPDATE anyway

But the SELECT adds no correctness.

The actual decision still comes from:

UPDATE result

If the Customer-facing error does not need exact current Inventory quantity, skip the unnecessary read.

We Do Not Expose Remaining Quantity on Failure

When consumption fails, we return:

INSUFFICIENT_INVENTORY

not:

Only 3 units remaining

because our API does not currently promise exact Inventory quantities to Customers.

That keeps the operation simple and avoids turning rapidly changing stock into a public guarantee.

Product Missing vs Inventory Failure

Before Inventory consumption, Product validation already ensures:

Product exists

Then:

consumeIfAvailable() == false

can be interpreted in this workflow as:

INSUFFICIENT_INVENTORY

We do not need another Inventory lookup merely to distinguish:

row missing

from:

quantity too low

The Customer cannot fulfil the Order in either case.

What About Product Deactivation During Order Creation?

There is another possible race:

CreateOrderUseCase reads Product active=true

Admin concurrently deactivates Product

Our current requirement does not define strict serialization between:

Product administration

and:

already in-flight Order creation

So we do not lock Product rows merely to solve an unspecified boundary condition.

Our current guarantee is:

if Order creation observes Product inactive
→ reject

If the product later requires:

Once deactivation commits, absolutely no concurrent Order may also commit using that Product,

then Product lifecycle and Order creation would need a stronger shared concurrency policy.

We do not invent that requirement now.

Admin Inventory Set vs Order Consumption

Admin operation:

PUT /inventory/{productId}

means:

set current quantity to X

Order operation means:

subtract N if enough exists

They may touch the same database row concurrently.

PostgreSQL serializes conflicting updates to that row. The final state depends on which update takes effect first:

admin set
then order consume

or:

order consume
then admin set

That fits our current absolute-set admin semantics.

If future requirements need inventory audit/reconciliation semantics that preserve every adjustment as a movement, we would need a richer Inventory model.

`save()` Is Not Our Concurrency Strategy

This distinction matters:

inventoryRepository.save(
        inventory
);

means:

persist state

It does not inherently mean:

safely consume limited stock

Concurrency semantics should be visible in the Repository contract:

consumeIfAvailable(...)

not hidden behind generic save() assumptions.

Why Repository Has a Specialized Operation

Earlier we said Repositories should not become business workflow engines.

That remains true.

consumeIfAvailable() does not implement:

Create Order workflow

It implements one persistence primitive whose correctness depends on the database:

conditional Inventory mutation

UseCase still decides:

when to consume

which Products belong to the Order

what to do if consumption fails

when to persist Order

This is a healthy boundary.

Testing the SQL Behaviour

This particular behaviour cannot be proven adequately by:

Mockito

because the important property is:

what happens when PostgreSQL
receives concurrent updates

So this deserves:

PostgreSQL integration tests

using Testcontainers.

Basic Repository Test

Initial Inventory:

Call:

boolean consumed =
        repository.consumeIfAvailable(
                productId,
                3
        );

Expected:

consumed = true

Reload:

availableQuantity = 7

Exact Remaining Quantity Test

Initial:

Request:

Expected:

true

Final:

The condition is:

available_quantity >= :quantity

not:

available_quantity > :quantity

So consuming the final units is valid.

Insufficient Quantity Test

Initial:

Request:

Expected:

false

Final:

The failed conditional update must not partially decrease Inventory.

Missing Inventory Test

No Inventory row exists.

Call:

consumeIfAvailable(productId, 1)

Expected:

false

No row is created automatically.

Most Important Test — Two Concurrent Buyers

Initial:

availableQuantity = 1

Start:

Transaction A

Transaction B

at approximately the same time.

Both call:

consumeIfAvailable(
    sameProductId,
    1
)

Expected:

exactly one
→ true

exactly one
→ false

Final:

availableQuantity = 0

Never:

two successful consumptions

This is the integration test that proves the concurrency strategy is doing real work.

Concurrent Test Must Use Real Transactions

A useful concurrency test requires:

separate threads

separate transaction boundaries

real PostgreSQL

If both calls accidentally run through:

one transaction

the test does not reproduce the real race.

Test structure should intentionally synchronize both workers so they contend on the same Product.

The exact test utility code can remain focused; the important thing is the scenario being proven.

Multi-Item Rollback Test Comes Next

Another critical test:

Product A
Inventory = 5

Product B
Inventory = 0

Request:

A × 2

B × 1

During workflow:

A conditional update
→ succeeds

B conditional update
→ fails

Final required state:

A = 5

B = 0

no Order

Why A returns to 5 depends on:

transaction rollback

That is the focus of the next lesson.

Failure Is Not a Retry by Default

If:

consumeIfAvailable()
→ false

we do not repeatedly retry the same Order hoping Inventory appears.

The database has already told us:

current state cannot satisfy the request

So:

INSUFFICIENT_INVENTORY

is the correct business result.

Retries are useful for transient technical failures—not for a business condition known to be false.

Deadlock Failure Is Different

A PostgreSQL deadlock is not:

INSUFFICIENT_INVENTORY

PostgreSQL may abort one transaction to resolve a detected deadlock.

That is a technical concurrency failure.

If we later choose to automatically retry selected transaction-level failures, that belongs in a deliberate retry policy.

Do not convert all database concurrency errors into business stock errors.

No Broad Exception Catch

Bad:

try {
    consumeInventory();
} catch (Exception e) {
    throw new InsufficientInventoryException();
}

This could turn:

database unavailable

SQL bug

deadlock

connection failure

into:

INSUFFICIENT_INVENTORY

which is false.

Only:

updatedRows == 0

from our successful conditional query execution maps to insufficient Inventory.

Observability Later

A production system may eventually measure:

Inventory consumption failures

database lock waits

deadlocks

Order creation latency

But we do not introduce monitoring implementation here.

Module 11 covers production observability.

Our architecture should simply make these operations identifiable enough to observe later.

No New Database Column Needed

This concurrency strategy requires no:

version

column.

Our current table remains:

CREATE TABLE inventory (
    product_id UUID PRIMARY KEY,
    available_quantity INTEGER NOT NULL,

    CONSTRAINT inventory_product_fk
        FOREIGN KEY (product_id)
        REFERENCES products(id),

    CONSTRAINT inventory_quantity_non_negative
        CHECK (available_quantity >= 0)
);

The existing primary key gives PostgreSQL a direct way to target one Product's Inventory row.

No Flyway Migration Needed

Because we chose:

conditional UPDATE

rather than:

@Version column

or a new lock table, the schema itself does not change.

This is a persistence behaviour change, not a database structure change.

Final Inventory Responsibilities

Admin state management:

SetInventoryUseCase
    ↓
set available quantity

Customer browse:

Product browse query
    ↓
read available quantity

Order creation:

CreateOrderUseCase
    ↓
consumeIfAvailable()

Order cancellation later:

CancelOrderUseCase
    ↓
restore quantity

Each operation has distinct semantics.

Why This Is Better Than a Generic `updateInventory()`

Imagine:

updateInventory(
    productId,
    quantity
);

What does quantity mean?

set to quantity?

subtract quantity?

add quantity?

reserve quantity?

The name says nothing.

Compare:

setAvailableQuantity(...)

consumeIfAvailable(...)

restore(...)

Business intent is visible.

Revised Architecture

Create Order now uses:

CreateOrderHandler
    ↓
CreateOrderUseCase
    ├── ProductRepository
    │       ↓
    │   load Product state
    │
    ├── InventoryRepository
    │       ↓
    │   conditional atomic UPDATE
    │
    └── OrderRepository
            ↓
        persist Order aggregate

Concurrency implementation stays:

behind Repository

where it belongs.

What We Deliberately Did Not Choose

We did not choose:

naive read-check-save

because it can oversell.

We did not choose:

@Version optimistic locking

because the current conditional counter update is simpler.

We did not choose:

SELECT FOR UPDATE

because a direct conditional UPDATE expresses this operation with fewer steps.

We did not choose:

SERIALIZABLE for every Order

because it is broader than needed for the current invariant.

We did not choose:

Java synchronized

because application-process locks are not the source of truth.

We did not choose:

Redis/distributed locks

because PostgreSQL already owns the Inventory state and can safely coordinate its mutation.

Our Chosen Strategy

Canonical Inventory consumption:

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

Interpretation:

1 updated row
→ consumed successfully

0 updated rows
→ insufficient Inventory

Application Repository:

boolean consumeIfAvailable(
        ProductId productId,
        int quantity
);

For multiple Products:

consume in deterministic ProductId order

and all mutations belong to:

one CreateOrder transaction

Engineering Principle

The core principle:

Do not make a concurrency-sensitive business decision from a value you read earlier if the database can enforce the condition at the exact moment of mutation.

Another:

For Inventory consumption, “check availability” and “decrease quantity” are one operation. Modeling them as one conditional database UPDATE removes the race between checking and writing.

And:

Domain methods protect individual object invariants; database operations protect persisted invariants under concurrent requests. Production correctness often requires both.

Summary

In this lesson, we established that:

read → check → decrease → save is not sufficient for concurrent Inventory consumption.
CHECK (available_quantity >= 0) alone does not prevent logical overselling.
The real persistence operation is consume N units if N units are still available.
Pessimistic row locking can solve the problem but requires an explicit lock/read/update workflow.
Optimistic locking can solve the problem but introduces versioning and retry behaviour.
Serializable isolation can provide stronger transaction guarantees but is broader than required for this specific counter.
Our v1 strategy is a conditional atomic PostgreSQL UPDATE.
The query decrements Inventory only when available_quantity >= requested quantity.
PostgreSQL concurrent updates to the same row coordinate at the database level, and under READ COMMITTED the condition is re-evaluated against the updated row when necessary.
The affected-row count tells us whether consumption succeeded.
One affected row maps to success.
Zero affected rows maps to INSUFFICIENT_INVENTORY in Create Order.
InventoryRepository now exposes consumeIfAvailable(ProductId, quantity).
SQL and JPA details remain inside persistence.
Spring Data JPA @Modifying can execute the update and return its affected-row count.
Create Order no longer preloads Inventory merely to check quantity.
Avoiding that preload also prevents stale managed Inventory state around the native modifying query.
Inventory.decrease() remains useful for local domain correctness but does not provide database concurrency guarantees.
Product validation and server-side price capture happen before Inventory mutation.
Multiple Inventory rows should be consumed in a consistent ProductId order to reduce obvious deadlock patterns.
UUID ordering here is purely a technical lock-ordering mechanism, not business ordering.
Multi-item Inventory updates and Order persistence must participate in one database transaction.
Product browsing remains informational and does not reserve stock.
Two Customers may see the same last Product, but only one may successfully consume it.
No reservation model is introduced.
No Redis lock, Java lock, distributed lock, or new database column is required.
Database failures and deadlocks must not be mislabeled as INSUFFICIENT_INVENTORY.
The concurrency guarantee belongs at the authoritative PostgreSQL persistence boundary.

Next lesson:

Database Transactions

There we will take the now concurrency-safe Inventory operation and make the entire Create Order workflow atomic:

consume Inventory A
consume Inventory B
persist Order
persist OrderItems

must either:

all commit

or:

all roll back

We will cover @Transactional, transaction boundaries, rollback semantics, multi-Repository participation, runtime failures, and why a transaction should wrap the business UseCase rather than individual Repository calls.

The Problem With Read → Check → Write

Database Constraint Alone Does Not Solve It

The Real Operation

Candidate Strategies

Option 1 — Naive Read-Check-Write

Option 2 — Pessimistic Locking

Pessimistic Locking Can Work

Why We Are Not Choosing Pessimistic Locking

Option 3 — Optimistic Locking

Optimistic Locking Can Also Work

Option 4 — Serializable Transactions

Option 5 — Conditional Atomic UPDATE

Why This Query Is Powerful

Updated Row Count Is the Result

Application Repository Contract

What Does false Mean?

Repository Preconditions

Spring Data Persistence Query

Why Native SQL Here?

SQL Still Does Not Leak Into UseCase

JPA Adapter

Do We Need to Load Inventory First?

Why Avoid Loading Inventory Here?

What Happens to Inventory.decrease()?

Domain Model Does Not Have to Mirror the SQL Operation

Revised Create Order Flow

Product Validation First

Prepared Order Items

Then Consume Inventory

Multi-Item Orders Introduce Another Concurrency Concern

Consume Product Rows in a Consistent Order

Does UUID Sorting Have Business Meaning?

Deterministic Ordering Reduces Deadlock Risk

The Transaction Is Essential

CreateOrderUseCase Transaction

Revised CreateOrderUseCase Shape

We No Longer Pre-Check Available Quantity

Product Browse Inventory Is Still Fine

"But the Customer Saw It in Stock"

No Reservation Model

Why Not Redis Lock?

Why Not Java synchronized?

Why Not an In-Memory Lock Map?

Why Not SELECT Then Atomic UPDATE?

We Do Not Expose Remaining Quantity on Failure

Product Missing vs Inventory Failure

What About Product Deactivation During Order Creation?

Admin Inventory Set vs Order Consumption

save() Is Not Our Concurrency Strategy

Why Repository Has a Specialized Operation

Testing the SQL Behaviour

Basic Repository Test

Exact Remaining Quantity Test

Insufficient Quantity Test

Missing Inventory Test

Most Important Test — Two Concurrent Buyers

Concurrent Test Must Use Real Transactions

Multi-Item Rollback Test Comes Next

Failure Is Not a Retry by Default

Deadlock Failure Is Different

No Broad Exception Catch

Observability Later

No New Database Column Needed

No Flyway Migration Needed

Final Inventory Responsibilities

Why This Is Better Than a Generic updateInventory()

Revised Architecture

What We Deliberately Did Not Choose

Our Chosen Strategy

Engineering Principle

Summary

Database Transactions

What Does `false` Mean?

Do We Need to Load `Inventory` First?

What Happens to `Inventory.decrease()`?

Why Not Java `synchronized`?

Why Not `SELECT` Then Atomic `UPDATE`?

`save()` Is Not Our Concurrency Strategy

Why This Is Better Than a Generic `updateInventory()`