Use of LOCKTABLE in Navision (SQL DB)

tapas6324

I created a table & the Primary key is “SerialNo” which is Auto increment (BigInteger); now at the time of Posting in POS I written the following Code:

……………………………

…………………………..

RedeemTable.Reset;

RedeemTable.FIND(‘+’);

SerialNo:= SerialNo + 1;

User ID:= ………;

Till ID:=………;

RedeemTable.INSERT;

……………………………

……………………………



1) So my doubt is whether I should add “RedeemTable.LOCKTABLE;” or not. If yes then before FIND('+') or After FIND('+')?. Is SQL locks it automatically at the time of write transaction or I need to lock it explicitly.

2) As we know that when we insert multiple records in a table on that time we use “COMMIT” statement but in the above scenario just 1 record will be inserted; though I need to confirm that if I use “RedeemTable.LOCKTABLE;” is it required to add “RedeemTable.COMMIT;” after RedeemTable.INSERT; to remove the LOCK on the table or as I am inserting only 1 record so after insertion the LOCK will be removed automatically & no need to add COMMIT statement.

bbrown

It is rarely required to use explicit commits with Navision. You should think thru your code and use them only when absolutely required.

A LockTable with SQL does not lock anything. It only set the transaction isolation level to SERIALIZABLE. If you want to control the sequence of updates then yes use LockTable. See Codeunit 12 as an example.

todro

tapas6324 wrote:

I created a table & the Primary key is “SerialNo” which is Auto increment (BigInteger); now at the time of Posting in POS I written the following Code:

RedeemTable.Reset;

RedeemTable.FIND(‘+’);

SerialNo:= SerialNo + 1;

User ID:= ………;

Till ID:=………;

RedeemTable.INSERT;

As you already use AutoIncrement

SerialNo:= SerialNo + 1;

is not necessary as NAV takes care of this. Either you use auto increment or you increment manually like you did in the code above, but not both.

Auto increment is an atomic operation (lock, read old record, increment, insert with new PK value) which is executed when you run the INSERT and it can not be interrupted. This means either all steps are carried out or none.

If you decide to increment manually, you will have to lock, to ensure no other transaction interfers or your incremented PK value might be already in use by an other client inserting data. Like bbrown already stated, in this case you need to add a lock statement.

Be careful with

RedeemTable.Locktable;
RedeemTable.FIND(‘+’); 

as the locktable effects the next table access, in this case the FIND('+'). In the past, this was used in NAV to enforce a complete table lock.

tapas6324

So if I use LOCKTABLE is it required to use COMMIT statement ?

bbrown

todro wrote:

Auto increment is an atomic operation (lock, read old record, increment, insert with new PK value) which is executed when you run the INSERT and it can not be interrupted. This means either all steps are carried out or none.

An Auto-Increment operation does not read anything. The information to determine the next PK value is stored as part of the object catalog.

The use of explicit commit is determined by the code structure and desired behavior and not the use of LockTable

Marije_Brummel

Your signature sais certified for NAV .

Then you should have learned that programming a COMMIT is against all rules. You do not need that if your code is properly written.

The benefit of autoincrement is to insert unique records without having to lock the last record in the table.

However unique does not mean that there are no gaps in the numbering. If 2 users try to insert a record and the first one cancels the transaction this number will never be used.

We use it a lot in CRM.

If you do not want gaps, use the classic NAV way to increment by locking the last record and add 1 to the last value as seen all over the standard app. But turn off autoincrement because you will get runtime errors.

Play with it. Write 2 codeunits that create a new record and insert new data. Play with the locktable command and cancel some transactions. See what happens and try to understand how it works.

Good luck.

tapas6324

As I am doing:

Table.FIND('+');
//Table.SerianlNo := Table.SerialNo + 1;
........
........
Table.INSERT;

So if I comment the line "//Table.SerianlNo := Table.SerialNo + 1;
" & if suppose the last record number is 15 then its giving an run time error as: "The record 15 is alredy exsits"

Thats why I am increasing manually the Autoincrement field SerialNo by the code line: Table.SerianlNo := Table.SerialNo + 1;

1) Now my question is: Why it is not incrementing automatically while the autoincrement preperty is Yes.

2) Should I use LOCKTABLE as many users will insert records Simultaneously? if yes then before FIND('+') or after FIND('+') ?

Thanks in advance.

ara3n

make sure you init your variable.




able.FIND('+');
Table.init
//Table.SerianlNo := Table.SerialNo + 1;
........
........
Table.INSERT;

or at least set Table.SerianlNo := 0 before the insert.
If SerialNo has a value, it will not be auto incremented and will insert that value.

tapas6324

I want to know about LOCKTABLE......Still I not got my answer

kine

When using autoincrement, be sure that you fill the autoincrement field by 0 before you insert the record. Do not forget that INIT is not clearing PK fileds...

If you skip that, you will have error that record already exists or permission error for SET operation (SQL is trying to move the counter to new value but you must be db_owner or higher to be able to do that - common error)

ara3n

kine wrote:

