What if i remove Locking From Sales Table

ashu_gargs

While working on the native database and doing a batch post. Front End users can not continue working on sales documents as Sales tables are locked in the begining of the Sales Process and released after posting.
Can i remove the lock from the posting routine..... and some flag + code can be added in the client so that user can not modify the record that is currently being sent for post :idea: .... I hope it should not cause any data Inconsistency...

Kowa

Do not touch ANY LOCKTABLE in a posting routine. Tables have to be locked in the right order to prevent deadlocks.
Only one user can write to table at a time, even without LOCKTABLE. If you do not code it, the Server does the locking by itself, then as late as possible (optimistic concurrency).

kriki

The only thing you can do here is limiting the time everything is blocked. Some hints.

-Fast clients
-Fast network
-Commit cache on service and enough DB-cache (and enough memory to keep the DB-cache completely in memory)
-few (and small) indexes/SIFT-fields on the tables involved
-enough object-cache on the client, so it does not always has to get the objects from the server

Dodga

there are differences in locking if you use cside or sql, the standard locking works on cside but is horrible if you use sql server, there you really need to optimize it by hand

kriki

In a course about SQL-server performance with Navision, I heard SQL-server can be faster than a Navision DB. (If that is true, I don't know, because I never tried). But what is definitly true, it can become a lot faster if you don't maintain a lot of useless indexes/SIFT-fields-levels in SQL. Even if you set a SETCURRENTKEY, SQL decides itself wich index to use. The SETCURRENTKEY is only to sort the data for Navision.

In short, in the entry-tables, you can toggle of the MaintainSQLIndex and SIFTIndex for a lot of keys and with that writing to the DB becomes a lot faster.

ashu_gargs

My problem is not with the performance.. The problems is while running the batch process (sales Posting) in the backend. My front end users are not able to create sales documents as Sales Header and Sales Line tables are locked. Can i just remove the locks on these two tables and let other locks be there in place. To handle the data inconsistency i can put a check on the form that the current document is currently in posting Queue and can not be changed.

kine

Solution is - change the batch to commit after each posted document and sleep for some time. Between that all others can do their job and there will be no starvation...

JanV

Another possibility would be to put the job into the "job scheduler" and then just let the job run every morning at 5 am, or any other time when nobody is working.

Kowa

ashu_gargs wrote:

Can i just remove the locks on these two tables and let other locks be there in place.

If there are batch postings the no. series line table is also locked by the batch poster, because the "Last no. used" is always written to the table, so no new document nos. are available for the users who want to create new orders.
Put in a commit and a sleep statement in the batch routine as kine has suggested, that way the batch postings will take longer but other users can at least get a new document no. to create the new order. If necessary, the order can then be posted while the batch is sleeping ( a delay of a few seconds is possible, but it won't take minutes like before).
Still , all users might frequently receive error messages like this one (ENU language version)
The xyz table cannot be locked or changed because it is already locked by the user with User ID xyz.
Wait until the user is finished and then try again.

kriki

The problems is while running the batch process (sales Posting) in the backend.

Strange, because in Codeunit 80, when the order is posted, there is a COMMIT.
The first time you post an order, it can be a little slow, but after that it is fast because all the objects are in the object-cache.
So I wonder a few things:
1) how many lines does an order contain? If it contains a few hundreds, it will be slow and the only thing you can do is posting when no one else works. If it contains less, it should be fast.
2) From the client/NAS you are posting, did you put some object-cache (if you take double the amount Navision uses standard, it should be enough (at least if you didn't change a lot, like calling other objects in C80,C12,C22,...))
3) Have you enabled the COMMIT-cach on the DB-server?
4) did you remove the COMMIT in C80?
5) if you have changed the code, are you sure it is performant, like using some filters with a wrong SETCURRENTKEY?

kine

Kriki, we had problems with customer, which was posting invoices (100 and more) in one batch, each invoice with 10..200 lines (dimensions etc.), each document was posted aprox. in 5 sec., but if you calc 5sec*100doc = 8.3min - through this time the tables are locked, because all others are waiting. The commit is not working, because there is too small time hole in which others can work (milliseconds). Much easier is to modify the batch which is calling posting for each invoice, after that do commit to be sure, that nothing is locked (we are writing some statistics about time per doc in the batch etc...), sleep, and than post next doc... it is verified :-)

Of course optimizing HW is better, but if you still have 1sec/doc and you are posting 200 docs, you still have 3 min per batch, and this is too long for users, which need response in max 20 sec... (magic limit)

ashu_gargs

In fact the user base that I need to take care of is around 1000-3000 users. We have modified some code in Sales tables related to the vertical functionality but Commit is still there. SetCurrentkeys are there as per the base code.
I am following the Queue approach for making the posting SERIALIZED so that there are no deadlocks. If everything is Queued chances are that at some point of time Queue may have around 2000 postings.
I need to take care of this approach for SQL as well as Native server. For SQL it will be a little slow but will not affect the front end users but for Native I am sure it won't allow users to create new Orders and other documents if sales line is locked.
If I remove Locking on Sales Line from CU 80.. How it's going to lead to DATA INCONSISTENCY????