SDK integration fails to create MCO due to duplicate MCO number collision

PLM Oracle Agile PLM
, , , , , , , ,
1

We’re experiencing failures when creating Manufacturing Change Orders through the Agile SDK in our automated integration workflow. The process worked fine during testing, but in production we’re hitting duplicate MCO number errors even though our numbering sequence should be unique.

Our integration calls the SDK to create MCOs from external manufacturing requests. The error occurs intermittently - about 30% of attempts fail with duplicate number violations. We’ve verified the SDK error handling catches the exception, but the workflow gets blocked and requires manual intervention.

IAgileClass mcoClass = admin.getAgileClass("MCO");
IManufacturingChangeOrder mco = (IManufacturingChangeOrder) mcoClass.createObject();
mco.setValue(ChangeConstants.ATT_COVER_PAGE_DESCRIPTION, description);
// Fails here with: Duplicate MCO number detected

The uniqueness check seems inconsistent. Has anyone dealt with MCO number conflicts in SDK integrations? Our automated workflow resilience depends on resolving this.

2

Yes, we do have parallel processing - up to 5 concurrent threads handling incoming requests. That would explain the 30% failure rate during busy periods. I assumed the SDK would handle the locking internally. Should we implement our own synchronization around the MCO creation calls?

3

I’ve seen this before. The duplicate error usually happens when multiple SDK sessions try to create MCOs simultaneously. Agile’s auto-numbering has a race condition window where two processes can grab the same number before the uniqueness constraint fires. Are you running parallel threads in your integration? Check if multiple requests hit the SDK at the same time during peak hours.

4

I’d also verify the database connection pool settings. If your SDK sessions are sharing connections improperly, you might get transaction isolation issues that contribute to the duplicate detection failures. Check the maxActive and maxWait parameters in your wt.properties configuration. We had similar symptoms that cleared up after tuning the connection pool.

5

Another angle: check your auto-numbering scheme configuration. If you’re using a simple sequence without enough padding or randomization, collisions become more likely under load. We switched to a format that includes timestamp components and reduced conflicts significantly. The format change doesn’t require code modifications, just admin console updates to the MCO class auto-numbering settings.

6

Synchronization helps but isn’t enough. You need to implement retry logic with exponential backoff. When you catch the duplicate error, wait a random interval (50-200ms) and retry the creation. The SDK will assign a new number on the retry. Also consider using a database sequence or UUID prefix to reduce collisions. We’ve used a combination approach - synchronize the critical section AND add retry logic. This brought our failure rate from 25% down to less than 1%. The key is making your workflow resilient to transient failures rather than expecting the SDK to prevent them entirely.

7

Excellent thread - let me provide a comprehensive solution addressing all three focus areas.

MCO Number Uniqueness Check: The root cause is the SDK’s auto-numbering mechanism lacking atomic operations during concurrent access. When multiple threads request MCO creation simultaneously, they can receive the same candidate number before database constraints validate uniqueness. Implement a distributed lock pattern or use database-level sequence generation to ensure true uniqueness at the source.

SDK Error Handling: Your error handling needs to be proactive, not just reactive. Implement a three-tier approach:

private IManufacturingChangeOrder createMCOWithRetry(IAdmin admin, String desc) {
    int maxRetries = 3;
    for (int attempt = 0; attempt < maxRetries; attempt++) {
        try {
            synchronized(MCO_CREATION_LOCK) {
                IAgileClass mcoClass = admin.getAgileClass("MCO");
                IManufacturingChangeOrder mco = (IManufacturingChangeOrder) mcoClass.createObject();
                mco.setValue(ChangeConstants.ATT_COVER_PAGE_DESCRIPTION, desc);
                return mco;
            }
        } catch (DuplicateException e) {
            if (attempt == maxRetries - 1) throw e;
            Thread.sleep(50 + new Random().nextInt(150));
        }
    }
}

The synchronized block prevents local race conditions, while the retry loop with exponential backoff handles distributed conflicts. Log each retry attempt with correlation IDs for troubleshooting.

Automated Workflow Resilience: Make your integration fault-tolerant by implementing:

  1. Circuit breaker pattern - temporarily halt MCO creation if error rate exceeds threshold
  2. Dead letter queue - route failed requests to a separate queue for manual review
  3. Idempotency tokens - include unique request IDs to prevent duplicate MCO creation on retries
  4. Health monitoring - track success rates and alert when they drop below acceptable levels

Additionally, review your auto-numbering scheme. Consider formats like MCO-{YYYY}{MM}{DD}-{SEQ} where SEQ resets daily, reducing collision probability. Configure adequate sequence padding (at least 6 digits) to handle high-volume periods.

For production stability, also tune these Agile server properties:


wt.method.server.maxThreads=75
wt.pom.dbcp.maxActive=100
wt.pom.dbcp.maxWait=180000

This configuration ensures sufficient resources for concurrent SDK operations. Monitor your database connection pool utilization - if you’re consistently hitting limits, scale up the pool size accordingly.

Finally, implement comprehensive logging that captures: thread ID, timestamp, MCO number attempted, success/failure status, and retry count. This telemetry is invaluable for identifying patterns in failures and optimizing your resilience strategy over time.

8

Update: Implemented both the synchronization and retry logic. The combination is working much better - only 2 failures out of 200 test runs. Thanks for the suggestions!