Using Autoincrement in Warehouse Entry

Mauddib

Navision 3.60 code running on Navision 5 clients and SQL 2005

We have had performance issues and deadlocks in one company on our system. This company posts only Orders with warehousing. We found deadlocks happening in a few scenarios.

We have changed, in our test system, the warehouse entry table to autoincrement. We have stress tested this and all looks good. There was one dead lock that showed up but not often and only when we stress tested our system heavily, where upon we got a strange deadlock (while posting 300 warehouse shipments in 2 clients) between... strangely.... Warehouse Request and Warehouse Request table :-|

However, before taking this live any advice on other things that need to be checked would be much appreciated.

kine

Warehouse Request and Warehouse Request

If the deadlock is on same table, the deadlock is on the page level (it means when updating e.g. index) - if there will be just clustered index on the table, the deadlock will not be there, but because secondary indexes exists, you can have this sort of deadlocks on the index pages, because the page of index which is updated depends on the data in the record...

Marije_Brummel

kine wrote:

Warehouse Request and Warehouse Request

If the deadlock is on same table, the deadlock is on the page level (it means when updating e.g. index) - if there will be just clustered index on the table, the deadlock will not be there, but because secondary indexes exists, you can have this sort of deadlocks on the index pages, because the page of index which is updated depends on the data in the record...

Are you sure of this? :-s

nunomaia

If you are using autoincrement who are you maintaining Warehouse register?

ara3n

nunomaia wrote:

If you are using autoincrement who are you maintaining Warehouse register?

I haven't seen anybody actually use the warehouse register.

Warehouse register or any register that is not gl based should follow a different link tables instead of from and to entry no.

kine

Mark Brummel wrote:

kine wrote:

Warehouse Request and Warehouse Request

If the deadlock is on same table, the deadlock is on the page level (it means when updating e.g. index) - if there will be just clustered index on the table, the deadlock will not be there, but because secondary indexes exists, you can have this sort of deadlocks on the index pages, because the page of index which is updated depends on the data in the record...

Are you sure of this? :-s

If the process is just inserting the records, there is no another way how to get the deadlock on same table, or may be I missed something...

nunomaia

ara3n wrote:

nunomaia wrote:

If you are using autoincrement who are you maintaining Warehouse register?

I haven't seen anybody actually use the warehouse register.

Warehouse register or any register that is not gl based should follow a different link tables instead of from and to entry no.

I only had seen on report base on it. I know that isn’t a critical table. But I was only trying to focus that is a table that is be kept incorrectly. Since using autonumbers will break the concept of registers.

Mauddib

nunomaia wrote:

If you are using autoincrement who are you maintaining Warehouse register?

Sorry I did forget to mention this. codeunits 5760, 5763 and 7301 were also changed. The "Entry No." is obtained AFTER insert now not before. So the warehouse register then continues to function as normal.

DenSter

Try setting Max degree of parallelism to 1.

Mauddib

kine wrote:

If the process is just inserting the records, there is no another way how to get the deadlock on same table, or may be I missed something...

As its the warehouse request table it would be inserting AND deleting. codeunit 5771 is called during the shipping process and deletes open records and inserts released records.

Mauddib

Actually ive just been checking the SQL generated by the DELETEALL function in this codeunit.

When you do a FINDSET the SQL generated first does a SELECT TOP 1 NULL before doing a SELECT TOP 500. I guess this saves processing by checking is there anything to get before getting it.

DELETEALL however doesnt do this. I guess this means there is table locking, even if no updating is going to ocour. I see this in the information on the dead lock i have:

DELETE FROM "LOGISTICAL$Warehouse Request" WHERE (("Type"=1)) AND (("Source Type"=37)) AND (("Source Subtype"=1)) AND (("Source No_"='ORD-A-3001447')) AND (("Document Status"=0))

I wonder if changing the delete all code to this:

IF NOT WhseRqst.ISEMPTY THEN
WhseRqst.DELETEALL(TRUE);

would cause any benefits in terms of locking??

kine

Yes, this can help. Because trying to delete will lock "neighbors" record - in case of empty set the records around this set... Using IsEmpty before calling deleteall is good thing which I used more times too...

stryk

What happens on this "Warehouse Entry" table - actually it happens on all Ledger Entry tables - is simplified this:

WhseEntry.LOCKTABLE;
WhseEntry.FINDLAST;
NewEntryNo := WhseEntry."Entry No." + 1;

(or something similar)

The LOCKTABLE serializes the transaction, with the FINDLAST the UPDATE LOCKS are engaged, thus other processes are denied to access the last record in the table.
This algorithm is crucial to grant the consistent sequence of numbers, which is e.g. necessary in "G/L Entry" and all other "fiscally" relevant tables.

With "Warehouse Entry" this could be improved:
Using AutoIncrement would help to get rid of the LOCKTABLE and FINDLAST statements; SQL Server will generate the "Entry No." itself. As previously discussed, when doing this you cannot properly use the Registers anymore (who cares anyway?).

But AI alone will not solve the blocking problems, as the "Entry No." is still the Clustered Index: If a record is written to the last Page - "Entry No." is of type integer, hence recs will always be added to the end of the table - SQL Server establishes an "eXclusive" Lock on the record (ROW X) and an "Intended eXclusive" lock on the Page (PAG IX).
Thus, even though the transaction is not serialized, the PAG IX prevents other processes from getting a ROW X on this Page - still the other processes are blocked.
To avoid this, you have to change the Clustered Index, ideally using a field of type "GUID", as this will force some kind of "physical disorder" of the recs. Hence, processes will write to different pages, blocks are reduced!

We implemented this with great success! Of course, it's "tricky business" and several other things have to be regarded ... anyway, just "my two cents"