JDBC Receiver Adapter with many deadlocks

[former_member182004]

Hi Gurus !

Hi have many interfaces from Idocs to JDBC inserts to a MS SQL Server 2005. I'm using PI 7.0 SP 18.

I'm having a problem when I send many idocs (with many segments) because some messages stay in waiting, or, to be delivered status at the endpoint in message monitoring. The real message is:

Unable to execute statement for table or stored procedure. 'EQUI' (Structure 'JDBCStatement_EQUI') due to com.microsoft.sqlserver.jdbc.SQLServerException: Transaction (Process ID 63) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

It makes 6 retries, and after the sixth failure it stays in status NDLV. Every time that tries to resend the message it makes the same error (deadlock), I guess because there are many messages that are trying to do the same in same moment.

In the receiver CCs I put the value 50 in the Maximum Concurrency field.

Does anyone know which parameters can I improve this situation? The server hasn't any tunning.

Thanks in advance,

Juan

[prateek · Accepted solution]

Based on my experience, these are the things that could be used to improve JDBC performance

1. You may increase the thread count for JDBC related queues. This has to be done in accordance with SAP Note 1084161.

2. There is parameter in JDBC communication channel called Maximum Concurrency. It signifies that one communication channel can make how many connections to database. This is 1 by default and could be increased to some values like 3-4.

3. In the Visual Admin/ NWA, there is a parameter called as queueParallelism.maxReceivers which defines the number of parallel worker threads for one receiver channel instance. This should be done following SAP Note 1136790. This can be done along with the first point.

Regards,

Prateek

[former_member182004 · Accepted solution]

Thanks Prateek, I'll try it to see what happens.

Regards,

[former_member203764]

Hi Juan,

is the deadlock Problem solved?

Regards

Sara

[former_member182004]

Hi Sara, de problem was solved. It was a DB issue.

Regards,

[Former Member]

Hello Juan,

         Can you explain us what was the issue in the DB. Even I am facing a similar error.

Thanks

[former_member182004]

Hi Selvaganesh, it was solved by the DB admin tunning the DB.

I don't remember exactly wich parameters he changed.

Regards,

  Juan.

[Former Member]

Hi Juan,

Apart from what prateek said also do the following on the SQL Server 2005 side....

To reduce the chance of a deadlock:

1)Minimize the size of transaction and transaction times.

2)Always access server objects in the same order each time in application.

3)Reduce lock time in application.

4)Use query hints to prevent locking if possible (NoLock, RowLock)

5)Select deadlock victim by using SET DEADLOCK_PRIORITY.

Regds,

Pinangshuk.

[former_member182004]

Thanx Pinangshuk, I'll try it.

Regards,