How to skip deadlock message by creating somekind of queue

n.topalov

Hi guys,
I spend some time searching the forum for any relevant issues but did find exactly what I need.

So I am having a SQL 5.01 NAV and my client posts about 800-1000 invoices daily, which brings a lot of deadlock messages. I know that usually when having a deadlock problems I should try to optimize the problematic process but in this case I think that there are just too many invoices.

Because the users usually are scared when they receive the deadlock messages and also it is very annoying, my idea is to make some kind of queue so that when the user try to post an invoice not to get the deadlock error but just to wait until the tables are unlock and then automatically the posting is finished.

I already discussed this solution with the client and they are ok, however if you have any better ideas you are welcome.

Thanks in advance!

matttrax

Have you looked into batch posting? You can set up a job scheduler in NAV using the Application Server (or Web Services depending on your version). Just create a field on the Sales Header, "Ready to Post", and have the users check it. In the middle of the night have the scheduled job come along and post everything.

If you want closer to real time as you are describing then a queue is the way to go. I'm not sure how to tell if the table is locked, though. I don't think that that is an error message that can be trapped using the standard IF CODEUNIT.RUN THEN syntax.

rhpnt

In a std. NAV 5 there shouldn't be any deadlocks when posting anything. Are you sure we're talking dead or just simple locks?
Your idea of a queue is allready implemented in SQL server (since the first version), so there is no need to "invent" it again. If your customer really experiences deadlocks I'm pretty sure it's due to some customization.

kine

If there are deadlocks, it is nothing connected to the count of invoices, but rather wrong locking order in some process. The deadlocked processes are in most cases somehow different. This difference leads to locking order "Tab A, Tab B" in one process and "Tab B, Tab A" in second one (of course, it could be on index page level etc, not only on table level). In this case you have deadlock. If it is only about invoice count, it will be the Timeout error, not deadlock.

n.topalov

Thank u for your replays.

The solution with the night posting won't work for us since the client has also mobile devices which at the end of the day should be loaded with the available quantity of the items. I also thought of the If Codeunit.run then syntax solution but didn't try. I wanted to see if any better solution will come to me.

Yesterday I tested again the scenario with W1 db and localized BGR db and really I didn't got a deadlock but a lock error about GL entry table and that only if Automatic Post Cost is turn on. However I am pretty sure that when I discovered the problem I had received deadlock errors. Today I will make some tests again (this time with clients db) and let you know the results.

10x

kine

Automatic Post Cost on

If you have performance problems, disable the Automatic cost posting and do it in off-time in batch. Same for update analysis.

rhpnt

n.topalov wrote:

the client has also mobile devices which at the end of the day should be loaded with the available quantity of the items.

There's your troublemaker. Such adaptations with off-line terminals almost always cause trouble - speaking from experience. Check that add-on how and where it puts the transactions of those terminals.

n.topalov

Quote:
Automatic Post Cost on

If you have performance problems, disable the Automatic cost posting and do it in off-time in batch. Same for update analysis.

I cannot use this also because the client wants to have actual cost at any time since they are monitoring some reports. I know that it is not useful but also the Analysis by Dimension is updated during posting which also slow down the postings.

There's your troublemaker. Such adaptations with off-line terminals almost always cause trouble - speaking from experience. Check that add-on how and where it puts the transactions of those terminals.

There is no add-on. The transactions(invoices) are imported as text files and its done manually.

n.topalov

I made some additional tests and what I managed again to receive a deadlock error for GL entry table. It just comes to me when the automatic post cost is turned on.
I am considering to use additional table which I will lock just after the posting is started and will unlock just after the posting is finished. And every following posting will first check if it is locked (then will wait maybe I will use sleep function) and if not the posting will go.
I will check if this will work next week and let you know.