Hotfix for CA 8.0 SP5 SQL deadlock error is thrown attempting to access the Monitored Employees tab or any Role Assignment page in the Compliance Accelerator (CA) Client.

Article:TECH194758  |  Created: 2012-08-10  |  Updated: 2012-08-17  |  Article URL http://www.symantec.com/docs/TECH194758
NOTE: If you are experiencing this particular known issue, we recommend that you Subscribe to receive email notification each time this article is updated. Subscribers will be the first to learn about any releases, status changes, workarounds or decisions made.
Article Type
Technical Solution


Environment

Issue



When attempting to access the Monitored Employees tab or any Role Assignment page in the Compliance Accelerator (CA) Client, a SQL deadlock error will be displayed.

 


Error



System.Data.SqlClient.SqlException: Transaction (Process ID 281) was deadlocked
on lock | communication buffer resources with another process and has been chosen
as the deadlock victim. Rerun the transaction.
  at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean
breakConnection)
  at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception,
Boolean breakConnection)
  at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject
stateObj)
  at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand
cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler,
TdsParserStateObject stateObj)
  at System.Data.SqlClient.SqlDataReader.HasMoreRows()
  at System.Data.SqlClient.SqlDataReader.ReadInternal(Boolean setTimeout)
  at System.Data.SqlClient.SqlDataReader.Read()
  at System.Data.Common.DataAdapter.FillLoadDataRow(SchemaMapping mapping)
  at System.Data.Common.DataAdapter.FillFromReader(DataSet dataset, DataTable datatable,
String srcTable, DataReaderContainer dataReader, Int32 startRecord, Int32 maxRecords,
DataColumn parentChapterColumn, Object parentChapterValue)
  at System.Data.Common.DataAdapter.Fill(DataTable[] dataTables, IDataReader dataReader,
Int32 startRecord, Int32 maxRecords)
  at System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[]
datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command,
CommandBehavior behavior)
  at System.Data.Common.DbDataAdapter.Fill(DataTable[] dataTables, Int32 startRecord,
Int32 maxRecords, IDbCommand command, CommandBehavior behavior)
  at System.Data.Common.DbDataAdapter.Fill(DataTable dataTable)
  at KVS.Accelerator.AddressManager.Target.GetGenericTargetDataSet(DataSet DS,
SqlDataAdapter aSqlDataAdapter, String TableName, Int32 TargetID, CaseType aCaseType,
Int32 CaseID, Int64 RowVersion)

User:             EVLAB\user01
Customer:         2
Server:           evdasvr.evlab.com
Domain:           Accelerator Client Domain

Stack:

Server stack trace:
  at KVS.Accelerator.Common.AcceleratorEvent.LogError(String ErrorMessage, String
Description, Boolean ThrowError, Int32 EventID, Exception e)
  at KVS.Accelerator.AddressManager.Target.GetGenericTargetDataSet(DataSet DS,
SqlDataAdapter aSqlDataAdapter, String TableName, Int32 TargetID, CaseType aCaseType,
Int32 CaseID, Int64 RowVersion)
  at KVS.Accelerator.AddressManager.Target.GetDetailDS(Int32 TargetID, CaseType
aCaseType, Int32 CaseID, Int64 RowVersion)
  at KVS.Accelerator.AddressManager.Targets.GetDetailDS(Int32 TargetID, CaseType
aCaseType, Int32 CaseID, Int64 RowVersion)
  at KVS.Accelerator.Web.BO.BO_Targets.get_DetailTargets()
  at KVS.Accelerator.RBO.RBO_Employee.GetBasicEmployees()
  at System.Runtime.Remoting.Messaging.StackBuilderSink._PrivateProcessMessage(IntPtr md,
Object[] args, Object server, Int32 methodPtr, Boolean fExecuteInContext, Object[]& outArgs)
  at System.Runtime.Remoting.Messaging.StackBuilderSink.PrivateProcessMessage(RuntimeMethodHandle md,
Object[] args, Object server, Int32 methodPtr, Boolean fExecuteInContext, Object[]& outArgs)
  at System.Runtime.Remoting.Messaging.StackBuilderSink.SyncProcessMessage(IMessage msg,
Int32 methodPtr, Boolean fExecuteInContext)

