search cancel

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

book

Article ID: 54359

calendar_today

Updated On:

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

Description:

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.

Solution:

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 as BOLD)
    ALTER DATABASE IM_TPSTORE SET ALLOW_SNAPSHOT_ISOLATION ON
    ALTER DATABASE IM_TPSTORE SET READ_COMMITTED_SNAPSHOT ON

Environment

Release:
Component: IDMGR