Cleanup of cases (and tasks) via an IVY Process causes to Database Deadlocks

Dieter Staudt — Thu, 08 Jun 2017 07:34:33 -0400

I refer to the question # “478 - How to cleanup old Cases / Tasks?” with my questions:

“Triggers in the XpertIvySystemDatabase” Among other triggers there is a trigger when deleting cases and when deleting tasks. Because the installing and setup of IVY was done many years ago by a former colleague so my first question is: Were all triggers delivered by AXONIVY?
“Cleanup of old Cases and Tasks” We use an timer triggered Ivy Process for the Cleanup with the sql-statement for deleting finished cases: “DELETE FROM IWA_Case WHERE DATEDIFF(dd, EndTimestamp, GETDATE()) > in.TimestampString.

The Timestampstring contains the number of days the finished cases will be stored.

For example: “DELETE FROM IWA_Case WHERE DATEDIFF(dd, EndTimestamp, GETDATE()) > 7” will delete all finished cases older than 7 days.

As mentioned in my first question, all Delete-Statements in the CASE-DELETE-Trigger and all the nested Delete-Statements in the TASK-DELETE-Trigger will be executed automatically.

This causes to Database-Deadlocks – see one Message of our Nagios tool:

5 deadlocks detected on LUXDB004

spid status loginame hostname blk open_tran dbname cmd waittype waittime last_batch SQLStatement 
66 suspended sa_ivy LUXAS004 0 2 IvyDb DELETE 0044 6 Jun 6 2017 08:58:20:460PM DELETE IWA_TaskData FROM IWA_TaskData, deleted WHERE IWA_TaskData.TaskId = deleted.TaskId \n \n
66 suspended LUXAS004 0 0 IvyDb DELETE 00bb 76873 Jun 6 2017 08:58:20:460PM DELETE IWA_TaskData FROM IWA_TaskData, deleted WHERE IWA_TaskData.TaskId = deleted.TaskId \n \n
66 suspended LUXAS004 0 0 IvyDb DELETE 00bb 71822 Jun 6 2017 08:58:20:460PM DELETE IWA_TaskData FROM IWA_TaskData, deleted WHERE IWA_TaskData.TaskId = deleted.TaskId \n \n
66 suspended LUXAS004 0 0 IvyDb DELETE 00bb 49495 Jun 6 2017 08:58:20:460PM DELETE IWA_TaskData FROM IWA_TaskData, deleted WHERE IWA_TaskData.TaskId = deleted.TaskId \n \n
66 suspended LUXAS004 0 0 IvyDb DELETE 00bb 55455 Jun 6 2017 08:58:20:460PM DELETE IWA_TaskData FROM IWA_TaskData, deleted WHERE IWA_TaskData.TaskId = deleted.TaskId \n \n

What is wrong? What must we do to avoid this deadlocks?

Thanks in advance. Dieter

Questions Tagged With ivy

Cleanup of cases (and tasks) via an IVY Process causes to Database Deadlocks