Documentation

fsds/bbu-oracle-processor-race-condition-fix.md

Fix Race Condition in OracleProcessor ItemType/Item Creation

Problem Summary

The OracleProcessor has a race condition causing FK violations when creating Items. The error sequence is:

  1. "duplicate key value violates unique constraint IX_item_types_name"
  2. "ItemType X was created by another worker, looking up existing"
  3. "insert or update on table items violates foreign key constraint FK_items_item_types_item_type_id"

Root Cause

Primary Issue: The itemTypeMap returned by BatchUpsertItemTypesAsync is computed but NEVER USED.

Line 1113: var itemTypeMap = await BatchUpsertItemTypesAsync(...);  // Returns verified map
Line 1220: await SyncStackProductItemsWithContextAsync(...);        // Does NOT receive itemTypeMap!

Instead, SyncStackProductItemsWithContextAsync performs its own independent database query (lines 1927-1935), creating a race condition window where the newly created ItemType/identifier may not be visible yet.

Secondary Issue: The unique constraint on item_types.name is NOT tenant-scoped, causing cross-tenant conflicts.

Implementation Plan

Stage 1: Pass itemTypeMap to SyncStackProductItemsWithContextAsync

File: engines/bbu/src/Acsis.Dynaplex.Engines.Bbu/Services/OracleProcessor.cs

  1. Update method signature (line 1888) - add IReadOnlyDictionary<string, Guid> itemTypeMap parameter
  2. Update call site (line 1220) - pass itemTypeMap to the method

Stage 2: Remove Redundant Database Lookup

File: engines/bbu/src/Acsis.Dynaplex.Engines.Bbu/Services/OracleProcessor.cs

  1. Remove lines 1923-1935 (the independent SKU identifier lookup)
  2. Change line 1969 from itemTypeIdsByCode.TryGetValue to itemTypeMap.TryGetValue

Stage 3: Improve Error Logging

File: engines/bbu/src/Acsis.Dynaplex.Engines.Bbu/Services/OracleProcessor.cs

Update the logging when itemTypeId is not found to clearly indicate this is a bug:

_logger.LogError(
    "Item type for {ItemCode} not found in pre-verified itemTypeMap. " +
    "This indicates a bug in BatchUpsertItemTypesAsync. Stack: {StackId}",
    detail.ItemAlternateCode, stackItem.Id);

Stage 4: Fix Tenant-Scoped Unique Index

File: engines/catalog/src/Acsis.Dynaplex.Engines.Catalog.Database/ItemType.cs

Change line 17:

// FROM:
[Index(nameof(Name), IsUnique = true)]

// TO:
[Index(nameof(TenantId), nameof(Name), IsUnique = true)]

Stage 5: Create Database Migration

Use /generate-migration to create migration that:

  • Drops IX_item_types_name
  • Creates IX_item_types_tenant_id_name on (tenant_id, name)

Files to Modify

File Changes
engines/bbu/src/Acsis.Dynaplex.Engines.Bbu/Services/OracleProcessor.cs Add parameter, update call site, remove redundant lookup, improve logging
engines/catalog/src/Acsis.Dynaplex.Engines.Catalog.Database/ItemType.cs Fix unique index to be tenant-scoped
engines/catalog/src/Acsis.Dynaplex.Engines.Catalog.Database/Migrations/ New migration file (generated)

Why This Fixes the Bug

  1. Eliminates race window: The verified itemTypeMap from BatchUpsertItemTypesAsync is used directly - no gap where data can become stale
  2. Single source of truth: ItemType IDs are looked up once during creation, then reused immediately
  3. Better error handling: If an ItemType creation fails, it fails early with clear logging
  4. Tenant isolation: Each tenant can have their own ItemTypes with the same names

Risk Assessment

  • Low risk: Primary changes are parameter passing and removing redundant code
  • Migration risk: The index change is less restrictive, so existing data should be valid
  • Rollback: Both code and migration can be reverted if issues arise