Deadlock Issue in SAP PI/XI Proxy Sender to JDBC Receiver Scenario ?

[Former Member]

Hello All,

      SAP ECC is sending data to third party receiver via PI. The destination is a mysql database and the data will be replicated on the same via JDBC adapter. But when the message load is high (Nearly 5 messages at an instance) we get Deadlock issue in the JDBC channel and the messages are failing. However on reprocessing, the messages are getting cleared. Please find the error below and advice on this.

18.04.2016 10:41:14.449	Error	Could not execute statement for table/stored proc. "pricing_product" (structure "StatementD1") due to com.microsoft.sqlserver.jdbc.SQLServerException: Transaction (Process ID 92) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
18.04.2016 10:41:14.454	Error	JDBC Message processing failed, due to Error processing request in sax parser: Error when executing statement for table/stored proc. 'pricing_product' (structure 'StatementD1'): com.microsoft.sqlserver.jdbc.SQLServerException: Transaction (Process ID 92) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
18.04.2016 10:41:14.455	Error	Adapter Framework caught exception: null
18.04.2016 10:41:14.455	Error	MP: exception caught with cause com.sap.engine.interfaces.messaging.api.exception.MessagingException: Error processing request in sax parser: Error when executing statement for table/stored proc. 'pricing_product' (structure 'StatementD1'): com.microsoft.sqlserver.jdbc.SQLServerException: Transaction (Process ID 92) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
18.04.2016 10:41:14.455	Error	Transmitting the message to endpoint <local> using connection SOAP_http://sap.com/xi/XI/System failed, due to: com.sap.engine.interfaces.messaging.api.exception.MessagingException: Error processing request in sax parser: Error when executing statement for table/stored proc. 'pricing_product' (structure 'StatementD1'): com.microsoft.sqlserver.jdbc.SQLServerException: Transaction (Process ID 92) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction..

Thanks

Selvaganesh

[mate_moricz]

Hi Selvaganesh,

The problem can be caused by network problems between PI and DB.  Threads are not releasing the acquired locks anthe consecutive polling jobs will not get any lock to proceed further thus causing the error you face.

This can be avoided by changing parameters like sqlquerytimeout and driver level timeouts. You can check SAP Note 1078420 and in your case SAP Note 1586058.

Regards,
Mate

[iaki_vila]

HI Selvaganesh,

The problem seems to be in the DB that it can not accept a huge number of requests.

Check also Prateek suggestions here JDBC Receiver Adapter with many deadlocks | SCN

Regards.

[former_member186851]

Did you check for any locks in NWA?