When using the ActiveMatrix BPM bulkCancelProcessInstances API (from the
ProcessManagerService) to perform bulk cancellation of process instances,
deadlocks can occur in the ActiveMatrix BPM database, resulting in the following
symptoms:

* Bulk cancellation of process instances can take significant amounts of time,
with performance of the cancellation request(s) gradually degrading.

* Multiple instances of the following error can occur in the BPM log file:

     Transaction (Process ID xx) was deadlocked on lock resources with another
     process and has been chosen as the deadlock victim.

* Some process instances enter a Halted state instead of being cancelled.

These symptoms have been traced to the following causes:

* The IDX_PVM_WI_QID_S index on the PVM_WORK_ITEM table does not cover all the
  queries that it needs to. The solution is to replace the existing index with a
  different one.

* There is no index on the managed_obj_id column of the ec_managed_obj_lifecycle
  table. The solution is to drop the primary key for this table and add an
  index.

Database Deadlock Issues When Using the bulkCancelProcessInstances API

All supported operating systems and databases

Run the following SQL queries:

1. Replace the existing IDX_PVM_WI_QID_S index on the PVM_WORK_ITEM table:

   SQL Server:
   ----------------

      DROP INDEX IDX_PVM_WI_QID_S ON amxbpm.PVM_WORK_ITEM
      GO
      CREATE INDEX IDX_PVM_WI_QID_S on amxbpm.PVM_WORK_ITEM (QUEUE_ID, STATUS, TARGET_ID, TARGET_SPACE);
      GO
    
   Oracle:
   ---------
   
      DROP INDEX IDX_PVM_WI_QID_S;
      create index IDX_PVM_WI_QID_S on PVM_WORK_ITEM (QUEUE_ID, STATUS, TARGET_ID, TARGET_SPACE);

   DB2:
   ------

      DROP INDEX IDX_PVM_WI_QID_S;
      create index IDX_PVM_WI_QID_S on PVM_WORK_ITEM (QUEUE_ID, STATUS, TARGET_ID, TARGET_SPACE);

   NOTE: If you subsequently upgrade the ActiveMatrix BPM system you must drop
   this new index and re-create the original index before performing the  
   upgrade. See "Upgrade Impact" below.

      DROP INDEX IDX_PVM_WI_QID_S ON amxbpm.PVM_WORK_ITEM
      GO
      create index IDX_PVM_WI_QID_S on PVM_WORK_ITEM (QUEUE_ID, STATUS) WITH (ALLOW_PAGE_LOCKS = OFF)
      GO
     
2. Drop the primary key from and add a new index to the ec_managed_obj_lifecycle
   table:

   SQL Server:
   ----------------
   
      ALTER TABLE amxbpm.ec_managed_obj_lifecycle DROP CONSTRAINT <PK_CONSTRAINT_NAME>
      GO
      CREATE NONCLUSTERED INDEX ix_ec_managed_obj_lifecycle_idx2 on amxbpm.ec_managed_obj_lifecycle(managed_obj_id)
      GO
      
   where <PK_CONSTRAINT_NAME> is the constraint name of the
   PRIMARY_KEY_CONSTRAINT defined on the ec_managed_obj_lifecycle table. For
   example, PK__ec_manag__237028359E53B458. (See Finding the
   <PK_CONSTRAINT_NAME> below.)
      
   Oracle:
   ---------

      ALTER TABLE ec_managed_obj_lifecycle
      DROP CONSTRAINT PK_EC_MANAGED_OBJ_LIFECYCLE;
      CREATE INDEX ix_ec_lifecycle_idx2 on ec_managed_obj_lifecycle(managed_obj_id);

   DB2:
   ------

      ALTER TABLE ec_managed_obj_lifecycle DROP PRIMARY KEY/
      CREATE INDEX ix_ec_managed_obj_lifecycle_idx2 on ec_managed_obj_lifecycle(managed_obj_id)/
      
      
Upgrade Impact
---------------------

If you subsequently upgrade the ActiveMatrix BPM system you must drop the new
IDX_PVM_WI_QID_S index and re-create the original index before performing the
upgrade. You can obtain the required create statement from the <CONFIG_HOME>\
tibco\data\bpm\<BPM_APP_NAME>\database\ProcessManagercreate.sql file. For
example, on SQL Server:

      DROP INDEX IDX_PVM_WI_QID_S ON amxbpm.PVM_WORK_ITEM
      GO
      create index IDX_PVM_WI_QID_S on PVM_WORK_ITEM (QUEUE_ID, STATUS) WITH (ALLOW_PAGE_LOCKS = OFF)
      GO
      

Finding the <PK_CONSTRAINT_NAME>
------------------------------------------------------

To find the constraint name of the PRIMARY_KEY_CONSTRAINT defined on the
ec_managed_obj_lifecycle table, you can use a query like this:
      
      USE bpm;  
      GO  
      SELECT OBJECT_NAME(object_id) as constraint_name  
          ,SCHEMA_NAME(schema_id) AS schema_name  
          ,OBJECT_NAME(parent_object_id) AS table_name  
          ,type_desc   
      FROM sys.objects  
      WHERE type_desc LIKE '%CONSTRAINT'   
          AND parent_object_id = OBJECT_ID('amxbpm.ec_managed_obj_lifecycle');  
      GO