Locktable function

jks

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.

jks

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

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

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

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

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

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

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

@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
2. FIND is not obsolete, you should read online help: you can't just replace all find('-') with findfirst...

muletail

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

1. yes, google... 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...

Eugene

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

wrong post