Database rollback fails for change notification in ECN Management causing data inconsistency

PLM SAP PLM
, , , , , , , ,
1

We’re facing a serious issue with database rollback operations in our ECN Management module on SAP PLM 2020. When attempting to rollback change notifications that encounter errors during processing, the rollback operation itself fails, leaving our database in an inconsistent state.

The problem manifests when a change notification workflow encounters an error midway through execution - typically during the approval stage or when updating affected items. Instead of cleanly rolling back to the savepoint, we get partial commits that corrupt the change notification state.

ERROR: ORA-01086: savepoint 'ECN_SP_001' never established
at ChangeNotificationService.rollback(line 234)
at WorkflowEngine.handleError(line 567)

This has resulted in change notifications stuck in limbo states - showing as “In Progress” in the UI but actually committed in the database, or vice versa. The savepoint management logic seems broken. We’ve had to manually fix 15+ change notifications in the past month, which is impacting our engineering change process significantly. Any insights on proper rollback logic for ECN workflows?

2

Here’s a comprehensive solution addressing rollback logic, savepoint management, and change notification handling:

1. Rollback Logic Redesign

The fundamental issue is that traditional database rollback doesn’t work reliably with SAP PLM’s long-running ECN workflows due to auto-commits and transaction boundary complexities. Implement a compensation-based approach:

State Tracking Table:

CREATE TABLE ECN_WORKFLOW_STATE (
  ecn_id VARCHAR2(50),
  stage_name VARCHAR2(100),
  stage_status VARCHAR2(20),
  completed_at TIMESTAMP,
  rollback_required CHAR(1)
);

2. Savepoint Management Alternative

Since savepoints don’t survive commits and ECN workflows auto-commit, replace savepoint-based rollback with explicit compensation logic:

// Pseudocode - Compensation pattern implementation:
1. Record workflow stage entry in ECN_WORKFLOW_STATE table
2. Execute stage operations (item updates, approvals, etc.)
3. Mark stage as COMPLETED in state table with commit
4. If error in subsequent stage, read state table
5. Execute stage-specific compensation methods in reverse order
6. Mark stages as ROLLED_BACK in state table
// See ECN Workflow Guide Section 12.3 for compensation handlers

Each ECN workflow stage needs a corresponding compensation handler:

  • ITEMS_UPDATED stage: Compensation reverses affected item associations
  • APPROVALS_CREATED stage: Compensation cancels pending approvals and removes approval records
  • NOTIFICATIONS_SENT stage: Compensation sends cancellation notifications
  • BOM_UPDATED stage: Compensation restores previous BOM revision

3. Change Notification Handling

Implement robust change notification state management:

Immediate Actions:

  • Create a cleanup utility to identify orphaned change notifications:
SELECT ecn.ecn_id, ecn.status, ews.stage_name
FROM ECN_MASTER ecn
LEFT JOIN ECN_WORKFLOW_STATE ews ON ecn.ecn_id = ews.ecn_id
WHERE ecn.status = 'IN_PROGRESS'
AND NOT EXISTS (
  SELECT 1 FROM ECN_WORKFLOW_STATE
  WHERE ecn_id = ecn.ecn_id
  AND stage_status = 'COMPLETED'
  AND completed_at > SYSDATE - 1
);
  • Add a scheduled job that runs every 4 hours to detect and flag stuck change notifications for manual review

Long-term Architecture:

  • Implement idempotent operations for all ECN workflow stages so they can be safely retried
  • Add optimistic locking with version numbers to prevent concurrent modification conflicts
  • Create a workflow audit trail that logs every state transition with before/after snapshots
  • Configure workflow timeout thresholds - if an ECN stage exceeds 30 minutes without progress, auto-trigger compensation

Configuration Changes:

  • Increase UNDO tablespace retention: `ALTER SYSTEM SET UNDO_RETENTION = 3600 SCOPE=BOTH;
  • Disable auto-commit for ECN-specific workflows in your application server configuration
  • Set transaction isolation to READ_COMMITTED with row-level locking
  • Configure connection pool with proper transaction timeout settings (aligned with workflow duration)

Error Recovery Procedure:

  1. When rollback fails, immediately log the ECN_ID and current workflow state
  2. Execute the compensation handler chain in reverse stage order
  3. Update change notification status to ‘ROLLBACK_COMPLETED’ or ‘REQUIRES_MANUAL_REVIEW’
  4. Send automated notification to change managers with recovery details
  5. Use Flashback Query for data verification: `SELECT * FROM ECN_MASTER AS OF TIMESTAMP (SYSTIMESTAMP - INTERVAL ‘1’ HOUR) This compensation-based approach eliminates dependency on database savepoints and provides explicit control over each stage’s reversal logic, significantly improving reliability for complex ECN workflows in SAP PLM 2020.
3

I’ve debugged similar issues in our ECN implementation. The problem often stems from the workflow engine committing intermediate steps automatically. SAP PLM’s workflow framework has an auto-commit feature for certain long-running operations to prevent transaction timeouts. This breaks the savepoint chain because savepoints don’t survive commits. You need to either disable auto-commit for ECN workflows (if possible) or redesign your error handling to use compensating transactions instead of rollbacks. We switched to a compensation pattern where we explicitly reverse operations rather than relying on database rollback, which proved more reliable for complex change workflows.

4

That’s revealing - I wasn’t aware of the auto-commit behavior in long-running workflows. Our ECN approval process can take 2-3 hours with multiple approval stages, so we’re definitely hitting that scenario. The compensating transaction approach makes sense, but how do you handle partial updates to multiple related tables (change notification header, affected items, approval history)? Do you maintain a separate audit trail to track what needs to be reversed?

5

The ORA-01086 error indicates that savepoints are being referenced before they’re established, or they’re being established in a different transaction context than where they’re used. In SAP PLM 2020, ECN workflows often span multiple transaction boundaries due to the workflow engine’s architecture. Check if your savepoint creation is happening in the same transaction scope as the rollback attempt. You might need to use autonomous transactions or restructure your transaction boundaries.

6

Another critical aspect - verify your transaction isolation level configuration. SAP PLM 2020 ECN workflows should use READ_COMMITTED with proper locking strategies. If you’re using SERIALIZABLE isolation, long-running transactions can cause rollback failures due to serialization conflicts. Also check your UNDO tablespace sizing - insufficient UNDO space can cause rollback operations to fail silently or throw unexpected errors. We had to increase our UNDO retention from 900 seconds to 3600 seconds to accommodate our longest ECN workflows.

7

Have you considered using Oracle’s Flashback Transaction feature as a fallback? While not a primary solution, it can help recover from failed rollbacks by allowing you to query and restore data as it existed before the problematic transaction. This is particularly useful for those 15+ manual fixes you mentioned - you can automate the recovery by identifying the SCN (System Change Number) before the failed transaction and using Flashback to restore affected rows. It won’t prevent the issue but can significantly reduce manual remediation time.