about NAV LOCK TABLE ISSUE

rxqrxqrxqrxqrxq

hi ,
Have somebody seen the solution about Create Clusted key and Create SQLIndex for Nav to prevent the table lock staff?


IF somebody have tried this ,Please tell me about it ,because I tried in NAV 4.1 + SQL 2005, It's not working!

kine

rxqrxqrxqrxqrxq wrote:

hi ,
Have somebody seen the solution about Create Clusted key and Create SQLIndex for Nav to prevent the table lock staff?


IF somebody have tried this ,Please tell me about it ,because I tried in NAV 4.1 + SQL 2005, It's not working!

1) NAV4.1 is not compatible with MS SQL 2005... recommended version is NAV4.00SP3 + latest updates
2) Creating indexes or clustered indexes will not solve the locking problem. It is optimalization to lower the lock times and thus solving the timeouts for users... but performance optimalization is much more complex thing than just creating the indexes...

rxqrxqrxqrxqrxq

Thank God,I take whole afternoon to sit here to wait for the answer,
Hello,kine ,Could I have your MSN?and I got a stranger Problem for this issue ,if don't disturb you so much ,I will appriciate for it very!

my MSN:rxqrxqrxqrxqrxq@hotmail.com

David_Singleton

rxqrxqrxqrxqrxq wrote:

Thank God,I take whole afternoon to sit here to wait for the answer,
Hello,kine ,Could I have your MSN?and I got a stranger Problem for this issue ,if don't disturb you so much ,I will appriciate for it very!

my MSN:rxqrxqrxqrxqrxq@hotmail.com

:shock:


rxqrxqrxqrxqrxq best is to log a support incident with Microsoft, AND PAY!
:evil:

rxqrxqrxqrxqrxq

I already have done this ,It seems they have not any idea about this,so ,I came here ,and hope somebody would be helpful for me !

David_Singleton

rxqrxqrxqrxqrxq wrote:

I already have done this ,It seems they have not any idea about this,so ,I came here ,and hope somebody would be helpful for me !

I think you misunderstood.

My remark was that you have been a member of this site less than 2 hours and you are already complaining how long it takes to get a reply.

rxqrxqrxqrxqrxq

Yes,you are right, anyway,
you know what the logistic about preventing the lock table by using the clustered key and SQLIndex property?

as my idea, if you use LOCKTABLE,NAV would lock the range records which is grouped by SQLIndex fields,if I set the filter to some of which,
am I right?

kine

rxqrxqrxqrxqrxq wrote:

Yes,you are right, anyway,
you know what the logistic about preventing the lock table by using the clustered key and SQLIndex property?

as my idea, if you use LOCKTABLE,NAV would lock the range records which is grouped by SQLIndex fields,if I set the filter to some of which,
am I right?

Locked are records/pages/tables which are read... just setting filter is not enough. Each FIND, NEXT or GET or INSERT/MODIFY/DELETE after Lockateble will lock something or expand existing lock to bigger part... nothing with indexes...

rxqrxqrxqrxqrxq

normally,I know Nav would lock records for range level,and what my point is how to minimize the count of these records


I saw some document which tell me I can get this effort by setting the Clustered key and setting the SQLIndex ,and I just followed the document ,but I never get the right result.(if you need this detail document, I can send it to you)


I don't know if you guys ever did this before? and whether you get the result or not!

kine

Creating good indexes will speed things up and thus making the locks shorts, it will not have direct impact to size or number of locks...

rxqrxqrxqrxqrxq

Ok，firstly ,I should Thank you for your attention,Please see the famous Example for the table Document Dimension:


In NAV data from T357 is selected by querying the "Dimension" records e.g. from the "Sales Header" and "Sales Line" table.
Process A (simplified code):
DocDim.LOCKTABLE;
DocDim.SETFILTER("Table ID", '36|37');
DocDim.SETRANGE("Document Type", Order);
DocDim.SETRANGE("Document No.", 100);

but you input coding like this ,NAV would lock more records than you expected

