Lock timeouts occur for IBM Business Process Manager (BPM) when sending tracking definitions or transferring data to the Performance Data Warehouse

Technote (troubleshooting)


Problem(Abstract)

The processing of tracking definitions can cause lock contention due to message-driven bean (MDB) concurrency. The processing of data transfer to the Performance Data Warehouse can also lead to lock contention. The lock occurs because multiple threads are trying to create or alter tables in the Performance Data Warehouse at the same time. This scenario can lead to database lock timeouts or hung threads depending on your lock timeout settings.

Symptom

The following example is one of the lock timeouts that you might see. In this case, there is an Oracle database and the error occurs during the DataDefLoader processing (DataDefLoaderBean.onMessage) of the tracking definitions installation. However, this situation might occur with any database type. If the issue is during the next stage of processing, then you might, instead, see the problem with RepresentationManager processing (RepresentationManagerQueueBean.onMessage).

Note: Lock timeouts are very generic errors. This document is only for the specific case where they occur during the processing of tracking definitions or data transfer.

org.springframework.jdbc.BadSqlGrammarException: PreparedStatementCallback; bad SQL grammar [insert into LSW_SNAPSHOT (SNAPSHOT_ID,NAME,DESCRIPTION,SHORT_NAME,ARCHIVED) values (?,?,?,?,?)]; nested exception is java.sql.SQLSyntaxErrorException: ORA-02049: timeout: distributed transaction waiting for lock
at org.springframework.jdbc.support.SQLExceptionSubclassTranslator.translate(SQLExceptionSubclassTranslator.java:78)
at
...
at com.lombardisoftware.server.tracking.definitionloader.TrackingDefinitionTraverser.traverse(TrackingDefinitionTraverser.java:62)
at com.lombardisoftware.server.tracking.definitionloader.BasicDefinitionLoaderImpl.store(BasicDefinitionLoaderImpl.java:76)
at com.lombardisoftware.server.tracking.loader.LoaderStrategy.resumeLoad(LoaderStrategy.java:109)
at com.lombardisoftware.server.tracking.definitionloader.DefinitionLoaderStrategy.resumeLoad(DefinitionLoaderStrategy.java:60)
at com.lombardisoftware.server.ejb.tracking.DataDefLoaderCore.onMessage(DataDefLoaderCore.java:40)
...
at com.lombardisoftware.server.ejb.tracking.DataDefLoaderBean.onMessage(DataDefLoaderBean.java:38)
at com.ibm.ejs.container.MessageEndpointHandler.invokeMdbMethod(MessageEndpointHandler.java:1092)
...
Caused by: java.sql.SQLSyntaxErrorException: ORA-02049: timeout: distributed transaction waiting for lock
...
at oracle.jdbc.driver.OracleStatementWrapper.executeBatch(OracleStatementWrapper.java:213)
at com.ibm.ws.rsadapter.jdbc.WSJdbcPreparedStatement.pmiExecuteBatch(WSJdbcPreparedStatement.java:1016)

Cause

Tracking Definition Installation


The following sequence describes the workflow for processing tracking definitions:

  1. An Enterprise Java™Beans (EJB) call delivers the tracking definitions to the Performance Data Warehouse when tracking definitions are sent or snapshots installed. For more information, see the Sending tracking definitions to Performance Data Warehouse topic in the information center.
    1. The tracking definitions are written to the DataDefLoader JMS queue for later processing.

    2. The EJB call returns.

  2. The DataDefLoader queue is processed:
    1. Tracking definitions are validated.

    2. Metadata tables in Performance Data Warehouse are updated. The LSW_TABLE table might be updated with pending tracking group table changes.


      Note: Data transfer from the Process Server to the Performance Data Warehouse does not occur if there are pending changes. If there are pending changes, there is a warning message in the Performance Admin Welcome page.

    3. A message is written to the RepresentationManager JMS queue for further processing.

  3. The RepresentationManager queue is processed:
    1. New tracking group tables are created, as needed, based on the contents of the metadata tables.

If fields are added to a tracking group, existing tables are updated.
    1. All the tracking group views are re-created.

    2. The LSW_TABLE table is updated to show that all pending table changes are complete.

    3. If all steps complete successfully, the red warning message in Performance Admin console is not displayed and data transfers are allowed again. If there are failures, it can be seen in the SystemOut log files for the Performance Data Warehouse server.


Most problems arise when multiple threads try to create tables and views at the same time. The parallel work can be seen using multiple threads in the same server or in multiple servers in a cluster. Because of the multi-threading, there is lock contention between the threads, which can lead to the lock timeouts related to the DataDefLoader or RepresentationManager message-driven beans.

For data transfer, the process is executed every 30 seconds (or the configured transfer execution interval) on each server in the Performance Data Warehouse cluster. These threads need the same locks to perform their execution. So, if one has not finished yet, then subsequent threads will wait on the locks. Some of the needed locks also contend with those needed to send tracking definitions.

Resolving the problem

In IBM Business Process Manager Version 8.0.1, product improvements have been made to reduce the lock contention that exists in the area of sending tracking definitions and data transfer. The data transfer processing was moved to be driven by the DataDefLoader queue which is also used for processing tracking definitions. These queues have been configured, by default, to be signal-threaded to avoid the lock contention issues. APAR JR44180 is opened to address the same problems on IBM Business Process Manager Version 7.5.1.

