Table locking on approval with Workflow Step Instance table

asmilie2b3

Since moving to NAV 2018 (R1) and now using the newer Workflows for approvals, we have a lot of locking that occurs. For example when users send for approval from the sales order, they receive an error due to the Workflow Step Instance table being locked. It is intermittent. I have looked through the code around the workflows and can see it is very lengthy the amount of code that has to fire to create the approval. A total of 22289 lines of code altogether. I can't see any simple way to resolve the problem. I wonder if anyone has encountered this, and would have any advice?

pc9988

I have encountered same issue and been bothered during past few months, however it is getting closed to be resolved.

1. Get the most recent Cumulative Update, update Codeunit 1501,1502,1509,1510,1511 and 1535. There was a major update in CU13 (26893) and some other minor updates relative with workflow. Basically the most updated CU avoid using Query but use traditional SETRANGE to find records. New code also avoid unnecessary table locks caused by DELETEALL.
The above update shall minimize Workflow Step Instance table lock issue. But then the another Table 1506 Workflow Table Relation Value table lock may happen.

2. NAV out of box Workflow Template will default setup Table 1505 (Workflow - Table Relation) content. For example, Table 36 sales header has Table Relation with table 454, 37, 8000. I have deleted the table relation with Table 37 and 8000 since those relation are not really used in my workflows but just result a lot of records in table 1506 for each approval request. Reduce the number of unnecessary records help a little bit.

However, workflow relative table lock or deadlock still happen occasionally. I plan to do two more modifications:
To-do 1: Modify table 1506's UpdateRelationValue function, to avoid unnecessary record MODIFY.
To-do 2: Get rid of using Query 1501 in Table 1504 SetTableRelationValues function.

Hope the above information helps.

pc9988

I have encountered same issue and been bothered during past few months, however it is getting closed to be resolved.

1. Get the most recent Cumulative Update, update Codeunit 1501,1502,1509,1510,1511 and 1535. There was a major update in CU13 (26893) and some other minor updates relative with workflow. Basically the most updated CU avoid using Query but use traditional SETRANGE to find records. New code also avoid unnecessary table locks caused by DELETEALL.
The above update shall minimize Workflow Step Instance table lock issue. But then the another Table 1506 Workflow Table Relation Value table lock may happen.

2. NAV out of box Workflow Template will default setup Table 1505 (Workflow - Table Relation) content. For example, Table 36 sales header has Table Relation with table 454, 37, 8000. I have deleted the table relation with Table 37 and 8000 since those relation are not really used in my workflows but just result a lot of records in table 1506 for each approval request. Reduce the number of unnecessary records help a little bit.

However, workflow relative table lock or deadlock still happen occasionally. I plan to do two more modifications:
To-do 1: Modify table 1506's UpdateRelationValue function, to avoid unnecessary record MODIFY.
To-do 2: Get rid of using Query 1501 in Table 1504 SetTableRelationValues function.

Hope the above information helps.

asmilie2b3

pc9988

Thank you so much for the detailed information. And with a CU that alleviates it confirms I think I was not alone. I will look into that and the other things you have mentioned, and will post back my results.

Many thanks!