You’re dealing with a classic concurrency problem combined with API-level validation gaps. Here’s how to address all three focus areas:
Part Number Uniqueness Rules:
First, understand how Agile enforces uniqueness. The database has a unique constraint on the part number column, but the Import API can bypass some application-level checks if not configured properly. You need to ensure your import process respects both database-level and application-level constraints.
Verify your Agile configuration in Admin > Data Settings > Classes > Parts. Check that the Number field has these properties enabled:
- Required: Yes
- Unique: Yes
- Validation Rule: Set to enforce uniqueness
However, the real issue is that your code checks for existence, but there’s a race condition between the check and the creation. In a concurrent environment, two processes can both check, both find no existing part, and both attempt to create the same part number. You need synchronization at the application level:
// Pseudocode - Synchronized part creation:
1. Acquire distributed lock for part number (use DB or cache)
2. Within lock scope:
2a. Query for existing part with this number
2b. If exists: release lock, return existing part
2c. If not exists: create new part via Import API
2d. Commit transaction
3. Release lock
4. Return created part
// Use database SELECT FOR UPDATE or Redis lock
Import API Validation:
The Import API has configurable validation levels. You need to enable strict validation mode in your import code. When initializing your import session, set validation parameters:
// Pseudocode - Import API with validation:
1. Create ImportManager instance
2. Set validation mode to STRICT
3. Enable duplicate checking:
- setCheckDuplicates(true)
- setDuplicateCheckFields(["Number"])
4. Set error handling to FAIL_ON_ERROR (not SKIP)
5. Process import
6. Check import results for validation errors
// See Agile Import API Guide Section 6.2
This forces the Import API to perform the same validation checks as the interactive UI, including uniqueness verification.
Pre-Import Duplicate Checks:
Implement a robust two-phase checking strategy:
Phase 1 - Batch Pre-Validation:
Before starting any variant creation, query Agile for all part numbers in your planned batch. Build a set of existing numbers. This eliminates obvious duplicates before you even attempt imports.
// Pseudocode - Batch pre-check:
1. Collect all planned variant part numbers: plannedNumbers[]
2. Query Agile: SELECT number FROM items WHERE number IN (plannedNumbers)
3. Build set of existing numbers: existingSet
4. Filter planned list: newNumbers = plannedNumbers - existingSet
5. Only process newNumbers for import
Phase 2 - Per-Item Verification with Locking:
For each part number in your filtered list, use database-level locking to ensure atomicity:
// Pseudocode - Atomic duplicate check:
1. Start database transaction with isolation level SERIALIZABLE
2. Execute: SELECT * FROM items WHERE number = ? FOR UPDATE
3. If result exists:
3a. Rollback transaction
3b. Log duplicate attempt
3c. Skip to next part
4. If result is empty:
4a. Create part via Import API within same transaction
4b. Commit transaction
5. Handle constraint violation exception as fallback
// This ensures no concurrent process can create same number
Handling Concurrent Variant Jobs:
Since you mentioned multiple variant jobs run concurrently, implement job-level coordination:
-
Centralized Part Number Reservation: Create a reservation table that tracks which part numbers are being created by which job. Before creating a part, insert a reservation record. If the insert fails due to unique constraint, another job has reserved that number.
-
Job Sequencing: If possible, serialize variant creation jobs using a job queue system. This eliminates concurrency issues entirely but may impact throughput.
-
Retry Logic: When a duplicate is detected (either through pre-check or exception), implement intelligent retry with exponential backoff. The part number collision might resolve if you wait for the concurrent job to complete.
Autonumbering Considerations:
You mentioned Agile is configured for autonumbering, but your code is explicitly setting part numbers. This is contradictory. You need to choose one approach:
Option A - Use Autonumbering:
Let Agile generate part numbers automatically. Modify your Import API code to NOT specify the number field. Agile’s autonumber service has built-in locking and uniqueness guarantees. You can configure the autonumber format to match your P-##### scheme.
Option B - Manual Numbering:
If you need custom numbering logic (like incorporating CAD metadata into the number), disable autonumbering for the Parts class and implement comprehensive validation as described above.
Testing and Monitoring:
After implementing these changes, test specifically for concurrency:
- Create a test script that launches 10 concurrent variant creation jobs, all attempting to create parts with overlapping number ranges.
- Verify that no duplicates are created and that all jobs complete successfully (some should skip already-created parts).
- Monitor database deadlocks - if your locking strategy is too aggressive, you might introduce deadlock scenarios that need tuning.
Implement comprehensive logging that captures:
- Every duplicate check attempt and result
- Lock acquisition and release
- Import API validation failures
- Actual database constraint violations (these should be rare with proper pre-checking)
Review these logs regularly to identify patterns. If you see frequent duplicate attempts, investigate why your variant generation logic is producing overlapping part numbers - there might be an upstream issue in how variants are being defined.
This draft is based on general Oracle Agile PLM knowledge. It has not been verified against your specific version and environment. Practitioners: verify the steps and share your experience below.