Process A Table ID Document Type Document No. Line No. Dimension Code Process B
-> 36 Order 100 0 Dim1
L 36 Order 100 0 Dim2
O 36 Order 200 0 Dim1
C 36 Order 200 0 Dim2
K 36 … … … …
37 Order 100 10000 Dim3
<- 37 Order 100 20000 Dim4
37 Order 200 10000 Dim3
37 Order 200 20000 Dim4
37 … … … …
Am I right?
By forcing an optimized physical order of the records by changing the "Clustered Index" these kind of block could be prevented:
Clustered Index (Optimized): Document No., Document Type, Table ID, Line No., Dimension Code

Am I right?

kine

Better will be to:

divide the filter DocDim.SETFILTER("Table ID", '36|37'); into two loops with filtering first for table ID 36 and then 37... ;-)

Problem with the indexes is that you never know which will be really used, you can just assume... It is better to not use the complex filters and filter just to one value if possible...

rxqrxqrxqrxqrxq

Yeah,I get you totally,
But the fact is not like it should be,That 's why I am here,and that's what I am working in,

what I found is
if I put code like this ,
Record.RESET;
Record.SETRANGE(Name,'Name 1');
IF Record.FIND('-') THEN
REPEAT
;
UNTIL Record.NEXT =0;

but what confused me is Nav will lock the record whose field Name is 'Name' ,I don't know Why, and that 's exactly where I am when I post the information to Mibuso

kine

Every time SQL lock the needed records "and their neighbors" - it means if Name is right next to Name 1, it will be locked too...

rxqrxqrxqrxqrxq

Ok ,Thank you
1.I would test the theory of the neighbor record
2.How can I do about this situation: Process A lock the table about some records .at the same time ,I want to let Process B can insert the record and don't refresh Process A Data?

kine

Than you will need to have some mechanism how to divide the data for both processes in the table. If you search this forum, there are some posts about using GUID as PK to have better distribution of the locked data in the table and to have bigger chance that the needed record will not be locked as neighbor. Another example is inserting some dummy records which are creating dummy neighbors to prevent locking records which you need to use with another process (like creating some dummy journal lines with dummy batch name to fill gasp between two batches etc.

rxqrxqrxqrxqrxq

It's so weird,Please Look at the following Example:

No Name Name 2
01 Name Name 2
02 Name1 Name 2
03 Name Name 2
04 ppp sss


When I do coding like this:

gInt := gInt;
gRecTest.LOCKTABLE;
gRecTest.SETRANGE(No,'01');
IF gRecTest.FIND('-') THEN
REPEAT
gInt := gInt;
gInt := gInt;
gInt := gInt;
gInt := gInt;
UNTIL gRecTest.NEXT =0;

gInt := gInt;
gInt := gInt;
gInt := gInt;
gInt := gInt;

I found the whole Table locked ,why?

kine

1) Because locking granularity is driven by MS SQL, and if SQL means that table lock is cheaper than locking 2 of 4 records, it will use it...

2) If you want to force SQL to lock record by record, you can enable the RowLock hinting in the DB options in NAV. This is ON by default in older versions which do not have this option to disable it.

3) How do you know that whole table is locked?

rxqrxqrxqrxqrxq

1) what I want the sql to do is just lock one record '01'
2) Yes ,I found the option setup in Database ->Alter Database ->Advanced
But it is not working ,even I do coding like the previous ,I still can not update any of record in this table from other navision sql client

3) I set up break point in this coding last line:gInt := gInt;
I think the sql would lock the record '01' ,not other records.
But the fact is not like this ,I get into this table in other nav sql client,and can not update any of these records,So I think ,it seems the codeing lock the whole table!

bbrown

The table is probably not being locked (can another process read records?). The LOCKTABLE forces serializable transaction isolation.

rxqrxqrxqrxqrxq

Yes ,the record can be read by other process,but my purpose is ,when one process lock the table for some records which have the same features, the other process can insert or update the records which have not the features!!!