Exception rethrown at [0]:
  at System.Runtime.Remoting.Proxies.RealProxy.HandleReturnMessage
(IMessage reqMsg, IMessage retMsg)
  at System.Runtime.Remoting.Proxies.RealProxy.PrivateInvoke
(MessageData& msgData, Int32 type)
  at KVS.Accelerator.Interfaces.IRBO_Employee.GetBasicEmployees()
  at Accelerator.Client.MiddleTierAccess.EmployeeService.GetBasicEmployeesAndGroups()
  at Accelerator.Modules.EmployeeTargetManagementModule.Views.EmployeeManagementSummaryPresenter.PopulateDataModel(Boolean FetchData)

Transaction (Process ID 281) was deadlocked on lock | communication buffer resources with another process and has been chosen as the deadlock victim. Rerun the transaction.:

  at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
  at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
  at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
  at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
  at System.Data.SqlClient.SqlDataReader.HasMoreRows()
  at System.Data.SqlClient.SqlDataReader.ReadInternal(Boolean setTimeout)
  at System.Data.SqlClient.SqlDataReader.Read()
  at System.Data.Common.DataAdapter.FillLoadDataRow(SchemaMapping mapping)
  at System.Data.Common.DataAdapter.FillFromReader(DataSet dataset, DataTable datatable, String srcTable, DataReaderContainer dataReader, Int32 startRecord, Int32 maxRecords, DataColumn parentChapterColumn, Object parentChapterValue)
  at System.Data.Common.DataAdapter.Fill(DataTable[] dataTables, IDataReader dataReader, Int32 startRecord, Int32 maxRecords)
  at System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
  at System.Data.Common.DbDataAdapter.Fill(DataTable[] dataTables, Int32 startRecord, Int32 maxRecords, IDbCommand command, CommandBehavior behavior)
  at System.Data.Common.DbDataAdapter.Fill(DataTable dataTable)
  at KVS.Accelerator.AddressManager.Target.GetGenericTargetDataSet(DataSet DS, SqlDataAdapter aSqlDataAdapter, String TableName, Int32 TargetID, CaseType aCaseType, Int32 CaseID, Int64 RowVersion)

 


Environment



- Microsoft Active Directory with over 100,000 Users and Groups
- Compliance Accelerator 8.0 SP5 on Windows Server 2003 SP2 Enterprise Edition with Enterprise Vault 8.0 SP5 binaries installed.

 


Cause



When the ADSynchroniser process is updating Monitored Employee data, it accesses and locks certain tables in the Compliance Accelerator (CA) Customer database. When clicking on the Employees tab or any Role Assignment sub-tab in the CA Client, these same tables are attempted to be accessed. When one process has a table locked and another process attempts to access the same table, the SQL deadlock error is thrown as the second process cannot access the table needed.

Such SQL deadlocks can be caused by resource constraints on the SQL Server, or by the coding used in the stored procedures that are executed by the processes. The code used by the stored procedures is placing a lock and the number of entries in the affected tables is large enough that the lock is not being released in a timely enough manner to allow the second process to access the tables before the deadlock occurs. These SQL resource and code constraints are not normally seen until the number of Monitored Employees grows to a very large number, typically over 100,000.

 


Solution



Both a solution and a workaround exist for this issue.  The workaround can be used with any version of Compliance Accelerator 8.0 or greater.  The solution is strictly for CA 8.0 SP5.

Solution:

To fix this issue, the following stored procedures must be replaced -

  • usp_Target_Sel
  • usp_Target_UPD

