DB Deadlocks In IM server.log: ims.TaskPersistence - "..deadlocked on lock resources with another process..." exception reported from "com.microsoft.sqlserver.jdbc.SQLServerException"

Products

• CA Directory
• CA Identity Manager
• CA Identity Governance
• CA Identity Portal
• CA Risk Analytics
• CA Secure Cloud SaaS - Arcot A-OK (WebFort)
• CLOUDMINDER ADVANCED AUTHENTICATION
• CA Secure Cloud SaaS - Advanced Authentication
• CA Secure Cloud SaaS - Identity Management
• CA Secure Cloud SaaS - Single Sign On
• CA Security Command Center
• CA Data Protection (DataMinder)
• CA User Activity Reporting

Issue/Introduction

The following error starts a sequence of other errors with the task persistency DB causing some Create User/Modify User tasks to remain "In progress" state.

The error will usually occur on bulk load operations such as mass explore/correlate operation on the provisioning server which will trigger etaCallBackExit (inbound operation).

Sample of error message in the server log:
ERROR [ims.TaskPersistence] DBDAOFactory.fetchObjectById - Exception getting object id:com.microsoft.sqlserver.jdbc.SQLServerException: Transaction (Process ID 66) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

** Note: The deadlock symptom was spotted only when MS SQL server is used for the TP DB. Oracle does not seem to have this issue.

Environment

Release:
Component: IDMGR

Resolution

Solution found is running a procedure on the MS SQL Server:

1. Login to the SQL Studio with sa credential's (DBA)
   
   
2. Run the following SQLs (you need to change the database name to BOLD)
   ALTER DATABASE <DatabaseName> SET ALLOW_SNAPSHOT_ISOLATION ON
   ALTER DATABASE <DatabaseName> SET READ_COMMITTED_SNAPSHOT ON