InventoryItem: conflict and SQL log

1. Introduction

If you are choosing an entity to demonstrate optimistic locking, you want one where the error is visible not only in theory, but also in practice. InventoryItem in our Commerce Persistence Lab is a perfect fit: it has availableQty and reservedQty, and any concurrent overwrite quickly turns into a problem that even the business side can understand without a 40-slide presentation.

Imagine two parallel requests (or two threads) reserving stock. Both read availableQty = 10. The first wants to reserve 2, the second — 3. In each one’s head, everything is logical: “10 − 2 = 8” and “10 − 3 = 7”. But if both updates are applied independently, the final record will be either 7 or 8. The correct result is 5, because reservations must be summed. This is exactly the case where “last one wins” is not a strategy, but a bug.

To avoid confusion later, let us fix one simple idea: optimistic locking does not make the system “always successful.” It makes the system honest. Instead of silently overwriting data, we get an explicit conflict and the ability to decide what to do: reread, retry, or return a clear result to the calling code.

Now it is useful to bundle all of this into one lab scenario: take one InventoryItem, collide two independent transactions, and read the conflict from the SQL trace.

2. The InventoryItem model and @Version

The role of @Version here is easy to summarize in one sentence: Hibernate adds the version to the WHERE clause and increments it on a successful UPDATE. For the experiment, we need a working snapshot of InventoryItem on which the conflict is easy to reproduce and then see in the SQL log.

Below is a minimal entity shape. Three things matter: the row has an id, it has a technical version, and it has fields that are genuinely painful to lose during a race.

import jakarta.persistence.Entity;
import jakarta.persistence.GeneratedValue;
import jakarta.persistence.GenerationType;
import jakarta.persistence.Id;
import jakarta.persistence.Version;

@Entity
public class InventoryItem {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id; // Technical PK, row identifier

    @Version
    private Long version; // Version for optimistic locking (Hibernate manages it, not the domain)
}

Long is convenient here because Hibernate will assign the initial version itself when the entity is first synchronized with the DB.

Now let us add stock fields and a small domain operation reserve(int qty). That is enough to see a stale version on real arithmetic, not on an abstract counter.

public class InventoryItem {

    private int availableQty; // How much can still be reserved
    private int reservedQty;  // How much is already reserved

    public void reserve(int qty) {
        // Protection against “reserve -5 items” (in practice, this is often the result of a bug higher up the stack)
        if (qty <= 0) {
            throw new IllegalArgumentException("qty must be positive");
        }

        // Domain rule: you cannot reserve more than is available
        if (availableQty < qty) {
            throw new IllegalStateException("Not enough stock");
        }

        // Important: we change both fields consistently so the “stock math” stays correct
        availableQty -= qty;
        reservedQty += qty;
    }
}

From the database side, only one thing matters for this scenario: the table already has a version column next to the stock fields. If we look at the table after adding versioning, we care about at least these columns:

create table inventory_item (
  id bigserial primary key,
  version bigint not null,       -- Version for optimistic locking
  available_qty int not null,    -- Available for reservation
  reserved_qty int not null      -- Already reserved
);

This column is what allows the second UPDATE to miss the condition version = ?.

3. Reproducible conflict: two transactions

The most common reason a beginner “cannot reproduce optimistic locking” is that they try to do it within a single EntityManager. But one EntityManager means one persistence context, that is, one “snapshot of reality” inside a transaction. Inside a single persistence context, Hibernate must provide an identity map: one database row corresponds to one managed object. Therefore, you cannot get two different “world versions” in one context.

To reproduce the conflict properly, we need two independent transactions. In a live application, these are usually two parallel HTTP operations (even if you do not want to think about HTTP, the world will still live that way). In a lab or test, the easiest way is to simulate this with two EntityManager instances created from one EntityManagerFactory.

The conflict scheme looks very clearly like a sequence of events:

