Why NAV Table Locking behaves differently in two different cases?

krrish0909

Hi Experts,

I have one scenario in which I'm trying to understand the NAV behavior for table locking.

I have created 2 codeunits to insert gen journal lines in to 2 different batches "Batch 1" & "Batch 2" using two different sessions (NAV 2009 classic with set of 40K records per batch)

Case 1 :- I can able to insert records in to 2 batches ("Batch 1" & Batch 2") simultaneously using two different sessions without any lock errors

Case 2 :- I have already deleted records in "Batch 2". Now I'm trying to delete records in "Batch 1" and try to insert records in "Batch 2" simultaneously using two different sessions, now i'm facing lock errors (can't insert in to "Batch 2" as "Batch 1" is in process of deleting the records. Error: General Journal Line table locked by another user wait until ......)

can somebody explain NAV table locking behavior in above 2 cases and why "case 2" has different locking behavior than "case 1"?

Thanks in advance

Regards,
Krrish

krzychub83

It's not NAV, but SQL. Short story is that UPDATE is making record level lock, while DELETE locks whole table till the end of transaction. There are three solutions to go around this behave. You can try to commit sooner, or between sub-records in your process (if you for example delete multiple documents, then for each document in the set delete, commit, wait for few milliseconds if an automation). If this is multiple users working on the same table in the same time, then you can mark records to be deleted (by a new column dedicated for that purpose), filter out all marked records on users forms, reports, etc, so they won't see them, finally delete records with an automation task when out of rush hours on database. Third solution is to change record level policy on the SQL server, but this can come with lots of issues if you not familiar with it.

krrish0909

Thanks for the quick response. The above scenario i have created is for my understanding of table locking but actual scenario is something like this....

My customer has 3rd party integration with NAV (NAV2013), through webservice "gen jnl lines" (payments) will be created in to NAV and Job Queue has been configured to post the gen jnl lines with frequency of every 1 min (24 X 7). Parallelly in NAV, users do their daily business operations (includes Invoices, Journals Posting etc). Now JQ facing locking errors with "Gen Journal" table coz of some manual bulk transactions posting...but this will be routine process for my customer.

I would be happy if any suggestions can be made on how to handle JQ's better in this situation.

krzychub83

Sorry for posting so late, I didn't get any notification email that you replied. Basically there are as many solutions as developers.

Transactions deadlocks are normal. Whatever you will do they will happen. The question is how frequently they are showing up and how much of an issue they are for the user.

If the JQ is mostly affected, then I am assuming that JQ will try to post those journals again after a minute, and then again after a minute, and then again after a minute. It should carry on until it will manage to post it. Is it? Those are payment journals, so do users needs to see them posted straight away or is there some delay accepted? You need to put some security measures in place just in case it won't post for a day, etc..

The 'large bulk transaction' is what I would focus my attention at, as this is the source of the problem. Can it be split in smaller transactions? Can it be moved outside of database rush hours. For example it is common thing to generate invoices during the day, accept them for posting by somebody allowed to do it, and finally post them by JQ during the night.

I would advice you to use SQL profiling for:
- finding out which table are actually affected by deadlock. It might be something totally different than you expected, like No. Series Lines. Knowing what is actually deadlocking may help in amending the code to prevent it. Sometimes it may be key page split, that is taking place because of large bulk transaction. That split can stop anything else from post.
- checking how posting is executed for different docs. Wrong keys, filters, etc, might affect how much SQL server is stressed to execute the post. You want to remove any table scan instances, etc. It may sound silly, but if you multiply those millisecond by record count in your bulk transaction those numbers do matter. Just as an example I had an invoice batch, which at the time I inherited it from different developer was taking around 3 hours to post. After few code changes I squeezed it to 40 minutes. So it is worth investigating.

Deadlocks are not an easy subject, but worth looking into.

parm

Hi,
I think the difference in those cases is command buffer.
If you have only inserts on the same record, NAV will not issue those commands until has to read or update or delete that record or the commit is issued.
In case 1 both sessions are buffering the insertions and probably when they never lock each other.
In case 2 the first operation is a delete and this lock the table.

I agree with previous answer to look at Sql Profiller to know for sure whats happening.

Regards and good luck,

parm

bbrown

I see the term deadlock used above, but I think you are really talking about blocking. Don't confuse the two. They are very different issues.

A couple suggestions:

1. Investigate the other processes that are holding the locks. Can they be tuned to improve performance and thus reduce the lock durations? Locking is a fact of life. The problems come from hold locks to long or locking too much.

2. You might consider setting lock timeout to FALSE on the JQ processes. I will sometimes do this if it fits the situation. The process will then wait until the lock it released. Not a "fits all" solution. You have to consider your specific situation.

krrish0909

Thanks all for your response & suggestions

As suggested by krzychub83 & others, I have tried SQL Profiling and found that CLE,DCLE and some times GLE are the one having locking issues. Unfortunately coz of their business nature we can't set the delay more than 1 min as they wanted to post the JQ's straight away. Since there are some customization involved in this, I will look in to the option for optimization to improve the performance.

Thanks for your inputs