Locktable function

jks
jks Member Posts: 277
edited 2009-09-21 in NAV/Navision Classic Client
Hi all,

I am using Navision with SQL Sever option. I want to prevent the other user from editing the table when table is locked by other user.

e.g
Table name is Test.
calculatecost
{
Test.Get(value);
}

Now if another user opens Test table for editing then he should not be able to edit it. However calculatecost function doesn't modify table test.
I tried using following code but it still does allow another user to modify Test table
calculatecost
{
Test.Get(value);
Test.Locktable(true,false)
}

Please help.

Comments

  • jks
    jks Member Posts: 277
    Thanks for your help. It works.

    But a query,
    My primary key in Test table refers to Company table and Test table contains one record per company.
    So If i use Test.Get(CompanyName) instead of Test.FindSet then will it make any difference?

    What is the exact difference between Get and FindSet.
  • jks
    jks Member Posts: 277
    so in that case my Get statement should be after FindSet like

    Test.RESET;
    Test.LOCKTABLE;
    IF RECORDLEVELLOCKING THEN
    Test.FINDSET;
    Test.Get(CompanyName);

    and one more thing. if FindSet retrieves and lock all the record, then why Test.LOCKTABLE statement is required.
  • jks
    jks Member Posts: 277
    I have locked a record of table Test inside CalculateCost function . I am calling another function from CalculateCost.
    So in this case lock on the Test table is not retained in the called function.
    So other user is able to modify Test table record as soon as control is in the called function.

    I want the lock on the Test table record in all the functions called from CalculateCost function.

    How to achieve this?
  • muletail
    muletail Member Posts: 3
    BlackTiger wrote:
    Big difference.

    GET retrieves only values for single record.

    FINDSET retrieves all records for specified range. In this case FINDSET will retrieve (and lock!) all records in table.

    It retrieves only a part of records.
    The size of this part is setted in the DB settings.

    "This function works alongside the database property Record Set.
    To set this property, open Navision and click File, Database, Alter and click the
    Advanced tab."

    So, if you need to lock all the records - you need smth like this:
    Test.RESET;
    Test.LOCKTABLE;
    IF RECORDLEVELLOCKING THEN
    IF Test.FINDSET then
    REPEAT
    UNTIL Test.NEXT = 0;

    BlackTiger wrote:
    It's impossible.

    Not sure, but if you follow the logic - the lock is active in all functions until the completion of the transaction
    (until explicit\implicit Commit call)
  • Eugene
    Eugene Member Posts: 309
    just to clarify things - MS SQL server provides "record level locking" that is it allows to lock a single record in a table instead of locking the whole table as it was in native navision databases. So basicaly MS SQL locks only the records you have read after your LOCKTABLE statement (LOCKTABLE command as well as INSERT and MODIFY and FINDSET(TRUE) commands simply tell the SQL server to turn locking mechanism on).

    to prevent others from inserting into the table AFTER THE LAST record you can issue
    LOCKTABLE;
    FIND('+');

    to prevent others from inserting into the table BEFORE THE FIRST record you can issue
    LOCKTABLE;
    FIND('-');


    LOCKTABLE plus FINDSET locks a range of records that are cache-read from the server (there is a database parameter to set how many records to cache-read) but to lock all the record you need to read them all.

    P.S.
    issuing a NEXT command would lock the record you read but MS SQL can scale up the lock and lock the whole range of the following records as well that is it may decide to lock more than one record but many (there is a database parameter to prevent that that is to always row lock and never scale the lock up but in general setting that parameter will degrade performance)
  • Eugene
    Eugene Member Posts: 309
    now to your problem.
    as far as i understand you are afraid of the following situation:

    lets say A wants to modify records from 1000 to 9000 and it reads modifies them one by one.
    at the same time B manages to read lets say record 5000 and modify it before A even comes to read that record.

    since navision works in SERIALIZABLE transaction isolation mode (u need to call LOCKTABLE exactly for that - to put transaction into this isolation level mode) you should not worry about the situation above

    http://en.wikipedia.org/wiki/Isolation_%28database_systems%29#SERIALIZABLE
  • muletail
    muletail Member Posts: 3
    Eugene wrote:
    to prevent others from inserting into the table AFTER THE LAST record you can issue
    LOCKTABLE;
    FIND('+');

    to prevent others from inserting into the table BEFORE THE FIRST record you can issue
    LOCKTABLE;
    FIND('-');

    It`s kind of cavil but have to note that using of FIND statements in Navision SQL Server Option is obsolete.
    Good choice are FINDFIRST and FINDLAST statements.
  • Belias
    Belias Member Posts: 2,998
    @muletail:
    1. after 3 years (see original post) i think the problem is solved, and the developer has become so skilled that don't have to ask it :mrgreen:
    2. FIND is not obsolete, you should read online help: you can't just replace all find('-') with findfirst...
    -Mirko-
    "Never memorize what you can easily find in a book".....Or Mibuso
    My Blog
  • muletail
    muletail Member Posts: 3
    1. I think it`s not good - when wrong opinions that indexed by Google gets in people's heads.

    2. "I am using Navision with SQL Sever option" - topic author said...
    I do not call to replace all the lines of the original code. Why it might still needed in new code with Navision Sql Server Option? All that you might need is FINDSET, FINFIRST AND FINDLAST instead FIND i think...
  • Belias
    Belias Member Posts: 2,998
    1. yes, google... :mrgreen: the smile is "take it easy"...i didn't want to say that you were saying useless thins (just to be clear)
    2. directly from an official microsoft manual
    We do not recommend that you use FINDSET with a large result set. That is a
    result set that is larger than the Record Set Size parameter. In this case, you
    should use Find('-').
    and also
    NOTE: FINDSET only allows you to loop through the record set from the top
    down. If you want to loop from the bottom up, use FIND('+').

    anyway, there are different feelings about which performs better between findset and find('-') for large sets of records...
    -Mirko-
    "Never memorize what you can easily find in a book".....Or Mibuso
    My Blog
  • Eugene
    Eugene Member Posts: 309
    if you read NAV5.0 Solutions Developement course from Microsoft you will find there that Microsoft suggests to use FIND('-') over FINDSET in certain cases (when the number of records that will be read is expected to be small).

    I do agree however that in my previous post i should have used FINDSET in my example instead of FIND('-') because FIND('-') tries to read all the records at once (can be millions of records) whereas FINDSET fetches them in chunks of several hundreds.

    This is slightly offtopic but since we started this discussion here i'll give a small summary of when to use which option:

    ISEMPTY - if u dont need to use any records later on just need to check if there are any records in the filter
    FINDFIRST - if u need to use one record only and dont need to iterate through a recordset
    FIND('-') - if u need to iterate through a recordset and is garanteed it will be short enough to read it in one go (what FINDSET would have read in one go)
    FINDSET - if u need to iterate through a recordset and you know it will be lengthy thast is you will have to do many fetches (default server setting is several hundred records each of the fetches)
  • Belias
    Belias Member Posts: 2,998
    wrong post
    -Mirko-
    "Never memorize what you can easily find in a book".....Or Mibuso
    My Blog

Categories