Item Journal Line Posting causing deadlocked on item ledger entry table After Migrating from 2009R2 to 2013R2

Business Central/NAV Technical Forum
1

Hi All,

We Recently migrated from 2009 R2 to 2013 R2.when we post item journal line we are facing different error’s As Below.

  1. The activity was deadlocked with another user who was modifying the item ledger entry table. please retry the activity.

  2. The operation could not complete because a record in the item journal line table was locked by another user. please retry the activity.

  3. The following SQL error was unexpected. the server failed to resume the transaction. Desc:66000008b8

We have more than 80 user’s trying to post this simultaneously from different batches,

Earlier we didn’t face this issue when we are using 2009 R2.

From our investigation we noticed error is causing break at code unit 23

Below i compared the code unit 23 from 2009 R2 and 2013R2

Below highlighted red arrows is one of the place where we got error when we turn on debugger.

6082.Codeunit23 Compare.PNG
6082.Codeunit23 Compare.PNG1174×440 23.4 KB

Don’t know the exact reason but we are getting different error’s…

Please Help,

Thanks in Advance,

Prasad

2

For More Information,

We are trying to post Huge Number of transactions as month end loading and unloading happening.

Simultaneous we are Posting Item Re-class journal and Sales Invoice also,

We are using sql server 2014 Enterprise.

Thanks and regards

Prasad

3

There is really not any differences in the code. The RECORDLEVELLOCKING command is not needed in 2013 since there’s no chance of being the native DB. This command is used to check which DB is being used. The LOCKTABLE to “Ledger Entry Dimension” is not needed due to the redesign of dimensions.

4

Hi Babrown,

Many Thanks for the reply,So you mean there is no resolution for this Problem.

When two user’s are posting Item reclass journal simultaneously Using different Batch, it will lock the the ILE table And won’t allow second user to post untill first user finish posting,Is it…?

5

I have not said that. I am just stating why the code has changed.

6

Ok,

Babrown thank you for your explanation regarding code changes in 2013R2 from 2009 .

Have understood our problem,We are facing ILE locking issue when two user’s trying to post Item reclass journal at same time,

If you have any Idea, Please suggest a solution to get rid of this locking issue.

7

Are we talking about “Deadlocking” or “Blocking (excessive locking)” here? Your post is using both terms (which are not the same thing).

8

“The operation could not complete because a record in the item journal line table was locked by another user. please retry the activity.”

This is the error we are getting when 2 user’s try to post item reclass journal at the same time.

Is there any way to get rid of this,Please suggest.

9

Multiple users posting at the same time will encounter locking. That’s just a basic fact and there is nothing you can do to totally get rid of it. However that does not mean it will always rise to the level of lock timeout errors such as you are receiving. By improving the performance of your posting process and (where practical) reducing posting size can limit the impact of this locking.

If you are asking me for the magic “change this and this” solution, I’m sorry but I don’t have that for you.

10

Hi Babrown,

Thank you for your Reply.

Best Regards,

Prasad.