New stored procedures to replace these are included in the hotfix file referenced below.

To install the fix files, complete the following steps.

On the workstations with the Compliance Accelerator Client installed:
1) Close any opened CA Client.

On the CA Server:
2) Log in as the Vault Service Account (VSA).
3) Stop the Enterprise Vault Accelerator Manager Service (EVAMS) or the Customer Background Task (CBT) of the CA Customer encountering the issue.

On the SQL Server hosting the CA Customer database encountering the issue:
4) Log onto the SQL Server using an account that has permission to change the contents of the CA Customer database, such as the Vault Service Account (VSA).
5) On any drive attached to the server, create a folder to contain the fix install and removal files.
6) Download the compressed file 'Etrack_2754799_SQL_Deadlock_Refreshing_Employees_or_Role_Assignments.zip' referenced below containing the fix install and removal files.
7) Uncompress the file into the folder.
The following files will be extracted from the compressed file:
Etrack_2754799_Fix_Installation_and_removal_Instructions.txt (these instructions)
Install_usp_Target_Sel_FIX_v3.txt
Install_usp_Target_UPD_FIX.txt
Uninstall_usp_Target_Sel_FIX.txt
Uninstall_USP_TARGET_UPD_FIX.txt
8) Launch the SQL Server Management Studio.
9) Open a Query window focused on the CA Customer database.
10) Copy the contents of the file 'Install_usp_Target_Sel_FIX_v3.txt' into the Query window.
11) Execute the contents of the Query window.
12) Open another Query window focused on the CA Customer database.
13) Copy the contents of the file 'Install_usp_Target_UPD_FIX.txt' into the new Query window.
14) Execute the contents of the new Query window.
15) As needed, close both Query windows, exit SQL Server Management Studio, and log off of the SQL Server.

On the CA Server - still logged on as the VSA:
16) Start EVAMS or the CBT of the CA Customer that was stopped in Step 3 above.

On the workstations with the CA Client installed:
17) Launch the CA Client.
18) Click on the Employees tab, or any Role Assignment sub-tab within the Application tab or any Department to verify the SQL  deadlock issue has been resolved.

To uninstall the fix files, complete the following steps.

On the workstations with the Compliance Accelerator Client installed:
1) Close any opened CA Client.

On the CA Server:
2) Log in as the Vault Service Account (VSA).
3) Stop the Enterprise Vault Accelerator Manager Service (EVAMS) or the Customer Background Task (CBT) of the CA Customer encountering the issue.

On the SQL Server hosting the CA Customer database encountering the issue:
4) Log onto the SQL Server using an account that has permission to change the contents of the CA Customer database, such as the Vault Service Account (VSA).
5) Launch the SQL Server Management Studio.
6) Open a Query window focused on the CA Customer database.
7) Copy the contents of the file 'Uninstall_usp_Target_Sel_FIX.txt' into the Query window.
8) Execute the contents of the Query window.
9) Open another Query window focused on the CA Customer database.
10) Copy the contents of the file 'Uninstall_USP_TARGET_UPD_FIX.txt' into the new Query window.
11) Execute the contents of the new Query window.
12) As needed, close both Query windows, exit SQL Server Management Studio, and log off of the SQL Server.

On the CA Server - still logged on as the VSA:
13) Start EVAMS or the CBT of the CA Customer that was stopped in Step 3 above.

On the workstations with the CA Client installed:
14) Launch the CA Client.
15) Click on the Employees tab, or any Role Assignment sub-tab within the Application tab or any Department to verify the SQL  deadlock issue has returned.

Workaround:

A workaround exists to this issue that allows the original stored procedures to remain in place.  The workaround overview is -

I. Set the Active Directory Synchronization option to disabled for the CA Customer.

II. Manually run the ADSynchroniser process through the use of a batch file, either as needed or in a scheduled job on the CA server.

To implement the workaround -

