Uploaded image for project: 'Red Hat Process Automation Manager'
  1. Red Hat Process Automation Manager
  2. RHPAM-4253

Current index settings might cause DeadLocks in SQL Server


    • Icon: Bug Bug
    • Resolution: Done
    • Icon: Major Major
    • 7.13.1.GA
    • 7.11.1.GA
    • jBPM Core
    • None
    • 2022 Week 26-28 (from Jun 27), 2022 Week 29-31 (from Jul 18), 2022 Week 32-34 (from Aug 8)

      Under heavy stress tests, the following error has been reported in the customer case 03178546

      Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: Transaction (Process ID 1649) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

      The following fix didn't solve the issue: https://access.redhat.com/solutions/3710781

      To solve the issue, the following fix has been applied by the customer, in collaboration with SQL Server DBAs

      We should evaluate to include the fix in the current script we provide for SQL Server:

      "After we run a significant number of stress tests in the UAT environment, we ended up with the conclusion that there was something missing from the database schema.
      The DDL script downloaded from the product site (sqlserver-jbpm-schema.sql) creates 2 indexes on EventTypes table:
      I. create index IDX_EventTypes_Id ON EventTypes(InstanceId);
      II. create index IDX_EventTypes_element ON EventTypes(element);
      It seems that the second one is completely useless, since there are only SELECTs using InstanceId, INSERTs and DELETEs (“delete from EventTypes where InstanceId=? and element=?”), while there is no PK or an index that combines both database columns (“InstanceId” and “element”).
      After we created a new index on both “InstanceId” and “element” the deadlocks completely disappeared. Also the performance of the delete statement was significantly improved."

      Case number: https://access.redhat.com/support/cases/#/case/03178546

            vmudadla@redhat.com Vani Haripriya Mudadla
            fmarchio@redhat.com Francesco Marchioni (Inactive)
            Antonio Fernandez Alhambra Antonio Fernandez Alhambra (Inactive)
            Antonio Fernandez Alhambra Antonio Fernandez Alhambra (Inactive)
            0 Vote for this issue
            5 Start watching this issue