Support Article
Data Set Insert new and overwrite existing records causes lock
SA-37940
Summary
User is on Pega 7.2.1 and reported an issue related to Database table locking when using "Insert new and overwrite existing records" option in a Data Set that is mapped to the table.
They have a data flow sources via report definition against a simple flat table, though it has millions of records.
Destination of the data flow is a data set that points to same table as the source.
The intent is to run set of business rules and update records. They are facing DB locking issue possibly due to contention between Select by Report definition And Delete/Insert (Modify) operations on Data Set against same table.
Error Messages
Not Applicable
Steps to Reproduce
- Create data flow that sources via report definition.
- Create a Data Set and map it to a DB2 table. Destination of the data flow is a data set that points to same table as the source.
- Execute it using the option "Insert new and overwrite existing records" selected in Data Set.
Root Cause
A defect or configuration issue in the operating environment is identified as root cause.
The value for webSphereDefaultIsolationLevel property in Websphere.2 is not set for the JDBC data source representing the database where the table mapped to Data Set was present.
By default, the DB2 isolation level is 4 (Read Stability).
This causes a full table lock while reading data. User needs the isolation level to be set to 2 (Cursor Stability).
Resolution
Make the following change to the operating environment:
Set value of webSphereDefaultIsolationLevel property to 2, for the required JDBC data source configured in Websphere environment.
Published June 1, 2017 - Updated October 8, 2020
Have a question? Get answers now.
Visit the Collaboration Center to ask questions, engage in discussions, share ideas, and help others.