sequenceDiagram
    participant T1 as "Tx1 (EM1)"
    participant DB as PostgreSQL
    participant T2 as "Tx2 (EM2)"

    T1->>DB: "SELECT inventory_item (version=0)"
    T2->>DB: "SELECT inventory_item (version=0)"
    T2->>DB: "UPDATE ... SET ..., version=1 WHERE id=? AND version=0"
    T2-->>DB: commit OK
    T1->>DB: "UPDATE ... SET ..., version=1 WHERE id=? AND version=0"
    DB-->>T1: 0 rows updated
    T1-->>T1: OptimisticLockException

The key point: both transactions start with the same version (version = 0). Then one manages to update the row and increments the version. The second arrives with the old version, and its UPDATE no longer “matches” the row.

A minimal code sample (in a “laboratory scalpel” style, without Spring magic) may look like this:

import jakarta.persistence.EntityManager;
import jakarta.persistence.EntityManagerFactory;

// Two independent EntityManagers => two independent persistence contexts
EntityManager em1 = emf.createEntityManager();
EntityManager em2 = emf.createEntityManager();

// Two independent transactions (this is exactly what we need for a version conflict)
em1.getTransaction().begin();
em2.getTransaction().begin();

Now we read the same row in two contexts:

// Both EMs read the same row, but “hold” it in different persistence contexts
InventoryItem first = em1.find(InventoryItem.class, 1L);
InventoryItem second = em2.find(InventoryItem.class, 1L);

// At the start, versions match because both transactions see the same DB state
System.out.println("Tx1 version = " + first.getVersion());   // Tx1 version = 0
System.out.println("Tx2 version = " + second.getVersion());  // Tx2 version = 0

Next, we make one transaction “overtake” the other:

second.reserve(3);              // Change the managed entity inside Tx2
em2.getTransaction().commit();  // On commit, Hibernate will send UPDATE and increment the version (it becomes 1)

And only after that do we try to commit the first transaction:

first.reserve(2);               // Tx1 still thinks the version is old and current
em1.getTransaction().commit();  // We expect a version conflict on UPDATE (stale version)

In a real test, you would wrap the last commit() in assertThrows, and in a lab run you would see an exception. But the important idea is already clear: the conflict happens not because “two threads are touching something at the same time,” but because the second update tries to write a row with an outdated version.

4. Conflict on flush and commit

One operational fact is important in this scenario: the conflict appears where Hibernate actually sends the versioned UPDATE. So you can see it either on commit(), or earlier — on an explicit flush().

For a lab, flush() is more convenient: it does not end the transaction, it just tells Hibernate to synchronize the persistence context with the DB right now. This makes it easier to tie the exception to a specific UPDATE in the logs and not guess what happened at the commit boundary.

In service code, this often looks like: “after a critical stock change, I want to know immediately whether it succeeded or conflicted.” Then we call flush() in the middle of the method. For example:

import jakarta.persistence.EntityManager;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

@Service
public class InventoryReservationTxService {

    private final EntityManager entityManager;

    public InventoryReservationTxService(EntityManager entityManager) {
        this.entityManager = entityManager;
    }

    @Transactional
    public void reserve(Long itemId, int qty) {
        // 1) Load the managed entity into the current persistence context
        InventoryItem item = entityManager.find(InventoryItem.class, itemId);

        // 2) Change the state in memory (still no SQL)
        item.reserve(qty);

        // 3) Force SQL now so the conflict appears on this line
        entityManager.flush(); // version conflict will appear here
    }
}

Here flush() is a diagnostic button, not an obligatory ritual in every service. After an optimistic conflict, the current transaction is still considered unsuccessful; the point is to see the problem at a clear spot, not only at the very end.

5. SQL log: UPDATE ... WHERE version = ?

The value of the SQL log here is not the exception itself. It shows which transaction went with the old version and why the second UPDATE did not find a row.

For comfortable diagnostics, SQL trace and bind parameters are usually enabled (in the project, this is done with the sql-trace profile). The minimal idea of the configuration looks roughly like this:

logging:
  level:
    org.hibernate.SQL: debug            # Print the SQL sent by Hibernate
    org.hibernate.orm.jdbc.bind: trace  # Print bind parameter values (including version)

Now in the log you will see not only the SQL itself, but also the parameter values. And this is where the useful part begins.

