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

  1. “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?

  2. “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

asked 08.06 at 07:34

Dieter%20Staudt's gravatar image

Dieter Staudt
11336
accept rate: 0%

edited 08.06 at 07:40

Reguel%20Wermelinger's gravatar image

Reguel Werme... ♦♦
5.4k1830

What kind of database are you using as system database?

(08.06 at 11:39) Reto Weiss ♦♦ Reto%20Weiss's gravatar image

We are using for IVY the SQL-Server 2008 R2

(08.06 at 11:54) Dieter Staudt Dieter%20Staudt's gravatar image

Normally, a database detects deadlock itself and then throws an exception to one of the deadlock owners. Do you see such an deadlock error in your ivy logs too?

How many cases are deleted? If there are many it may take some time to delete them on the database. Maybe that is why Nagios thinks its a deadlock. In this case try to reduce the amount of cases that are deleted in one statement. After one statement is executed commit the transaction. Then execute the next statement. This will reduce the the amount of memory and transaction log that is necessary to delete the cases.

For example read the id's of the cases to delete. Then delete each case one after the other.

link

answered 09.06 at 05:28

Reto%20Weiss's gravatar image

Reto Weiss ♦♦
3.5k152143
accept rate: 73%

Hello Mr. Weiss. for example, for one day there will be about 7250 Case records that will be deleted. I think the main problem is the "mixed arrangement" of the sql statements in both triggers: If a case record is deleted, the trigger deletes also the associated task record BUT in the trigger for deleting tasks there is an update for the field IWA_Case.CreatorTaskId=NULL in the case record that is deleted??? Pleas can you or your team check the content of both triggers for deleting cases and tasks. Thank you. BR Dieter.

(14.06 at 04:40) Dieter Staudt Dieter%20Staudt's gravatar image

PS: In the meantime I have found a way to avoid deadlock situations: I execute the delete statment manually by using the SQL Management Studio with the Parameter TOP.

DELETE top (1000) FROM IWA_Case WHERE DATEDIFF(dd, EndTimestamp, GETDATE()) > 20 (DAYS)

To delete 1,000 cases with all associated records (by trigger function) taks about 10 Seconds.

(14.06 at 04:45) Dieter Staudt Dieter%20Staudt's gravatar image
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "Title")
  • image?![alt text](/path/img.jpg "Title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Tags:

×64
×51
×16
×9

Asked: 08.06 at 07:34

Seen: 341 times

Last updated: 14.06 at 04:45