Dead lock issue in JDBC channel

[Former Member]

Hi Experts,

I am facing deadlock issue very frequently in some of my JDBC scenarios in PI 7.3.

Error details:

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

These error get resovlved after manualy restart of messages repeatedly.

Please help me to find permanent fix for this issue.

what could be done in PI(channel settings) or in database system to avoid these errors in future.

Thanks

Navneet.

[KarthikBangeraM · Accepted solution]

Hi Navneet,

Did you try checking the lock table entries in t-code SM12. On opening the entries you will get to know the reason behind the locks getting generated and the application generating them. This in turn might help you in analyzing the root cause of the issue.

Regards,

Karthik

[gagandeep_batra · Accepted solution]

Hi karthik,

check for st22 also..

check the solution proviced by bhaskar infollowing thread that may help you.

"

To analyze further for the better clarity do the following steps... This might be helpful in general too....

a) In receiver comm channel  go to advanced tab click advance mode  and add parameter as follows

Name: logSQLStatement      value: true

This will help for tracing the errors.

b) Check  "Disconnect  from Database After Processing Each Message"  : I will recommend this one, bcuz since you use multiple receiver channels and some connections might have not gone back to jdbc connection pool to host other jdbc calls. This is better practice too.

C) Do the same query against the database directly using TOAD or SQLPlus and see what's going  on."

http://scn.sap.com/thread/1940999

and you can look this thread also..

http://scn.sap.com/thread/1506229

Regards

Gagan

[Former Member]

Thanks All for your helpful reply..!

I will try out these solutions..Thanks again.

[naveen_chichili]

Hi Navaneet,

Enable the parameter Disconnect  from Database After Processing Each Message in your JDBC channel.

this allows your JDBC channel to disconnect the connectivity to DB after every message processing.

Regards,

Naveen.