Without @Version, Hibernate usually performs an update roughly like this (simplified):

With @Version, you will see SQL like this (roughly; formatting depends on settings):

update inventory_item
set available_qty = ?, reserved_qty = ?, version = ?
where id = ? and version = ?

Notice the dual role of version: it appears in both SET and WHERE.

In WHERE, Hibernate puts the “old version” — the one the object had when it was loaded into the persistence context. In SET, Hibernate puts the “new version” — usually oldVersion + 1. This is an important psychological point: the version increases not because you increased it. It increases because Hibernate considers the update successful and “moves” the row to the next revision.

Now let us imagine our conflict. The second transaction (Tx2) commits first. In the log you will see roughly this sequence of events: first SELECT, then UPDATE with version 0 in WHERE. And if bind parameters are enabled, you will literally see the version value:

Hibernate: update inventory_item set available_qty=?, reserved_qty=?, version=? where id=? and version=?
binding parameter (3:BIGINT) <- [1]   -- new version
binding parameter (5:BIGINT) <- [0]   -- old version in WHERE

This is the ideal case: you can see that Hibernate is trying to “raise” the version from 0 to 1.

Then it is the first transaction’s turn (Tx1). It also believes that version 0 is current (it read the row earlier!). So it will generate the same UPDATE — with version = 0 in WHERE. But the database already has version 1 because Tx2 managed to update the row.

What will you see in the logs? You will again see UPDATE ... WHERE version = 0, and then you will get an exception. The most important part of the explanation sounds like this: “The SQL is syntactically correct, but it did not find a row that matches the conditions id = ? AND version = 0, because the current version in the database is already different.”

At that point, Hibernate throws OptimisticLockException (or a more “internal” Hibernate exception that is propagated and wrapped). At the Spring layer, you often see something from the ObjectOptimisticLockingFailureException family. Do not be frightened by the long names: the meaning is the same — “the update was not applied because the version is stale.”

The most valuable brain exercise (and a useful production-analysis skill) is to learn to ask yourself two questions when reading the SQL log:

The first question is: “Which version was loaded into the transaction?” You can see that either from the SELECT (if you print the entity state), or from WHERE version = ? in the UPDATE.

The second question is: “Who managed to bump the version first?” You can see that from which UPDATE went through first and which new version it set in SET.

As soon as you answer those two questions, the conflict stops being a “mysterious ORM error” and becomes plain math: two processes tried to update one row, one got there first, the second arrived with an old revision number.

6. Project scenario: independent transactions

After the lab with two EntityManager instances, it is important to see that in an application this is not an artificial trick. Each separate request or background operation comes with its own transaction and its own persistence context, so the stale version appears in the same way, only under real load.

In Commerce Persistence Lab, reservation logic usually lives in the service package of the inventory feature. We only need one short idea: the service reads InventoryItem, calls reserve(qty), and commits the changes. With optimistic locking, @Version guarantees that if someone else has already updated this same InventoryItem in parallel, you get an explicit conflict instead of a silent overwrite.

Optimistic locking itself still remains Hibernate’s responsibility; the repository here is just a regular entry point to the entity:

import org.springframework.data.jpa.repository.JpaRepository;

// Repository as the entity “entry point”; Hibernate performs optimistic locking via @Version
public interface InventoryItemRepository extends JpaRepository<InventoryItem, Long> {
}

And the service, with explicit flush() for diagnostics, looks like this:

import jakarta.persistence.EntityManager;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

@Service
public class InventoryService {

    private final EntityManager entityManager;

    public InventoryService(EntityManager entityManager) {
        this.entityManager = entityManager;
    }

    @Transactional
    public void reserve(Long itemId, int qty) {
        // Find the entity and work with it as a managed object
        InventoryItem item = entityManager.find(InventoryItem.class, itemId);

        // All business math stays in the domain
        item.reserve(qty);

        // Makes the conflict visible here, not somewhere at the commit boundary
        entityManager.flush();
    }
}

