BW Transaction manager caused database blocking session
Please refer attached screenshot of our Save Request Process.
1. InsertRequestToDB step - This inserts a new record in Request table in database (SQL Server).
2. AddCaseData, CreateCaseUsingSSO - These are SSOLITE_DATA iProcess Stored Procedure on Oracle database, to start a new case and retrieve the CASE ID for the same.
3. UpdateRequest - Update statement on request table (step 1 request record) to set the Case ID retrieved in step2.
All the above steps are in a Transaction block. Transaction is across both SQL Server and Oracle database steps.
We were doing a load test on the application with around 60 users and after around 11 mins database hanged and we found blocking sessions in SQL Server database which was pointing to a Open Transaction on Request table. SQL query identifed for blocking session was Step 3 Update Request which was waiting to be committed or roll backed. We found following message in our exception log table and at below stack trace - "Commit failed with exception "[tibcosoftwareinc][SQLServer JDBC Driver]Object has been closed.". Will rollback current transaction."
While doing load testing of our application we received following error:
Job-1364 Error in [BusinessProcesses/SubProcesses/OSSPService/spSaveOSSPRequest.process/OSSPSaveRequestTransaction/end] Commit failed with exception "[tibcosoftwareinc][SQLServer JDBC Driver]Object has been closed.". Will rollback current transaction. at com.tibco.plugin.jdbc.JDBCActivity$JDBCTransactionResource.commit(JDBCActivity.java:1641) at com.tibco.pe.core.TransactionGroup.eval(TransactionGroup.java:262) at com.tibco.pe.plugin.Activity.eval(Activity.java:209) at com.tibco.pe.core.TaskImpl.eval(TaskImpl.java:540) at com.tibco.pe.core.Job.a(Job.java:712) at com.tibco.pe.core.Job.k(Job.java:501) at com.tibco.pe.core.JobDispatcher$JobCourier.a(JobDispatcher.java:249) at com.tibco.pe.core.JobDispatcher$JobCourier.run(JobDispatcher.java:200)
This error and stacktrace suggests that OSSPSaveRequestTransaction commit and rollback failed as "[tibcosoftwareinc][SQLServer JDBC Driver]Object has been closed." and due to that a indefinite open transaction was left on the database which caused all other sessions trying to access Request table to be blocked.
This is major stumbling block for us. Please could you suggest ways to get around this issue.
Thanks in advance for your help.