Question
Anthem
IN
Last activity: 21 Jan 2020 12:06 EST
Slow running sppc_data_uniqueid_withouttran store proc in Production Environment.
Hi Team,
High counts of following slow running Stored Procedure sppc_data_uniqueid_withouttran has been observed in the production environment (PRPC731,MSSQL and TOMCAT).
1) What is the functionality of this SP ?
2)What factors can contribute to its frequent and slow runs and the probable impacts it can cause to the applications?
3) Recommendation or Optimization steps to resolve ?
Thank you.
Pegasystems Inc.
FR
Hello,
Have you tried to work with your DBA? Any advice from his side with the execution plan maybe?
Anthem
IN
Sure.
Meanwhile,could we please obtain responses for the below mentioned.
1) What is the functionality of this SP ?
2)What factors can contribute to its frequent and slow runs and the probable impacts it can cause to the applications?
Anthem
IN
On a side note, When is “sppc_data_uniqueid” used vs “sppc_data_uniqueid_withouttran”.Our application uses only the “sppc_data_uniqueid_withouttran”. This SP does a SELECT with “UPDLOCK, ROWLOCK)” and in the same storage procedure tries INSERT/UPDATE a record. Since a transaction is not used around these two statements, under some special conditions the locking that we observer can happen. Should the process be using “sppc_data_uniqueid”, which puts a transaction around the other SP? Were there any issues while using the one with transaction? Please provide some history regarding the usage of these two SPs?
Pegasystems Inc.
US
Hi Pradeep!
My reading of this is that the caller should use a transaction.
You are correct that without the transaction boundary the statements should be atomic and the purpose of the select with the update rowlock is defeated. In that instance it is possible for 'atomic' transactions to cause a deadlock. Using the outer SP should avoid this...
-Jim