Some weird locking

cnicola · 2007-07-18

Hi guys,

We are using EDI from Lanham and at times we may be creating 1000-2000 Sales Orders during 1 push of a button. We do get people locked up on the sales side (entering other orders or changing some) but let's say I can understand that.
However it is locking pretty much everyone else (including say people on the purchase side who are trying to enter a PO). I used an example from system management views on SQL 2005 and it seems they get locked on No Series Line and Document Dimension.
I do not understand why we get a table lock on No. Series Line so that no one else can get a number (that would happen on C/Side but I would not expect it on SQL with row locking). I understand it could also be bad coding but largely I am using same code as on another SQL client and there I do not have that problem.
And I definitely do not understand the document dimension issue since we are not using any dimensions whatsoever ](*,) . We had some setup but I removed them and then deleted the dimensions themselves. I thought maybe we have some Global Dimension Codes left over on master records but then it would eventually show up in the table or error out. But Document Dimension is empty.
Any ideas would be more than welcome.

ara3n · 2007-07-18

Hello
For one of my clients we do the same thing ~ 1000 get loaded into their system through periodic activity that is run from scheduler.

The way I've written the routine is that it COMMITS after every sales order. That way it does not lock any tables for long period.

I suggest you look at Lanham EDI and see if it is possible to put COMMITS.

kriki · 2007-07-18

Try to put File=>Database=>Alter=>Tab Advanced => "Always Rowlock" to true.
It might help. But probably, you will need to go for the solution of ara3n.

cnicola · 2007-07-18

Hi guys,

ara3n 's suggestion is not workable. If you have one Macy's Customer PO No. that will create 1000 store orders (i.e. sales orders) I cannot commit after each since it should be either all or nothing (since it is only one EDI transmission).
Kriki I did have Always rowlock on to start with and did not see any difference. Actually I thought it was even worse so put it back to false. Now I do not know whether you need to restart the server for that to take effect since in that case it was all in vain.
But I am still confused why it would lock on document dimension since we are not using dimensions at all.

ara3n · 2007-07-18

The code for dimension management is still running , I would search for locktable code. Turn on the code coverage and filter for locktable and see why dimension table gets locked.

davmac1 · 2007-07-19

No Series does not seem to be suited towards mass creation of orders.
Have you thought about altering the number series logic so you are not updating the same row 1000 times?
Also, since this is a Lanham product, have they had any response to your problem?

kriki · 2007-07-19

cnicola wrote:

Hi guys,

ara3n 's suggestion is not workable. If you have one Macy's Customer PO No. that will create 1000 store orders (i.e. sales orders) I cannot commit after each since it should be either all or nothing (since it is only one EDI transmission).
Kriki I did have Always rowlock on to start with and did not see any difference. Actually I thought it was even worse so put it back to false. Now I do not know whether you need to restart the server for that to take effect since in that case it was all in vain.
But I am still confused why it would lock on document dimension since we are not using dimensions at all.

And how about reading the EDI-transmission, store it in a new table (or multiple) and then start transferring it sales orders with COMMIT's in between and flags in the new table that tells you if you moved it or not?

ara3n · 2007-07-19

Lanham EDI has two staging table. So that's why I suggested it, but I guess he wants all or none.

cnicola · 2007-07-24

Hi,

Ara3n, Lanham has 2 "staging" tables. But from one record in the header "staging table" you may have to create 1600 orders that represent one PO from the retail chains' perspective. So you should always have either all 1600 orders in the system or none of them. Hence the "all or nothing" ](*,)
To give you a full picture: i was getting locked on Document Dimension, then Reservation Entry and then Sales Header Archive table (even though I do not use any of those things). I mean No. Series Line is a problem too but at least that I know I use.
For now I commented out all the code for the above functionalities and things have improved a lot \:D/ But that will not work if they start using any of those 3 things :evil:

cnicola · 2007-07-24

Just quickly reading jondoba's link from Clustered Indexes topic (http://msdn2.microsoft.com/en-us/library/ms190639.aspx ) I would draw the following conclusions about Sales Line table (and header) (as an example):

1. Primary key: Document type,Document No.Document Line No. should be Document No., Document Type, Document Line No. or Document No., Document Line No.,Document Type
2. You might not want a clustered index on Sales line and Header if you are creating a lot of orders all the time (like my case) since it needs to keep them in order.

Am I understanding that wrongly?

ara3n · 2007-07-24

1. I would not change the existing key. Create a new key "Document No.",Document type and make that your clustered key.

2. That's not what they say in the link.
Columns that under go frequent changes, (meaning existing records that change the data, one of which is part of clustered index). This cause the a lot of restructuring on the index to put the record in correct position.

In your case you are inserted records in sequential order (the cluster index will not be your bottleneck). And high volume means millions of transactions.

For now I commented out all the code for the above functionalities and things have improved a lot Dancing But that will not work if they start using any of those 3 things Evil or Very Mad

Make is a setup and run the code if the setupfield is checked.

cnicola · 2007-07-27

Hi ara3n,

1. Very nice idea. But do you think will make a difference in performance?
2. Well high volume in Navision with the issues it has may not need to be millions :-)

As for the setup field you are right. I would have normally done that but the client was live, everyone was screaming so just caved :oops:
I will go back this weekend and change it O:)

stryk · 2007-07-28

Hi!

Changing - Optimizing! - the Clustered Index would help a lot, it will increase transaction speed and reduce blocking problems!

It is absolutely correct, that the most selective fields should be on the "left-most" side of the index. As since NAV 4.00 you could use the properties "SQL Index" and "Clustered" you could implement these changes from C/SIDE.

Here http://www.stryk.info/PFG_Page25.pdf you could find an excerpt from my "NAV/SQL Performance Field Guide" (EXCLUSIVELY for my customers 8) ) where this issue is explained.

Cheers,
Jörg

Howdy, Stranger!

Quick Links

Categories

Some weird locking

Comments