Iteration and Transaction Boundaries

Be careful when iterating over objects to make sure that you retrieve the object as part of the same transaction that acts on the object. Otherwise, Salesforce B2C Commerce returns an error to prevent you from accidentally overwriting the object with stale data.

Updating Objects Using Iterators

If a node has the Transactional property set to True, it executes as a separate transaction. Script nodes, by default, have the Transactional property set to true.

To successfully update objects you can use one of the following methods:

Method 1: commit each object in a separate Transaction

Identify all objects to update, retrieve each object one at a time and then update each object in a separate transaction. This is the recommended method for updating objects.

An example is a pipeline that does the following:

Assign node From_0 dw.catalog.ProductMgr.queryAllSiteProducts() To_0 products
Loop node that iterates through products where the element key is CurrentProduct and the iterator key is products
1. GetProduct pipelet node supplies a ProductID of CurrentProduct.ID to retrieve a Product
2. Script pipelet node runs ScriptFile UpdateProducts.ds, a custom script file, which opens a transaction, updates the Product, and commits the transaction.

Note: If you use this method, it's important to be aware if a transaction removes or modifies an objects, that object might not exist if it's referenced in later transactions.

Method 2: commit in a single Transaction

Update all objects in a single transaction. This method must only be used if you have a reliably small number of objects to update and you only want changes to be made to all objects or none of them.

An example is a pipeline that does the following:

Assign node From_0 dw.catalog.ProductMgr.queryAllSiteProducts() To_0 products
Script pipelet node runs ScriptFile UpdateProducts.ds, a custom script file, which opens a transaction, iterates through each product, updates it, and commits the transaction.

Note: If you use this method, it's important to be aware that:

the transaction might become very large, with a risk of exceeding the allowed transaction size and using a very large amount of system resources.
the transaction commit might fail due to other concurrent threads. If the commit fails, none of the objects are updated.

Reusing Iterators

As a best practice, do not reuse the same iterator in multiple pipelets in the same pipeline. Doing so can cause an IllegalStateException: Iterator is invalid error.

Iterators in this state don't return accurate results. Any attempt to use them will result in a WARN log message that says Iterator is invalid. Scripts and pipelines that generate this error should be rewritten to ensure they generate correct and complete results.

For example:

The SearchSystemObject pipelet creates a SearchResult iterator.
The ExportCustomers pipelet uses that iterator to export the results to a file. As a side effect, the iterator moves to the end of the collection.
A second pipelet attempts to use the same iterator. At this point, the iterator has been consumed so a warning is logged, and the results are inconsistent.

The correct way to do this is to repeat the SearchSystemObject step so that the second pipelet gets a fresh iterator.