Spring R2DBC conditional update: test tenant, version and stock floor in one statement

Use the affected-row count as the decision

The checked UPDATE includes SKU, tenant, expected version and available >= units in its predicate. A matching reservation changes one row. A stale version or wrong tenant changes zero rows, and the surrounding transaction rolls back the inserted event marker. A separate SELECT followed by an unguarded UPDATE would reopen the race that the predicate closes. The JDBC counterpart uses the same invariant with a different client API.

Zero rows does not explain which predicate failed. Returning distinct wrong-tenant and stale-version details without care can reveal whether another tenant owns a row. Decide what the caller may learn, then log the internal reason only where authorized. The test checks state; it does not define a deployed HTTP or GraphQL error envelope.

Retry a complete command only when safe

A stale client may re-read and resubmit a new command; a transient database error may justify retrying the original command. Do not blindly retry a stale version with the same expectation. Stable event identity and a unique marker protect repeated delivery of the same committed command, but they do not decide whether a newly calculated reservation is valid.

Checked excerpt

database.sql("update stock_balance set available = available - :units, "
        + "version = version + 1 where sku = :sku and tenant = :tenant "
        + "and version = :expectedVersion and available >= :units")
    .bind("units", units).bind("sku", sku).bind("tenant", tenant)
    .bind("expectedVersion", expectedVersion)
    .fetch().rowsUpdated();

Verification boundary

The Java 21 / Spring Boot 4 source kit checks transactionCommitsMarkerAndVersionedDebitTogether, staleVersionRollsBackEventMarker and wrongTenantRollsBackEventMarker.

Cost and limits

A primary-key lookup with a conditional update is bounded by the matching row and its indexes, but concurrent writers can contend on that row. The embedded H2 result does not prove target-database locking, retry or isolation behavior.

Common Mistakes

• Do not check version and stock in separate unguarded statements.
• Do not infer the exact failure reason from a zero-row count alone.
• Do not retry a stale business command without revisiting its intent.

Read next

Spring JDBC versioned tenant update: inspect the affected row count, Spring R2DBC reactive transaction: roll back the event marker with a rejected debit, Spring GraphQL mutation versions: reject a stale stock reservation, Spring consumer idempotency: reserve an event ID with the stock mutation, Spring transaction isolation: state the anomaly you need to prevent.