Here flush() at the end is not a “must always do this” requirement. It is a way to make the conflict visible where it is easier to control the outcome of the operation. In some scenarios, you will leave everything to commit. But for learning and for diagnostics, this is a very convenient place: you clearly see that the conflict belongs to the database write, not to your arithmetic in Java.

7. Mini test: conflict in one run

Here we need the shortest possible microscope: one run, one stale version. For this, you do not even need multithreading — it is enough to create two EntityManager instances manually and control the transactions.

A minimal test skeleton (in a “so that it is clear where we are at all” style) may look like this:

import jakarta.persistence.EntityManagerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;

// Start the Spring context to obtain a configured EntityManagerFactory
@SpringBootTest
class InventoryOptimisticLockTest {

    @Autowired
    EntityManagerFactory emf;
}

Next comes the actual scenario. We intentionally create two transactions, read one row, and commit in the “wrong” order:

import jakarta.persistence.EntityManager;
import org.junit.jupiter.api.Test; 
import static org.junit.jupiter.api.Assertions.assertThrows;

class InventoryOptimisticLockTest {

    @Test
    void shouldFailOnStaleVersion() {
        // Two independent persistence contexts so each “lives in its own world version”
        EntityManager em1 = emf.createEntityManager();
        EntityManager em2 = emf.createEntityManager();

        em1.getTransaction().begin();
        em2.getTransaction().begin();

        // Both read the same row with the same version
        InventoryItem a = em1.find(InventoryItem.class, 1L);
        InventoryItem b = em2.find(InventoryItem.class, 1L);

        // Tx2 overtakes: it commits first and bumps the version in the DB
        b.reserve(3);
        em2.getTransaction().commit(); // successful commit, version increased

        // Tx1 still thinks version 0 is current
        a.reserve(2);

        // Explicit flush makes the stale version visible at the most predictable point
        assertThrows(jakarta.persistence.OptimisticLockException.class, em1::flush);
    }
}

On plain JPA, this will most often be an OptimisticLockException. On layers above JPA, you may encounter Hibernate and Spring wrappers as well, so it is useful to anchor on the point where the conflict appears — flush with a stale version — rather than on the idea that it must only surface on commit().

This also makes the boundary of optimistic locking very clear. If such conflicts are rare, @Version is an excellent default: we honestly detect the race and decide what to do next. But if one InventoryItem turns into a hot row, and each failed attempt triggers another expensive read-write cycle, the retry cost starts to rise quickly.

8. Typical optimistic locking mistakes

Mistake #1: trying to reproduce a conflict inside one EntityManager.
When you call find() twice in one transaction, Hibernate gives you the same managed object from the first-level cache. That is one snapshot of reality, and it cannot conflict with itself. You need two independent persistence contexts for a conflict: either two EntityManager instances, or two parallel requests in a real application.

Mistake #2: expecting the conflict to appear at the moment you call reserve() or a setter.
Optimistic locking is a row-write-level check. Until SQL is sent, there is nothing to compare the conflict against. That is why you will see the exception on flush() or on commit(). If you want to catch the conflict “earlier” and closer to business logic, use an explicit flush() as a diagnostic tool.

Mistake #3: focusing only on the exception and ignoring the SQL.
The exception text is useful, but it is often wrapped and can look scary. The SQL log shows the essence: UPDATE ... WHERE version = ?. If you learn to spot version in the WHERE clause with your eyes, you will understand optimistic locking faster than your brain can open Stack Overflow.

Mistake #4: having bind parameters disabled and trying to guess which version participated in the conflict.
Without parameters, you will see where id=? and version=?, but you will not see what exactly was in version. For a training lab, it is almost always worth enabling org.hibernate.orm.jdbc.bind: trace so you can see the values and know exactly which transaction went with the old version.

Mistake #5: trying to “swallow” the optimistic lock exception inside the transaction and continue as if nothing happened.
A version conflict means your update attempt was not applied. Moreover, the transaction usually has to be rolled back. So the correct style is to treat this as a separate operation outcome: either return the conflict to the caller, or start a new attempt in a new transaction (and only if the business case justifies it). Usually, you cannot and should not “force” the situation within the same attempt.