JR53359: INITIATEANDCLAIMFIRST METHOD RESULTS IN DB LOCK WAIT ON E_SWI_T TABLE

8.0.1.3-WS-BPMADV-IFJR53359
8.0.1.0-WS-BPMADV-IFJR53359
Version 8.5 Refresh Pack 7 for the IBM Business Process Manager products

APAR status

• Closed as program error.

Error description

• The EJB API method initiateAndClaimFirst sporadically results in
  database lock wait timeouts on the E_SWI_T table. This situation
  occurs when a single request triggers multiple actions on the
  table in two active transactions, and both try to obtain the
  same lock.
  
  The following statement can be seen where the lock wait timeout
  occurs:
  SELECT S.PKID FROM BPEDB.E_SWI_T S WHERE S.HASH_CODE = ? FETCH
  FIRST 1 ROWS ONLY
  
  Example Stack on DB2:
  CWWBA0010E: Unexpected exception during execution.
      com.ibm.bpe.api.UnexpectedFailureException: CWWBA0010E:
  Unexpected exception during execution.
  com.ibm.db2.jcc.am.SqlTransactionRollbackException: DB2 SQL
  Error: SQLCODE=-911, SQLSTATE=40001, SQLERRMC=68,
  DRIVER=4.15.113
   at com.ibm.db2.jcc.am.fd.a(fd.java:683)
   at com.ibm.db2.jcc.am.fd.a(fd.java:60)
   at com.ibm.db2.jcc.am.fd.a(fd.java:127)
   at com.ibm.db2.jcc.am.ResultSet.completeSqlca(ResultSet.
    java:4101)
   at com.ibm.db2.jcc.t4.cb.h(cb.java:287)
   at com.ibm.db2.jcc.t4.cb.a(cb.java:245)
   at com.ibm.db2.jcc.t4.cb.c(cb.java:31)
   at com.ibm.db2.jcc.t4.r.a(r.java:32)
   at com.ibm.db2.jcc.t4.i.readFetch_(i.java:263)
   at com.ibm.db2.jcc.am.ResultSet.flowFetch(ResultSet.java:3863)
   at com.ibm.db2.jcc.t4.c.f(c.java:2396)
   at com.ibm.db2.jcc.am.jc.a(jc.java:210)
   at com.ibm.db2.jcc.t4.c.a(c.java:128)
   at com.ibm.db2.jcc.am.ResultSet.nextX(ResultSet.java:382)
   at com.ibm.db2.jcc.am.ResultSet.next(ResultSet.java:309)
   at com.ibm.ws.rsadapter.jdbc.WSJdbcResultSet.next
    (WSJdbcResultSet.java:3120)
   at com.ibm.bpe.database.Tom.getSharedWorkItem
    (Tom.java:10646)
   at com.ibm.bpe.database.SharedWorkItemCache.get
    (SharedWorkItemCache.java:458)
   at com.ibm.bpe.database.
    SharedWorkItemManager$SharedWorkItemPattern
    Cache.get(SharedWorkItemManager.java:2173)
   at com.ibm.bpe.database.SharedWorkItemManager.
    assignExistingSharedWorkItemID(SharedWorkItemManager.
    java:866)
   at com.ibm.bpe.database.SharedWorkItemManager.
    assignSharedWorkItemID(SharedWorkItemManager.java:774)
   at com.ibm.bpe.database.SharedWorkItemManager.
    transform(SharedWorkItemManager.java:731)
   at com.ibm.bpe.database.SharedWorkItemManager.
    transform(SharedWorkItemManager.java:699)
   at com.ibm.bpe.database.SharedWorkItemManager.
    beforeCompletion(SharedWorkItemManager.java:204)
   at com.ibm.bpe.database.TomExtended.beforeCompletion
    (TomExtended.java:1109)
   at com.ibm.bpe.database.TomExtended.flush(TomExtended.
    java:1078)
   at com.ibm.bpe.framework.BusinessProcessServiceImpl.
    completeTransaction(BusinessProcessServiceImpl.java:39181)
   at com.ibm.bpe.framework.BusinessProcessServiceImpl.
    initiateAndClaimFirst(BusinessProcessServiceImpl.java:40343)
   at com.ibm.bpe.framework.BusinessProcessServiceImpl.
    initiateAndClaimFirst(BusinessProcessServiceImpl.java:27731)
   at com.ibm.bpe.framework.BusinessFlowManagerBean.
    initiateAndClaimFirst(BusinessFlowManagerBean.java:16873)
  

Local fix

Problem summary

• This situation occurs because the initiateAndClaimNext API
  triggers an insert operation into the E_SWI_T table in one
  database transaction followed by a read operation  in a
  different database transaction. Because the first transaction is
  not yet committed, the second transaction must wait until the
  lock wait timeout occurs.
  

Problem conclusion

• A fix is/will be available for IBM BPM V8.0.1.0 that handles the
  lock wait timeout exception properly: The local transaction is
  rolled back, and the global transaction succeeds.
  
  On Fix Central (http://www.ibm.com/support/fixcentral), search
  for JR53359:
  
  1. Select IBM Business Process Manager with your edition from
    the product selector, the installed version to the fix pack
    level, and your platform, and then click Continue.
  
  2. Select APAR or SPR, enter JR53359, and click Continue.
  
  When you download fix packages, ensure that you also download
  the readme file for each fix. Review each readme file for
  additional installation instructions and information about the
  fix.
  

Temporary fix

• Not applicable
  

Comments

APAR Information

• APAR is sysrouted FROM one or more of the following:

• APAR is sysrouted TO one or more of the following:

Fix information

• Fixed component name

  BPM ADVANCED

• Fixed component ID

  5725C9400

Applicable component levels

• R801 PSY

     UP