When using autoincrement, be sure that you fill the autoincrement field by 0 before you insert the record. Do not forget that INIT is not clearing PK fileds...

If you skip that, you will have error that record already exists or permission error for SET operation (SQL is trying to move the counter to new value but you must be db_owner or higher to be able to do that - common error)

forgot about the init. I don't use init that's why

I always use clear();

thanks

ara3n

tapas6324 wrote:

I want to know about LOCKTABLE......Still I not got my answer

Please do not use Locktable if you autoincrementing.

tapas6324

Table.FIND('+');
Table.INIT;
//Table.SerianlNo := Table.SerialNo + 1;
........
........
Table.INSERT;

So if I comment the line "//Table.SerianlNo := Table.SerialNo + 1;
" & if suppose the last record number is 15 then its giving an run time error as: "The record 15 is alredy exsits"

Thats why I am increasing manually the Autoincrement field SerialNo by the code line: Table.SerianlNo := Table.SerialNo + 1;

1) Now my question is: Why it is not incrementing automatically while the autoincrement preperty is Yes.

2) Should I use LOCKTABLE as many users will insert records Simultaneously? if yes then before FIND('+') or after FIND('+') ?

bbrown

With autoincrement the FIND is not needed. Neither is the INIT;

CLEAR(Table);
[Set non-PK field values]
Table.INSERT;

kine

tapas6324 wrote:

Table.FIND('+');
Table.INIT;
//Table.SerianlNo := Table.SerialNo + 1;
........
........
Table.INSERT;

So if I comment the line "//Table.SerianlNo := Table.SerialNo + 1;
" & if suppose the last record number is 15 then its giving an run time error as: "The record 15 is alredy exsits"

Thats why I am increasing manually the Autoincrement field SerialNo by the code line: Table.SerianlNo := Table.SerialNo + 1;

1) Now my question is: Why it is not incrementing automatically while the autoincrement preperty is Yes.

2) Should I use LOCKTABLE as many users will insert records Simultaneously? if yes then before FIND('+') or after FIND('+') ?

It is not autoincrementing, because MS SQL is assigning new no. just only if the field is 0 or NULL. If you are inserting record with some other value, MS SQL will try to change the counter to this new value (DB_Owner and Sysadmin will success but others will have permission error).

It is better to use INIT, because Clear is clearing whole record object - it means all global variables etc. and this can have performance impact (e.g. whne in some trigger is some setup read in correct way - just once). And by clearing the variable you clear all filters etc. you can use in another part of the code. it means, it is doing much more than necessary 8)

bbrown

INIT will not work since it does not clear the primary key fields. You must clear the primary fields. If the primary key fields have a value, then an attempt is made to insert those values. In SQL this will fail if you are not at least DB_Owner. (DBCC Identity Insert ON).

DenSter

so INIT DOES work, as long as you clear the field that has to auto-increment.

kine

:-)

Thanks Deniel...

tapas6324

HI Dear,

INIT is not at all concernt for me....I am asking for LOCKTABLE that in SQL should I use LOCKTABLE???????

If Yes then before FIND('+') or after FIND('+') ?


Thanks in advance.
Regards,
Tapas

bbrown

DenSter wrote:

so INIT DOES work, as long as you clear the field that has to auto-increment.

The use of INIT is irrelevant. The requirement is to clear the primary key values. Either thru Rec.CLEAR or an explicit assignment or Rec2.COPY(rec1, TRUE). If the primary key values are cleared , then auto-increment will work regardless of whether or not a Rec.INIT has been called.

The decision to use one approach over the other is dependent on the overall purpose of the code being written.

ara3n

tapas6324 wrote:

HI Dear,

INIT is not at all concernt for me....I am asking for LOCKTABLE that in SQL should I use LOCKTABLE???????

If Yes then before FIND('+') or after FIND('+') ?


Thanks in advance.
Regards,
Tapas

If you want your transaction to be SERIALIZABLE then you can use locktable. But in your case you are autoincrementing, so you don't need to lock the table.
Anyways if you want to still use locktable then do it before FIND('+')

kine

bbrown wrote:

DenSter wrote:

so INIT DOES work, as long as you clear the field that has to auto-increment.

The use of INIT is irrelevant. The requirement is to clear the primary key values. Either thru Rec.CLEAR or an explicit assignment or Rec2.COPY(rec1, TRUE). If the primary key values are cleared , then auto-increment will work regardless of whether or not a Rec.INIT has been called.

The decision to use one approach over the other is dependent on the overall purpose of the code being written.

The talking about INIT is just off-topic (just reaction to "I am using CLEAR"). Main thing is that PK must be cleared in some way and easiest is to assign 0.

tapas6324

HI Dear,

I am inserting only one record in a table at the time of Posting so my question is :::

Should I use LOCKTABLE (We aree using SQL DB)??????

ara3n

tapas6324 wrote:

HI Dear,

I am inserting only one record in a table at the time of Posting so my question is :::

Should I use LOCKTABLE (We aree using SQL DB)??????

NO.