Notifications
Announcements
No record found.
Hi,
Why this G\L Locking error coming frequently while posting.
what should be done to get rid of this??
SQL Indexing is the solution?? Can I get any indexing queries?
Abdul.
*This post is locked for comments
Hey Abdul,
Check out this link it will help you
blogs.msdn.microsoft.com/.../gl-entry-table-locking-redesign-in-microsoft-dynamics-nav-2013
Which version you are using Mr.Abdul?
Indexing would most likely not help you in this case (however it would be very good to fully re-index your database at least on a weekly period, and update statistics daily). If you are not yet doing this, please consider setting up a scheduled process
technet.microsoft.com/.../ms187874(v=sql.105).aspx
Generally, locking is done by the system when one user or batch job is writing the data so that the other user would not be able to interfere with the other posting and corrupt the data or lead to data inconsistency. Or, the reader does not conflict with transactions that modify the same data. If one wants to ensure that he is reading the latest data from a table, he must lock the table before reading it.
The locking might appear on different levels, e.g. on a record level (a certain record is locked but the table is available), or set of records, or the whole table can be locked. The locks mainly depend on the processes that exist in your system. E.g., when you run the batch job for item G/L cost correction, it locks G/L so other users need to wait with postings. G/L entry table is locked when users post inventory cost to G/L.
There are different settings you can perform for SQL database to reduce the level of locks, however do not go too far otherwise users might face "dirty reads" or non-repeatable reads. I would recommend to set Default Isolation Level to REPEATABLEREAD and increase a little Lock Timeout.
There is a very good PDF attached in the end of this post, please read it for educational purposes to learn more about NAV performance setup:
blogs.msdn.microsoft.com/.../microsoft-dynamics-navsql-server-configuration-recommendations
You can also re-design the processes in the system, so that users would conflict less with each other. E.g., switch off "posting to G/L" for items transactions and run a batch job at night instead.
Hi AMol,
We are using NAV 2013 R2,
MS SQL Server 2012.
Regards,
Amol.
Hi Alexander,
Thank you for such a detailed explanation.
What I am thinking is NAV is such big ERP system from mircrosoft, still these kinds of issues are not solved inside the app??.
Got to learn lot about SQL indexing before it can be executed, I am afraid any mistakes I commit affects my DB.
"I would recommend to set Default Isolation Level to REPEATABLEREAD" , can you please elaborate on your quoted comment?
Hi Abdul,
It is true that, NAV is very stable solution from MS, however please understand that, it is build on very old C/AL language from 1990s...
MS is working very hard to overcome table locking issue, however it may not be resolved due to language limitation.
If you have voluminous transactions, best way to overcome the challenges is to use Background posting using job queue. This way you can reduce lot of table locking error.
Also, plan periodic re-indexing on the key important ledger entries tables.
Also, try to fine tune the customisations, which might be affecting performance very badly.
Hi Vishal,
Sorry for late. Thank you.
I may have to check on the indexing and background posting.
Re-indexing/rebuilding statistics of the table will not affect our DB or NAV right? I can confidently do that??
I am now in discussion with our partner about this.
Thanks again.
Under review
Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.
As AI tools become more common, we’re introducing a Responsible AI Use…
We are honored to recognize Neeraj Kumar as our Community Spotlight honoree for…
These are the community rock stars!
Stay up to date on forum activity by subscribing.
