Options

Strange Locking Issue

megawavezmegawavez Member Posts: 133
Hi,

We are doing something like this on a table:

TableRec.SETRANGE("Field1", 'XXXX');
If TableRec.FINDSET() THEN REPEAT
TableRec.DELETE(TRUE);
UNTIL Table.NEXT() = 0;

This set of code looks to be doing something like a table lock - If I end up running this routine at the same time on another instance of navision classic (6.0) with a different range (i.e. 'YYYY' instead of 'XXXX'), the second instance of navision will end up with a "Another User Has Locked TableRec". I think this is related to SERIALIZATION ?

Thanks for any help.
Mega

Comments

  • Options
    rmv_RUrmv_RU Member Posts: 119
    It's normal if you don't use setcurrentkey("Field1"). After locktable or any modification/deletion/insertion in table Navision use updatlock hint to reading data from SQL Server. So, if you use wrong key - lot of records may be blocked.
    Try to rewrite code with setcurrentkey("Field1") or use temporary table.
    Looking for part-time work.
    Nav, T-SQL.
  • Options
    megawavezmegawavez Member Posts: 133
    rmv_RU wrote:
    It's normal if you don't use setcurrentkey("Field1"). After locktable or any modification/deletion/insertion in table Navision use updatlock hint to reading data from SQL Server. So, if you use wrong key - lot of records may be blocked.
    Try to rewrite code with setcurrentkey("Field1") or use temporary table.

    Well, that fixed it !

    I had been told that sql picks the keys to use for navision operations automatically, but I guess that's really not the case.

    Thanks,

    Mega
  • Options
    rmv_RUrmv_RU Member Posts: 119
    megawavez wrote:
    I had been told that sql picks the keys to use for navision operations automatically.
    It's not true. Always use key with best selectivity, especially for data modification.
    Looking for part-time work.
    Nav, T-SQL.
  • Options
    megawavezmegawavez Member Posts: 133
    rmv_RU wrote:
    megawavez wrote:
    I had been told that sql picks the keys to use for navision operations automatically.
    It's not true. Always use key with best selectivity, especially for data modification.

    Thanks - I got some bad advice regarding this. I'll be putting fixes in.
  • Options
    Marije_BrummelMarije_Brummel Member, Moderators Design Patterns Posts: 4,262
    The advice has been correct for a while long time ago.

    I remember 5 years ago each NAV build had different best practices.

    Now (last years) they fixed it back to SQL prefering the SETCURRENTKEY to be there, it behaves more like the old Native database.
  • Options
    megawavezmegawavez Member Posts: 133
    The advice has been correct for a while long time ago.

    I remember 5 years ago each NAV build had different best practices.

    Now (last years) they fixed it back to SQL prefering the SETCURRENTKEY to be there, it behaves more like the old Native database.

    This is a pretty significant change, so let's hope no changes in the future.
  • Options
    mdPartnerNLmdPartnerNL Member Posts: 802
    so, by only adding setcurrentkey("Field1") this is solved.

    And NAV doesn't use "Field1" as default if not specified in code?
  • Options
    Marije_BrummelMarije_Brummel Member, Moderators Design Patterns Posts: 4,262
    If you do not specify the setcurrentkey the primairy key will be used for the ORDER BY statement.
  • Options
    mdPartnerNLmdPartnerNL Member Posts: 802
    ok :) but why did it solve his problem to add the primary key in the setcurrentkey() ?
  • Options
    Marije_BrummelMarije_Brummel Member, Moderators Design Patterns Posts: 4,262
    SQL Server analyses the query that comes in and determines the best key to use.

    If you do for example

    SELECT * FROM CUSTOMER WHERE ADDRESS = 'XX' ORDER BY NO

    or

    SELECT * FROM CUSTOMER WHERE ADDRESS = 'XX' ORDER BY ADDRESS

    SQL Server is more likely to use the index in the second example.
  • Options
    mdPartnerNLmdPartnerNL Member Posts: 802
    That's clear.

    But why did it help the TS to add setcurrentkey() ? That's automatically the default? :-k
  • Options
    Marije_BrummelMarije_Brummel Member, Moderators Design Patterns Posts: 4,262
    I don't think that in this case it was the default. Must have been something else.
  • Options
    mdPartnerNLmdPartnerNL Member Posts: 802
    @megawavez, could you provide more info?

    When we do an update for records, we expect the primary key to be used.

    As a result we do not use setcurrentkey(..with primary field...) as it's already used by default.
Sign In or Register to comment.