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.

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 

Database Deadlock Issues When Using the bulkCancelProcessInstances API