How One SQL Statement Solves the Order Closure vs Payment Callback Race

The article addresses a classic concurrency bug: an order timeout closure job and a payment callback can interleave so that a paid order ends up marked CLOSED. The author first illustrates the failure mode — the closure job reads PENDING, the callback flips the row to PAID, then the closure job overwrites it with CLOSED — and states the correctness rule: whichever side completes a constrained state transition in the database first wins; the loser must either abort (closure) or create a compensating refund record (late payment).

Schema and core states

Assumes MySQL 8.x + InnoDB, Spring Boot 3.x, Java 17+. Only three order states exist: PENDING, PAID, CLOSED. The biz_order table stores id, status, expire_at (DATETIME(6)), paid_payment_id (unique), and amount_cent (integer cents). A refund_outbox table holds order_id, payment_id (unique), amount_cent, and status default NEW; the unique key on payment_id deduplicates refund requests.

Order expiry job — atomic conditional UPDATE

The job first selects candidate IDs with a lightweight index scan:

SELECT id FROM biz_order
WHERE status = 'PENDING' AND expire_at <= CURRENT_TIMESTAMP(6)
ORDER BY expire_at, id LIMIT 200

For each ID it executes a single UPDATE that re-checks the state and expiry inside the database , avoiding application/DB clock skew:

UPDATE biz_order
SET status = 'CLOSED'
WHERE id = ? AND status = 'PENDING' AND expire_at <= CURRENT_TIMESTAMP(6)

If the update returns 1, the closure succeeded; 0 means the row was already flipped to PAID by the callback or closed by another instance — both are normal, not errors. The scan interval (e.g., every 5 seconds) only affects closure latency, not correctness.

Payment callback — short transaction with SELECT FOR UPDATE

The verified callback handler runs in a single @Transactional method. It locks the order row:

SELECT status, paid_payment_id, amount_cent
FROM biz_order WHERE id = ? FOR UPDATE

Then it branches on the current state:

PENDING → attempt conditional UPDATE to PAID with the new paid_payment_id. If the UPDATE returns 1, done; otherwise throw (should not happen because we hold the lock).

PAID with same payment_id → idempotent retry, return silently.

PAID with different

payment_id</strong> → duplicate payment, insert refund outbox row.</li>
<li><strong>CLOSED</strong> → late payment, insert refund outbox row.</li>
<li>Any other state → exception.</li>
</ul>
<p>The refund outbox insert uses <code>INSERT ... ON DUPLICATE KEY UPDATE

so the same payment_id never creates two rows. The author emphasizes never calling the external refund API inside the DB transaction ; instead a separate worker reads refund_outbox, derives a stable refund request ID from payment_id, calls the idempotent refund endpoint, and marks the row complete on success.

Verification via integration test

A JUnit test ( OrderRaceTest ) uses a fixed thread pool of two threads, a CountDownLatch to start the closure job and the payment handler simultaneously, and runs 100 iterations with distinct order/payment IDs. It asserts that each order ends either PAID with zero refund rows, or CLOSED with exactly one refund row for that payment_id . The test requires a real MySQL instance (not H2) because InnoDB locking behavior is essential. Additional manual chaos tests include replaying the same payment ID, sending a second different payment ID for an already-paid order, running multiple closure instances, and killing the refund worker after the channel has processed the refund but before the outbox is marked complete.

Operational observability

Three metrics are tracked in production: (1) count of closure UPDATEs returning 0, (2) count of refund outbox rows created by late payments, (3) age of the oldest unprocessed refund outbox row. A sustained rise in the third metric signals that the compensation path is falling behind.

MySQL 8.4: InnoDB lock types set by various SQL statements

MySQL 8.4: Locking reads

Spring Framework: Declarative transaction annotation and proxy

Spring Framework: Task scheduling