Deadlock errors happen when parallel processing is enabled

Deadlock errors in IBM® Predictive Maintenance and Quality typically happen when parallel processing is enabled by increasing extra instances, and all messages are routed to single folders and queues.

About this task

The error message is named EventError.txt and is found in the \error folder in the IBM Integration Bus node, location that is defined by the MQSI_FILENODES_ROOT_DIRECTORY environment variable.

The error message is as follows:

"Error:Label:StdEventLoad_1.LoadEvent:TransactionId:fbc6b4c0-b434-11e2-8336
-09762ee50000TransactionTime:2013-05-04 02:34:022322:Child SQL exception:[unixODBC]
[IBM][CLI Driver][DB2/LINUXX8664] SQL0911N  The current transaction has been rolled 
back because of a deadlock or timeout.  Reason code "2". SQLSTATE=40001"

For more information, see Parallel processing.

Procedure

  1. Connect to the database with the following command:
    db2 connect to db <dbname [IBMPMQ]>
  2. Set the isolation level to RR with the following command:
    db2 set isolation level to RR
  3. Check the value of the dead lock check time setting with the following command:
    db2 get db cfg |grep DL

    The suggested values are:

    Interval for checking deadlock (ms)
    (DLCHKTIME) = 20000
    Deadlock events
    (MON_DEADLOCK) = WITHOUT_HIST
  4. If the value for the DLCHKTIME property is less than 2000, then set the value with the following command:
    db2 update db cfg for <dbname> using DLCHKTIME 20000 immediate
  5. Check the value of Lock list and percentage of Locks that are allowed per application
    db2 get db cfg |grep LOCK

    The suggested values are:

    Maximum storage for lock list (4 KB)
    (LOCKLIST) = 100000
    Percentage of lock lists per application
    (MAXLOCKS) = 97
    Lock timeout (sec)
    (LOCKTIMEOUT) = -1
    Block non logged operations
    (BLOCKNONLOGGED) = NO
    Lock timeout events
    (MON_LOCKTIMEOUT) = NONE
    Deadlock events
    (MON_DEADLOCK) = WITHOUT_HIST
    Lock wait events
    (MON_LOCKWAIT) = NONE
  6. If the value for the LOCKLIST property is less 1000, then set the value with the following command:
    db2 update db cfg for <dbname> using LOCKLIST 100000 immediate
  7. If the value for the MAXLOCKS property is less than 97, then set the value with the following command:
    db2 update db cfg for <dbname> using MAXLOCKS 97 immediate