[Solved] Record locked by another user in MS Dynamics NAV
Views (111)
Like
Share
ReportHello readers, Most of the client face record locking issues, if they do heavy transactions concurrently in multi user environment. After NAV 2013 R2, Microsoft team has done many enhancements so that the user do not have to face this problem. You can read lots of blog regarding this enhancement. There is a field called "Use Legacy G/L Entry Locking" in general ledger setup. If this is marked false, it locks the G/L entry table, only when the g/l entry creation starts. If this is marked true, it starts locking the ledger entry tables very early during the posting process of transaction. And it can only be marked false if the automatic cost posting in Inventory setup is marked false.
If Automatic cost posting is true, it will create the additional g/l entries that makes the inventory reflected in g/l account, balance with cost shown in value entries. And this automatic cost posting might take a longer time that makes the deadlock if at the same time another user tries to post the inventory transaction, so if your client wants the inventory account balance with the cost of item in value entries when the invoice is posted, then they are now might be going through a big nightmare during the days.
You can read about the locking in Microsoft Dynamics NAV in MSDN. Today we, are going to discuss on how we solved this locking nightmare that occurred in one of our client.
The client that has 150 concurrent users, 11 NAV Service Instances, 32 GB of RAM allocated in NAVServer, 32 GB RAM in SQL Server but suddenly got locking issues when they started the global service camp. They started creating lots of service orders, transfer orders, invoices within minutes that NAV started giving the locking issues.This client has 50 service locations and from each location they started the global service camp. There was huge line of vehicle servicing customers and service engineers was so tired of having locking issues because of this they were not performing well on service camp.
Even SQL indexing was also scheduled every weekend, but it was not solving the Item ledger entry deadlock and service line locking issues. Finally, it was decided to write a routine in NAV that creates and post the service order randomly in test server and watch the deadlock in sql profiler. This routine then was executed from 5 different session. And you can guess, the result was same, the deadlock/locking issue.
For each service order posting, it was reserving the memory for lots of time and looking at the profiler it was not releasing the locked tables. There was the customization routine that calls the service posting batch job from another codeunit that does few more task before and after posting the service order. And at the end of this codeunit execution, there was no COMMIT. COMMIT; not only ends the write transaction but also frees the reserved variable memories and locked tables.
After the COMMIT is kept at very end of the codeunit execution, the problem got solved. Use COMMIT wisely. For reference you can follow the patterns, like in Codeunit 80,90,13 etc..
Happy Coding :)
If Automatic cost posting is true, it will create the additional g/l entries that makes the inventory reflected in g/l account, balance with cost shown in value entries. And this automatic cost posting might take a longer time that makes the deadlock if at the same time another user tries to post the inventory transaction, so if your client wants the inventory account balance with the cost of item in value entries when the invoice is posted, then they are now might be going through a big nightmare during the days.
You can read about the locking in Microsoft Dynamics NAV in MSDN. Today we, are going to discuss on how we solved this locking nightmare that occurred in one of our client.
The client that has 150 concurrent users, 11 NAV Service Instances, 32 GB of RAM allocated in NAVServer, 32 GB RAM in SQL Server but suddenly got locking issues when they started the global service camp. They started creating lots of service orders, transfer orders, invoices within minutes that NAV started giving the locking issues.This client has 50 service locations and from each location they started the global service camp. There was huge line of vehicle servicing customers and service engineers was so tired of having locking issues because of this they were not performing well on service camp.
Even SQL indexing was also scheduled every weekend, but it was not solving the Item ledger entry deadlock and service line locking issues. Finally, it was decided to write a routine in NAV that creates and post the service order randomly in test server and watch the deadlock in sql profiler. This routine then was executed from 5 different session. And you can guess, the result was same, the deadlock/locking issue.
For each service order posting, it was reserving the memory for lots of time and looking at the profiler it was not releasing the locked tables. There was the customization routine that calls the service posting batch job from another codeunit that does few more task before and after posting the service order. And at the end of this codeunit execution, there was no COMMIT. COMMIT; not only ends the write transaction but also frees the reserved variable memories and locked tables.
After the COMMIT is kept at very end of the codeunit execution, the problem got solved. Use COMMIT wisely. For reference you can follow the patterns, like in Codeunit 80,90,13 etc..
Happy Coding :)
This was originally posted here.
*This post is locked for comments