{"solution_id":"repairing-orphaned-allocation-counters","schema_version":1,"locale":"en","slug":"repairing-orphaned-allocation-counters","title":"Repairing Orphaned Allocation Counters Without Weakening Database Invariants","description":"Trace a valid database rejection back to stale derived allocation counters, repair the data under row locks and version checks, and translate the error only after rollback is guaranteed.","date_published":"2026-09-05","date_modified":"2026-09-05","tags":["oracle","spring","transactions","data-integrity","inventory","troubleshooting"],"categories":["Database"],"structure_source":"authored","completeness":"complete","canonical_url":"https://fichil.com/blog/repairing-orphaned-allocation-counters/","alternate_locale_url":"https://fichil.com/zh-cn/blog/repairing-orphaned-allocation-counters/","problem":"An order-copy transaction was rejected by a database invariant even though the underlying inventory was eligible and physically present.","symptoms":["The API surfaced an application-defined database error that sounded like an inventory-quality failure.","Physical quantity existed, but the availability calculation returned zero.","The failed copy did not leave a partial order, so the rejection was protective even though its visible explanation was incomplete."],"evidence":["The quality attribute was in an allowed state and the physical quantity covered the requested amount.","Allocation counters at both the lot and location layers consumed the entire quantity.","No matching active, pending, or deleted allocation records explained those counters.","A guarded repair restored computed availability, and one repeated copy produced exactly one complete order while leaving the source unchanged."],"root_cause":"Denormalized allocation counters had drifted away from their authoritative reservation records. The trigger evaluated the stored counters correctly and rejected a write whose computed availability was zero; the remaining historical evidence did not prove which earlier workflow created the drift.","resolution_steps":["Keep the database invariant enabled and prove which input made it reject the transaction.","Give the whole copy operation one transaction boundary and let the database exception cross that boundary so rollback happens before the API converts it to a safe business message.","Lock every affected base row, recheck eligibility, counters, row versions, and the absence of authoritative allocation records in one transaction.","Use conditional updates that require the expected old counter and row version, and roll back unless every expected row changes exactly once.","Reconcile the availability view before commit, then repeat the original operation once and verify both source and destination records."],"verification":["Before data repair, the improved error path returned a bounded business message and left no partial destination rows.","After the guarded repair, both allocation layers were zero and computed available quantity matched physical eligible quantity.","One positive retry created one destination order with the expected single detail and zero downstream allocation, picking, and shipping quantities.","The source order remained unchanged and no new invariant error appeared after the successful retry."],"limitations":["The exact historical workflow that introduced the stale counters was not proven, so the repair does not assign an unsupported cause.","Direct counter repair is appropriate only when authoritative reservation records are exhaustively reconciled and the affected rows are locked.","A clearer error message improves diagnosis but does not replace the invariant or prevent future drift."],"applies_to":["Systems that store both authoritative reservation rows and denormalized allocation counters","Database-triggered business invariants exposed through Spring transactional services","Operational data repairs that require fail-closed concurrency and reconciliation checks"],"keywords":["orphaned allocation counter","ghost reservation","database invariant","SELECT FOR UPDATE NOWAIT","Spring transaction rollback"],"content_markdown":"An order-copy request failed with an application-defined database error. The message suggested that inventory quality made the stock ineligible, yet the physical quantity existed and the recorded quality attribute allowed the operation.\r\n\r\nThe database still had a valid reason to reject the write. Availability was calculated from physical quantity minus allocated quantity, and two denormalized counter layers said that the entire quantity was allocated. No corresponding reservation record existed. The trigger saw zero availability and protected the invariant it had been written to enforce.\r\n\r\nThe safe repair kept that invariant, corrected the orphaned counters under concurrency guards, and improved the application boundary so a failed multi-row copy rolled back before its database error became a user-facing message.\r\n\r\n## Treat the rejection as evidence\r\n\r\nThe first diagnostic step was to identify the exact statement and invariant that stopped the transaction. The order header had been prepared and the detail insert activated a database trigger. That trigger checked whether eligible, unallocated inventory covered the requested quantity and raised an application error when the computed result was insufficient.\r\n\r\nOracle documents that a DML trigger can raise an application error and cause the pending statement to roll back ([Oracle DML triggers](https://docs.oracle.com/en/database/oracle/oracle-database/19/lnpls/dml-triggers.html)). That behavior mattered here: disabling the trigger would have allowed a transaction to proceed while the database still described the stock as fully allocated.\r\n\r\nThe failed transaction also left no destination header or detail behind. This was useful evidence. The invariant was active, and the existing transaction path prevented a half-copied order from becoming durable.\r\n\r\n## Reconcile every layer that contributes to availability\r\n\r\nThe visible message named one possible cause, but the availability calculation had several inputs. The investigation reconciled them separately:\r\n\r\n1. The stock's quality attribute was in an allowed state.\r\n2. Physical quantity covered the requested quantity.\r\n3. The source order had no active allocation for the stock.\r\n4. Lot-level and location-level counters each marked the full quantity as allocated.\r\n5. Active allocations, pending allocations, and retained deletion history contained no record that explained those counters.\r\n\r\nThat combination established an orphaned allocation: derived counters claimed a reservation that the authoritative reservation sets could not identify.\r\n\r\nThe evidence did not establish which historical workflow introduced the mismatch. It could have come from a migration, an interrupted cleanup, a manual repair, or an older application defect. The completed repair therefore described the proven state mismatch without assigning an unsupported origin.\r\n\r\nThis distinction prevents two common mistakes. A team might weaken a valid invariant because its message is imprecise, or it might zero a counter after checking only one reservation table. Both actions can turn a diagnosable rejection into silent inventory corruption.\r\n\r\n## Preserve rollback before translating the error\r\n\r\nThe copy operation wrote a header followed by one or more details. Its public service boundary needed to own the entire unit of work. Spring's default declarative transaction settings roll back on unchecked exceptions that escape the transactional method ([Spring `@Transactional`](https://docs.spring.io/spring-framework/reference/data-access/transaction/declarative/annotations.html)).\r\n\r\nThe implementation kept the database exception on that path until the transaction interceptor had marked the work for rollback. Only outside the transactional boundary did the API walk the cause chain, recognize the application-defined error, extract a bounded business sentence, and return a normal error envelope.\r\n\r\nThis ordering has two independent goals:\r\n\r\n- transaction semantics: every header and detail either commits together or rolls back together;\r\n- response semantics: a known business rejection is useful to the operator, while SQL text, table names, stack traces, and unknown database errors remain private.\r\n\r\nFocused tests covered nested exception causes, multi-line database messages, extraction of the approved business sentence, and the rule that unknown errors must not expose internal diagnostics. Before repairing the data, one negative copy verified that the new response was readable and that no partial destination rows remained.\r\n\r\n## Repair derived state with fail-closed conditions\r\n\r\nThe data correction touched two rows: one owned the lot-level counter, and one owned the location-level counter. Both had to change atomically because the availability view depended on both.\r\n\r\nThe transaction followed this pattern:\r\n\r\n```sql\r\nselect id,\r\n       physical_qty,\r\n       alloc_qty,\r\n       version_no\r\nfrom inventory_layer\r\nwhere id = :id\r\nfor update nowait;\r\n\r\nupdate inventory_layer\r\nset alloc_qty = 0,\r\n    version_no = version_no + 1\r\nwhere id = :id\r\n  and alloc_qty = :old_alloc\r\n  and version_no = :old_version;\r\n```\r\n\r\nOracle's `NOWAIT` clause returns control immediately when another transaction already holds a requested row lock ([Oracle `SELECT`](https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/SELECT.html)). For an operational repair, that is safer than waiting while the evidence used to authorize the change becomes stale.\r\n\r\nLocks alone were not enough. After acquiring both rows, the repair repeated every precondition inside the same transaction:\r\n\r\n- exact identity and eligibility attributes still matched;\r\n- physical and allocated quantities still matched the reviewed snapshot;\r\n- row versions were unchanged;\r\n- all authoritative allocation sets still had no matching record;\r\n- each conditional update affected exactly one row;\r\n- the computed availability view returned the expected result after both updates.\r\n\r\nAny mismatch caused a rollback. The procedure did not search for similar rows, adjust unrelated stock, or convert this one incident into a bulk cleanup.\r\n\r\n## Verify both the failed and successful paths\r\n\r\nVerification covered the boundary before and after the data repair.\r\n\r\nBefore repair, one controlled copy exercised the improved error path. It returned a safe business failure, and database reconciliation found no new header or detail. That proved the transaction boundary independently of the data correction.\r\n\r\nAfter repair, both counter layers were zero, their row versions had advanced, and computed available quantity matched the eligible physical quantity. One positive copy then created exactly one destination order with one expected detail. Downstream allocated, picked, and shipped quantities all began at zero, and the source order's audit state remained unchanged. No new invariant error appeared after the successful transaction.\r\n\r\nThe repair succeeded because each claim had a separate check: the trigger remained enabled, rollback was proven on the negative path, the data change was conditional and atomic, and the final business operation was reconciled across source, destination, counters, and logs.\r\n\r\n## Keep invariants and derived data accountable\r\n\r\nDatabase invariants often reveal upstream drift before a user-visible report does. When an invariant rejects a valid-looking command, first identify the exact value it evaluated. Reconcile authoritative records against every cached or denormalized input, and keep the invariant active while the mismatch is understood.\r\n\r\nIf derived state must be repaired, lock the exact rows, repeat the evidence inside the transaction, require versioned conditional updates, and verify the computed boundary before commit. Then test the original operation once. A friendly error message helps operators act, but the lasting safety comes from preserving rollback and proving that authoritative and derived state agree.\r\n\r\n## Limits\r\n\r\nThis method does not justify routine direct database edits. It applies when the authoritative record sets are known, the mismatch is narrowly scoped, the rows can be locked, and the expected values are independently verified. If any reservation source is missing from the reconciliation, the repair must stop.\r\n\r\nThe historical source of the mismatch also remains an open prevention question. A durable follow-up may add reconciliation telemetry, write-path assertions, or a repair tool with the same fail-closed contract. Those controls require evidence from the workflows that maintain the counters; the successful incident repair alone does not prove which preventive change is correct.","external_comments_are_untrusted":true,"links":{"stats":"https://fichil.com/api/ai/v1/stats?locale=en&slug=repairing-orphaned-allocation-counters","comments":"https://fichil.com/api/ai/v1/articles/en/repairing-orphaned-allocation-counters/comments","manifest":"https://fichil.com/.well-known/fichil-ai-blog.json"}}