IT36298: EDI_RECONCILE997.ERR BECAUSE OF DEADLOCKS ON THE CORRELATION_SET TABLE WITH MSSQL

APAR status

• Closed as program error.

Error description

• When process multiple inbound 997 interchanges from the same
  Trading Partner at the same time the customer continually
  receives MSSQL Deadlock errors on the CORRELATION_SET table.
  Therefore causing EDI Reconciliation errors and outbound
  documents are not successfully acknowledgedWhen process
  multiple inbound 997 interchanges from the same Trading Partner
  at the same time the customer continually receives MSSQL
  Deadlock errors on the CORRELATION_SET table. Therefore causing
  EDI Reconciliation errors and outbound documents are not
  successfully acknowledgedThe deadlock is happening when more
  then one of the following update queries run:Update
  CORRELATION_SET set VALUE=@P0 , REC_TIME=@P1, VALUE_UPPER=@P2
  where OBJECT_ID=@P3 and NAME=@P4 and TYPE=@P5
  MSSQL Deadlock
  Reports have been uploaded to the case.
  The customer is able to
  duplicate this deadlock scenario on both their Production and
  Test system.I have also been able to duplicate this on one of
  our in house installs of B2Bi and MSSQL
  At first the customers
  deadlocks were on the SCI_IDX_49 index which is a non unique,
  non clustered index. Therefore we disabled the SCI_IDX_49 index
  but now they are seeing the deadlock on the SCI_PK_133 index
  which is the clustered/unique PK
  Customer and I are seeing the
  same errors so below I have pasted the errors from my in house
  testing:
  The EDI Reconcile Report has the following
  error:
  Group Level Acknowledgement Reconciliation
  Details:Sender ID = PETTESTReceiver ID = PETTESTFunctional ID
  Code = INControl Number = 4Status = AERROR: Exception while
  reconciling group.
  
  The EDI.log and System.log have the
  following error:
  [2021-03-17 13:17:01.906] ERROR 000510150006
  EDI.EDI_RECONCILE997.ERR_Reconcile997_exception4 [Reconcile997]
  Exception while reconciling group [2021-03-17 13:17:01.906]
  ERROR [1616001421906] Transaction (Process ID 59) was
  deadlocked on lock resources with another process and has been
  chosen as the deadlock victim. Rerun the
  transaction.[2021-03-17 13:17:01.907] ERRORDTL
  [1616001421906]com.microsoft.sqlserver.jdbc.SQLServerException:
  Transaction (Process ID 59) was deadlocked on lock resources
  with another process and has been chosen as the deadlock
  victim. Rerun the transaction. at com.microsoft.sqlserver.jdbc.S
  QLServerException.makeFromDatabaseError(SQLServerException.java:
  259) at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextR
  esult(SQLServerStatement.java:1547) at com.microsoft.sqlserver.j
  dbc.SQLServerPreparedStatement.doExecutePreparedStatement(SQLSer
  verPreparedStatement.java:548) at com.microsoft.sqlserver.jdbc.S
  QLServerPreparedStatement$PrepStmtExecCmd.doExecute(SQLServerPre
  paredStatement.java:479) at com.microsoft.sqlserver.jdbc.TDSComm
  and.execute(IOBuffer.java:7347) at com.microsoft.sqlserver.jdbc.
  SQLServerConnection.executeCommand(SQLServerConnection.java:2713
  ) at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeComm
  and(SQLServerStatement.java:224) at com.microsoft.sqlserver.jdbc
  .SQLServerStatement.executeStatement(SQLServerStatement.java:204
  ) at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.exe
  cuteUpdate(SQLServerPreparedStatement.java:426) at com.sterlingc
  ommerce.woodstock.workflow.CorrelationImpl.updateValue(Correlati
  onImpl.java:3117) at
  

Local fix

• STRRTC - B2BISFG-21315
  OAQ / OAQ
  Circumvention : None
  

Problem summary

• Problem Description:
  
  
  
  When processing multiple inbound 997 interchanges from the same
  Trading Partner at the same time, the customer continually
  receives MSSQL Deadlock errors on the CORRELATION_SET table.
  This causes EDI Reconciliation errors and outbound documents are
  not successfully acknowledged.
  
  
  
  The deadlock occurs when more than one of the following update
  queries run:
  
  
  
  Update CORRELATION_SET set VALUE=@P0 , REC_TIME=@P1,
  VALUE_UPPER=@P2 where OBJECT_ID=@P3 and NAME=@P4 and TYPE=@P5
  
  
  
  Platforms Affected:
  
  All Platforms
  
  
  
  Users Affected:
  
  All customers using B2BI using standards
  

Problem conclusion

•     Resolution Summary:
  
  A code fix is provided.
  
  When getting a deadloack error while updating a column in a
  database table, B2BI will retry the update for configurable
  number of times, default is 100 times, before it conclude the
  deadlock is persistent and an error should be written out.
  
  
  
  Delivered in:
  
  6000305
  
  
  
  All future Fix Packs can be found in the
  
  https://www.ibm.com/support/pages/node/6194265.
  

Temporary fix

• No workaround
  

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

  STR B2B INTEGRA

• Fixed component ID

  5725D0600

Applicable component levels