These are the steps to take to ensure that tracking definitions are processed on one thread of execution; not in parallel. This approach will improve the issues with tracking definitions. However, the product improvements are needed to avoid the majority of the Performance Data Warehouse lock issues in a clustered environment. To minimize issues with tracking definitions, note the configuration changes to the Performance Data Warehouse and follow the best practices when installing process applications to the process server. Changing the configuration of your IBM Business Process Manager environment can help to limit the number of threads that process tracking definitions.

Configuration Changes
You can make configuration changes so that only one thread is used for each queue, even in a cluster. Both queues might be processed at the same time, but only one message from each queue is processed at the same time.

Note: These configuration changes are made, by default, in IBM Business Process Manager Version 8.0.1.

Configuration changes to activation specifications ensure that only one thread at a time works on messages from the Java™ Message Service (JMS) queue. Changes to bus destinations ensure that only one cluster member works with the queue at a time. To make these changes, update the following specifications and destinations:

Activation specifications:

  • DataDefLoaderActivationSpec
  • RepresentationManagerActivationSpec

Bus destinations:
  • DataDefLoaderQueueDestination....
  • RepresentationManagerQueueDestination...

Note: You might also want to make the changes for the PostLoadCalculationQueue resources.

To update the Activation Specs, make the following changes in the WebSphere Integrated Solutions Console:
  1. From menu on the left, select Resources > JMS > Activation specifications as shown in the following screen shot.




  2. In the activation specifications page, select DataDefLoaderActivationSpec as shown in the following screen shot.




  3. Change the maximum concurrent MDB invocations per endpoint from 10 to 1 as shown in the following screen shot.
  4. Save the changes.

  5. Make the same change for RepresentationManagerActivationSpec


    Tip:
    Recycling the cluster is not always necessary. However, it ensures that the changes are in effect.


To update the bus destinations, complete the following steps in the WebSphere Integration Solutions Console:
  1. From the menu on the left, select Service integration > Buses and then select the PERFDW... bus. See the following screen shots.







  2. On the right of the screen under Destination Resources, select Destinations.




  3. Select DataDefLoaderQueueDestination....




  4. Select Receive exclusive and Maintain strict message order




  5. Save the changes.

  6. Make the same changes for RepresentationManagerQueueDestination....

Tip: Recycling the cluster is not always necessary. However, it ensures that the changes are in effect.


Installing Process Snapshots

When a process snapshot is installed to a Process Server, the tracking definitions are sent to Performance Data Warehouse as part of the installation.

Among other things, you can observe the progress of the Performance Data Warehouse while it processes the tracking definitions by using the Performance Admin Console. For more information, see the Managing Business Performance Data Warehouses topic in the information center.

To avoid Performance Data Warehouse issues, you can use the following best practices for installing your process snapshots:
  • Install the process applications during off-peak times. Data transfer is blocked when tracking definitions start to be processed. Data transfer does not resume until all tracking definitions have been successfully processed.

  • Install one process application at a time and verify that the tracking definitions have completed before installing the next process application.

  • After you receive a message from Process Center or the command line to indicate that the installation of the process application has completed, check the status of the Performance Data Warehouse.

  • In the Performance Admin Console, verify that the tracking definitions have completed:
    1. If tracking definitions are being processed, there will be a red warning message on the welcome screen. The following screen shot shows the warning message, which says: "WARNING: There are pending tracking group maintenance actions. Data loading is temporarily disabled until these operations have been completed."




    2. Refresh the welcome screen to see when tracking definitions have been fully processed. Alternatively, check the View Statistics option in the Performance Admin Console before and after the tracking definitions have been sent to see if the number of tracking groups or other values have increased.


      If there is an error while tracking definitions are being processed, resend the tracking definitions using the Process Admin Console. If it failed again, look at the errors in the log file and complete these processes:
      • If the error indicates that there was an attempt to create a table that already existed, find the table and verify that the table is empty. If the table is empty, drop the table and send the tracking definitions again. A partial failure of the tracking definition installation can lead to this condition.

      • If the error is not about a table already existing, or if the same error recurs when the tracking definitions are sent again, search the IBM Software Support site.

Related information

Tracking Data not being sent to PDW for processing
Disabling tracking data on a specific Process Server

Cross reference information
Segment Product Component Platform Version Edition
Business Integration IBM Business Process Manager Advanced for z/OS Performance Data Warehouse
Business Integration IBM Business Process Manager Standard Performance Data Warehouse AIX, Linux, Linux zSeries, Solaris, Windows 8.0.1, 8.0, 7.5.1, 7.5
Business Integration IBM Business Process Manager Express Performance Data Warehouse Linux, Linux zSeries, Windows 8.0.1, 8.0, 7.5.1, 7.5

Rate this page:

(0 users)Average rating

Add comments

Document information


More support for:

IBM Business Process Manager Advanced
Performance Data Warehouse

Software version:

7.5, 7.5.1, 8.0, 8.0.1

Operating system(s):

AIX, Linux, Linux zSeries, Solaris, Windows, z/OS

Reference #:

1613755

Modified date:

2013-02-19

Translate my page

Machine Translation

Content navigation