Dead Lock during batch posting

Does anyone knowns if the known issue about bad locking during batch posting invoices and post trasfer orders at the same time, has been fixed in Navision 4.00 or 4.00 sp1 version ? This problem has to do with SQL server option of Navision and during this process takes a lot of time to be completed because of locking problems. Our cutomers always complain about this. They use version 3.70b.

Unfortunately, the locking mechanisms in postings have not changed in 4.x; actually the problems are caused by LOCKTABLES on “G/L Entry” and other Ledger Entries, which are necessary to keep the posting resulte consistent. Here, the standard posting reoutines have a high deadlock potential. The general approach in fixing deadlock problems should be: Find out which processes and objects are involved! (Therefore you need e.g. the traceflags 1204, 1205 and 3605 activated on SQL Server) Find out the locking order of tables for each process! (Use the Performance Troubleshooting Tools) If possible: synchronize the locking orders! (same order within each process) Additionally: Get maximum speed in postings! PERFORMANCE! (The faster a transaction performs, the shorter is the lockng duration, the lower is the risk of deadlocks) 4.x includes in general some improvements in dealing with SQL Server, so at least a technicall upgrade is definitely an option! Hope this helps a little. Good luck!

Hi Philip, Does deadlocking corrupt the database or does it roll back neatly? We have a client who was getting lots of deadlocks in transfers and some were leaving the tracking information half done and in need of manual fixing. If you have that problem then I can give you the solution, otherwise stryk is right about making it as fast as possible. One thing that we did that made a big difference was to swich on MaintainSIFTIndex for the second key of the Value Entry table. It does increase the table size but depending on how your items are set up it can really improve read performance. Dan.

There is a commit for item tracking that is done. That commit will not roll back. You need to comment it out. It’s on the form that opens from everywhere where you need to put serial number. The form is 6510. The fuction that is called from transfer order is RegisterItemTrackingLines, which then calls writetodatabase function which has the commit. There is functino called block commit. Find the code in posting the transfer orders where they call RegisterItemTrackingLines and before calling the function add your new code and call blockcommit function first.

Hi Ahmed, That’s the fix I was talking about. So that means I’m not the only one who had this problem!

Well I found the issue while batch posting several transfer orders, and would get inconsistent data on serialized items if there is an error on one of the serial number. Ran codecoverage and looked for commits. That’s how I found the problem.