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 number
IT36298
Reported component name
STR B2B INTEGRA
Reported component ID
5725D0600
Reported release
603
Status
CLOSED PER
PE
NoPE
HIPER
NoHIPER
Special Attention
NoSpecatt / Xsystem
Submitted date
2021-03-19
Closed date
2021-10-05
Last modified date
2021-10-22
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
[{"Line of Business":{"code":"LOB02","label":"AI Applications"},"Business Unit":{"code":"BU059","label":"IBM Software w\/o TPS"},"Product":{"code":"SS3JSW","label":"Sterling B2B Integrator"},"Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"603"}]
Document Information
Modified date:
23 October 2021