On a workstation with the CA Client installed

  1. Logon using an account that has permissions to modify configuration settings, such as the Vault Service Account (VSA).
  2. Launch the CA Client.
  3. Click on the Configuration tab.
  4. Click on the Settings sub-tab.
  5. Expand the Profile Synchronization section.
  6. Locate the configuration setting of Synchronize profiles.
  7. Click the check mark in the Value column of this setting to remove the check mark.
  8. Click the Save button at the bottom of the page.
  9. Click the OK button to acknowledge the requirement to restart the Customer Background Tasks in the pop-up dialog box that will be displayed.
  10. Close the CA Client.
  11. Log off of the workstation as appropriate.

On the CA Server

  1. Logon as the VSA.
  2. Launch the Services MMC snap-in.
  3. Restart the Enterprise Vault Accelerator Manager Service (EVAMS).  This will stop the automatic synchronization with Active Directory or Lotus Domino Directory.
  4. Create a folder to contain the batch file that will be run as part of a scheduled job (i.e., C:\BatchJobs).
  5. Create a text file named "ManualADSyncBatchFile.bat" in the folder created in the previous step.
  6. Edit the file "ManualADSyncBatchFile.bat" using any text editor, such as NotePad, saving and closing the file when done.
    1. Enter the single line as shown but without the 'i.':
      1. "C:\Program Files\Enterprise Vault Business Accelerator\ADSynchroniser.exe" 2 1 a
    2. Modify the first value in quotes (which is "C:\Program Files\Enterprise Vault Business Accelerator\ADSynchroniser.exe") to be correct the path to the ADSynchroniser.exe file.  This file will be in the CA installation folder.  The default installation folder for CA on 32-bit Windows Server is "C:\Program Files\Enterprise Vault Business Accelerator".  On a 64-bit Windows Server, the default installation folder is "C:\Program Files x86\Enterprise Vault Business Accelerator".  Be sure to use the proper drive letter and path to the file or the batch file will not work.
    3. Modify the second value (which in Step 6-a-i is "2"), is the CustomerID of the CA Customer, which is stored in CA configuration database.
      1. To get this CustomerID -
        1. Log onto the SQL Server hosting the CA Customer database using an account that has permission to at least read the contents of the CA configuration database.
        2. Launch SQL Server Management Studio.
        3. Connect to the SQL instance hosting the CA configuration database.
        4. Expand Databases
        5. Right click on the CA configuration database.
        6. Select the New Query option
        7. In the new query window, execute the following command without the "I." -
          1. SELECT * FROM tblCustomer WHERE ATStatus = 21
        8. From the result set, note the CustomerID associated with the CA customer.
        9. Replace "2" in the batch file line above with the CustomerID noted in the previous step.
    4. Modify the third value (which in Step 6-a-i is "1"), is the ServerID of the CA Server, which is also stored in CA configuration DB.
      1. To get this ServerID -
        1. In the same query window as in Step 6-c-i-F, execute the following command without the "I." - 
          1. SELECT * FROM tblServer
        2. From the result set, note the ServerID of the CA Server hosting the CA Customer.
        3. Replace "1" in the batch file line above with the ServerID noted in the previous step.
    5. The forth value (which in Step 6-a-i is "a") tells ADSynchroniser.exe to synchronize all AD objects (Domain, groups and employees).  This value stays as shown.
  7. Create a scheduled job to run this batch file after normal working hours using any scheduling application installed on the CA server. 

 


Attachments

Etrack 2754799 Hotfix installation and removal files
Etrack_2754799_SQL_Deadlock_Refreshing_Employees_or_Role_Assignments.zip (10 kBytes)

Supplemental Materials

SourceETrack
Value2754799
Description

CA: Getting SQL deadlock almost every time when clicking on: Refresh Employees Click on Employees tab Role Assignments



Article URL http://www.symantec.com/docs/TECH194758


Terms of use for this information are found in Legal Notices