about NAV LOCK TABLE ISSUE

rxqrxqrxqrxqrxqrxqrxqrxqrxqrxq Member Posts: 22
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!

Comments

  • kinekine Member Posts: 12,562
    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...
    Kamil Sacek
    MVP - Dynamics NAV
    My BLOG
    NAVERTICA a.s.
  • rxqrxqrxqrxqrxqrxqrxqrxqrxqrxq Member Posts: 22
    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:[email protected]
  • David_SingletonDavid_Singleton Member Posts: 5,475
    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:[email protected]

    :shock:


    rxqrxqrxqrxqrxq best is to log a support incident with Microsoft, AND PAY!
    :evil:
    David Singleton
  • rxqrxqrxqrxqrxqrxqrxqrxqrxqrxq Member Posts: 22
    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_SingletonDavid_Singleton Member Posts: 5,475
    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.
    David Singleton
  • rxqrxqrxqrxqrxqrxqrxqrxqrxqrxq Member Posts: 22
    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?
  • kinekine Member Posts: 12,562
    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...
    Kamil Sacek
    MVP - Dynamics NAV
    My BLOG
    NAVERTICA a.s.
  • rxqrxqrxqrxqrxqrxqrxqrxqrxqrxq Member Posts: 22
    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!
  • kinekine Member Posts: 12,562
    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...
    Kamil Sacek
    MVP - Dynamics NAV
    My BLOG
    NAVERTICA a.s.
  • rxqrxqrxqrxqrxqrxqrxqrxqrxqrxq Member Posts: 22
    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?
  • kinekine Member Posts: 12,562
    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...
    Kamil Sacek
    MVP - Dynamics NAV
    My BLOG
    NAVERTICA a.s.
  • rxqrxqrxqrxqrxqrxqrxqrxqrxqrxq Member Posts: 22
    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
  • kinekine Member Posts: 12,562
    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...
    Kamil Sacek
    MVP - Dynamics NAV
    My BLOG
    NAVERTICA a.s.
  • rxqrxqrxqrxqrxqrxqrxqrxqrxqrxq Member Posts: 22
    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?
  • kinekine Member Posts: 12,562
    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.
    Kamil Sacek
    MVP - Dynamics NAV
    My BLOG
    NAVERTICA a.s.
  • rxqrxqrxqrxqrxqrxqrxqrxqrxqrxq Member Posts: 22
    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?
  • kinekine Member Posts: 12,562
    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?
    Kamil Sacek
    MVP - Dynamics NAV
    My BLOG
    NAVERTICA a.s.
  • rxqrxqrxqrxqrxqrxqrxqrxqrxqrxq Member Posts: 22
    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!
  • bbrownbbrown MAMember Posts: 3,227
    The table is probably not being locked (can another process read records?). The LOCKTABLE forces serializable transaction isolation.
    There are no bugs - only undocumented features.
  • rxqrxqrxqrxqrxqrxqrxqrxqrxqrxq Member Posts: 22
    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!!!
Sign In or Register to comment.