SQL deadlocks encountered during bulk project import in Program Management workspace

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

We’re experiencing SQL deadlock errors when attempting to import 120+ projects simultaneously using the Data Loader utility. The import process fails around the 40-50 project mark with deadlock exceptions. I’ve analyzed the database logs and see consistent patterns where transactions are waiting on PROJECT_MASTER and PROJECT_SCHEDULE tables. We’re using default batch size settings (50 records per commit). The transaction isolation level is currently set to READ_COMMITTED. Our onboarding process is severely impacted as we can’t efficiently migrate historical project data. Has anyone successfully tuned batch imports to avoid these deadlocks?

Error pattern we’re seeing:


ORA-00060: deadlock detected
at PROJECT_MASTER (ROWID: AAASs1)
wait resource: TX-00050023-00000156
2

Thanks for the insights. I reduced batch size to 15 but still hitting deadlocks, though less frequently. The sequential processing idea makes sense - are you suggesting I import all PROJECT_MASTER records first, then separately import PROJECT_SCHEDULE records?

3

Another factor to consider is your database’s lock escalation settings. Oracle by default can escalate row locks to page locks under heavy load. Check your DB_BLOCK_SIZE and ensure you’re not hitting lock escalation thresholds during bulk imports. Also review wait events in AWR reports during import windows.

4

Yes, exactly. Split your import into phases: Phase 1 - Project headers (PROJECT_MASTER), Phase 2 - Schedules and milestones (PROJECT_SCHEDULE), Phase 3 - Resource assignments. This eliminates cross-dependency deadlocks. Also consider using SERIALIZABLE isolation level for the import session only - it’s slower but prevents deadlocks entirely.

5

I’ve dealt with this exact scenario. The issue isn’t just batch size - it’s the order of table updates. When you import projects with schedules, the Data Loader locks PROJECT_MASTER first, then tries to lock PROJECT_SCHEDULE. If another concurrent import does it in reverse order, you get classic deadlock. You need to implement sequential processing for interdependent entities.

6

Let me provide a comprehensive solution to resolve your deadlock issues:

1. Deadlock Analysis in DB Logs - Root Cause: Your ORA-00060 error shows a classic circular wait condition. Based on the ROWID reference, multiple Data Loader threads are competing for the same PROJECT_MASTER rows while holding locks on related PROJECT_SCHEDULE rows. This happens because Agile’s Data Loader uses parallel threading by default, and project imports have parent-child relationships that create lock contention.

2. Batch Size Adjustment - Optimal Configuration:

Modify your Data Loader configuration:


loader.batch.size=10
loader.commit.frequency=5
loader.thread.pool.size=2

Key changes:

  • Reduce batch size to 10 (not 15) for projects specifically
  • Commit every 5 records to release locks faster
  • CRITICAL: Reduce thread pool from default 4 to 2 threads maximum

The thread pool reduction is essential - with 4+ threads, you’re guaranteeing lock contention on shared parent projects.

3. Transaction Isolation Tuning - The Game Changer:

For your import session, use SERIALIZABLE isolation:

ALTER SESSION SET ISOLATION_LEVEL=SERIALIZABLE;
-- Run your Data Loader import
ALTER SESSION SET ISOLATION_LEVEL=READ_COMMITTED;

While SERIALIZABLE is slower (expect 30-40% longer import time), it completely eliminates deadlocks by forcing sequential execution of conflicting transactions.

4. Phased Import Strategy:

Restructure your import process:

Phase 1 - Project Headers Only:

  • Import PROJECT_MASTER records with basic metadata
  • No schedule or resource data
  • Use batch size 20 (safe without dependencies)

Phase 2 - Project Schedules:

  • Import PROJECT_SCHEDULE with references to Phase 1 projects
  • Batch size 10
  • Single thread only (thread.pool.size=1)

Phase 3 - Resource Assignments:

  • Import project resources and allocations
  • Can use parallel threads again (dependencies resolved)

5. Database-Level Optimizations:

Add these indexes if missing:

CREATE INDEX IDX_PROJ_PARENT
ON PROJECT_SCHEDULE(PARENT_PROJECT_ID);

CREATE INDEX IDX_PROJ_STATUS
ON PROJECT_MASTER(STATUS, MODIFIED_DATE);

These indexes reduce lock hold times by speeding up foreign key lookups.

6. Monitoring and Validation:

During import, monitor deadlocks:

SELECT blocking_session, wait_time, seconds_in_wait
FROM v$session
WHERE blocking_session IS NOT NULL;

If you still see blocking after these changes, the issue is likely at the application server level (connection pool exhaustion).

Expected Results: With these changes, your 120-project import should complete in approximately 45-60 minutes (vs. the 20-30 minutes you might expect with aggressive settings) but will complete successfully without deadlocks. The phased approach adds overhead but ensures data integrity and eliminates import failures that force you to restart from scratch.

7

The default batch size of 50 is way too aggressive for project imports. Projects have complex dependencies - schedules, milestones, resource allocations. Try reducing to batch size 10-15 and see if that helps. Also check if you have proper indexes on PROJECT_MASTER.PROJECT_ID and PROJECT_SCHEDULE.PARENT_PROJECT_ID.