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

Document ID : KB000054359
Last Modified Date : 14/02/2018
Show Technical Document Details

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