Deadlock issue while sending data from PI to JDBC !!!

[Former Member]

Hi All,

We are getting below error while sending data from PI to JDBC (Database).

Error - JDBC message processing failed; reason Error processing request in sax parser: Error when executing statement for table/stored proc. 'store_order_details' (structure 'Order_details_Statement'): com.microsoft.sqlserver.jdbc.SQLServerException: Transaction (Process ID 61) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

This is happening when more than one PI message is trying to deliver data at the same time to database.

Can anyone let us know the root cause of this issue and how we can rectify this.

Is there some setting which we can use to do parallel processing as Delivering one message at a time to database is causing performance issues & taking lot of time.

Thanks

Neha Verma

[nikhil_bose]

Neha,

Check with DB if stored procedure does COMMIT after desired operation.

-Nikhil

[Former Member]

Hi All,

As per DB Admin, database supports serialization. But still when i select this option in receiver channel, no change is happening.

Thanks

Neha

[Former Member]

Hello,

Select database transaction level as serlizable and then check?

https://help.sap.com/saphelp_nw04/helpdata/en/64/ce4e886334ec4ea7c2712e11cc567c/content.htm

Thanks

Amit Srivastava

[Former Member]

Hi Amit,

i changed the parameter to serializable and restarted 2 msgs simultaneously but again same error.

[Former Member]

Hello,

It seems that ur DB doesn't support serialization? May be u can cross verify the same?

Tick Database ‘Auto-Commit’ Enabled option and then check?

Thanks

Amit Srivastava

[Former Member]

Hi Amit,

Auto-commit option is already enabled.But its not helping.

I willl cross check once the serilizable option with database owner.

Thanks

neha

[Former Member]

Hello Neha,

Can you check the JDBC receiver adapter parameter? Did you use the advance mode parameter for disconnect on each message processing.

What did you use for error conflict resolution?

Regards,

Hiren

[Former Member]

Hi Hiren,

Yes i have used this parameter - Disconnect from database after processing each message.

Also, for Error-Conflict - its Redo.

[Former Member]

Hello Neha,

Strange but can please get below information.

Please check with the DB admin about if the user is getting locked or is there any hanging threads related to user.

Also confirm with DB admin if the exclusive lock is on table or on the row when you try insertign or updating information.

You can share the user from the receiver channel.

Regards,

Hiren A.