DB Deadlocks in the Identity Manager server.log: ims.TaskPersistence

Products

• CA Identity Manager
• CA Identity Governance
• CA Identity Portal

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

Identity Manager 14.x

Resolution

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

1. Login to the SQL Studio with your DBA credential
   
   
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