Livelink 9.7.1 performance issues due to Deadlock on ProviderRetry Table

[dkjha0209]

Hello,

The current version of livelink 9.7.1 is having issues with performance due to deadlock on ProviderRetry table.

Contacted OTCS regarding it and they suggest an upgrade to CS10.5 would resolve the issue but business has no plan to move to latest version due to cost limitations.

These days users wait for 10 to 15 mins to reach to the next screen when they click on any button or link. The ProviderRetry table reaches around 8000 rows in a day.

If anyone could suggest something to provide a short term fix for it that would be very helpful.

 

[appnair]

do you store stuff documents etc on EFS or Archive Server.Are the RM module installed and configured

Well, if I called the wrong number, why did you answer the phone?
James Thurber, New Yorker cartoon caption, June 5, 1937
Certified OT Developer,Livelink ECM Champion 2008,Livelink ECM Champion 2010

http://www.tek-tips.com/faqs.cfm?fid=2884

http://www.linkedin.com/in/appunair

http://www.livelink.in

 

[dkjha0209]

Yes, we store documents and workflow form versions on EFS. RM module(Records Management?) module is not installed.

Problem is at every step in workflow the forms are updated and when the changes are saved, a new version of form is created which is stored in .dat format on EFS. Every time a new version of form is created, the older version of form is deleted from EFS and if not deleted it keeps an entry in ProviderRetry table. Later when the .dat file is deleted the entry from ProviderRetry is deleted which is where the deadlock is occurring.

In logs I can see the following error every 5 mins -

"ODBC diagnostic error: 1 ODBC error code: 1205 ODBC state: 40001 ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]Transaction (Process ID 71) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
12/22/2014 15:41:07 [1794350433] 0000081041: KSqlCursor::Execute() --> 'Could not execute cursor.'
12/22/2014 15:41:07 [1794350547] 0000081042: KSqlCursor::Close() --> 'SUCCESS'
12/22/2014 15:41:07 [1794350602] 0000081043: KSql::Execute(...) --> 'Error executing an Sql statement.',0 records,[sec: 7 msec: 376]
12/22/2014 15:41:07 [1794350687] 0000081044: ****** CAPIExec called ...
12/22/2014 15:41:07 [1794350729] 0000081045: KSqlCursor::Open(201f,'delete from ProviderRetry where EntryType =:A1 and ProviderData like :A2') -->'SUCCESS'
"
Please advise.

 

[appnair]

sorry I do not know much about it but you seem to have isolated and understood the process in detail.There are some sqlserver settings taht may help in transaction isolation also if you really do not want forms to happen as .dat you can make the template write to a table in which case only rows will be updated/inserted when subsequent versioning occurs.

Well, if I called the wrong number, why did you answer the phone?
James Thurber, New Yorker cartoon caption, June 5, 1937
Certified OT Developer,Livelink ECM Champion 2008,Livelink ECM Champion 2010

http://www.tek-tips.com/faqs.cfm?fid=2884

http://www.linkedin.com/in/appunair

http://www.livelink.in

 

[davidtOpenText]

Hi,

The code was changed to address the deadlock on this table. There was no solution provided for 971 since it was out of support at the time.