Error acces Lock TABLE in nav 2013

pruebasEx
pruebasEx Member Posts: 51
in NAV Three Tier
I have 2 reports who uses the same table. (pT_Filter --- TemporalFilter)

The table is in the 50000 range.

There are only a few lines referred to this table.

REPORT 50001
[b]OnPreReport[/b]

pT_Filter.RESET;
IF NOT pT_Filter.GET THEN
  pT_Filter.INSERT;

pT_Filter."Date From CG21" := v_periodFrom;
pT_Filter."Date To CG21" := v_periodTo;
pT_Filter.MODIFY;


[b]OnPostReport[/b]

pT_Filter."Date From CG21" := '';
pT_Filter."Date To CG21" := '';
pT_Filter.MODIFY;


REPORT 50002
[b]OnPreReport[/b]


IF NOT pT_Filter.FINDFIRST THEN
  pT_Filter.INSERT
ELSE BEGIN
  pT_Filter.RESET;
  pT_Filter.LOCKTABLE;
  pT_Filter."Date from CG19" := text_from;
  pT_Filter."Date to CG19" := text_to;
  pT_Filter.MODIFY;
  COMMIT;
END;

Sometimes, when I try to execute from differents users. (Or either from same user both reports at the same time). Nav throw me an error from this table is Locked

Maybe there is a problem in report 50001, but I cant explain myself why is this happening.

Best Answer

Answers

  • logger
    logger Member Posts: 126
    Hi @pruebasEx ,
    All data changes require table to be locked. If table is already locked, NAV returns an error. That's ok. To manage this you can use LOCKTABLE with parameters. I.e. pT_Filter.LOCKTABLE(TRUE);
    To get much info about LOCKTABLE, consult https://msdn.microsoft.com/ru-ru/library/dd301298.aspx

    Take into account, that timeout period is set on database parameters accesible through development environent, and by default is 10 seconds.
    Let's go!
  • pruebasEx
    pruebasEx Member Posts: 51
    logger wrote: »
    Hi @pruebasEx ,
    All data changes require table to be locked. If table is already locked, NAV returns an error. That's ok. To manage this you can use LOCKTABLE with parameters. I.e. pT_Filter.LOCKTABLE(TRUE);
    To get much info about LOCKTABLE, consult https://msdn.microsoft.com/ru-ru/library/dd301298.aspx

    Take into account, that timeout period is set on database parameters accesible through development environent, and by default is 10 seconds.

    Ok, so with the code of the report 50001 If I run it 2 R50001 at the same time. Nav will throw me an error because until one of the reports will finish the other cant acces to the same table. Because nav locks this table by default until the report has finished.

    I'm right?

    Maybe I have to change the code in this way to prevent the error.

    REPORT 50001
    [b]OnPreReport[/b]

pT_Filter.RESET;
IF NOT pT_Filter.GET THEN
  pT_Filter.INSERT;

pT_Filter.LOCKTABLE(TRUE);
pT_Filter."Date From CG21" := v_periodFrom;
pT_Filter."Date To CG21" := v_periodTo;
pT_Filter.MODIFY;
pT_Filter.COMMIT;


[b]OnPostReport[/b]

pT_Filter.LOCKTABLE(TRUE);
pT_Filter."Date From CG21" := '';
pT_Filter."Date To CG21" := '';
pT_Filter.MODIFY;
pT_Filter.COMMIT;
  • logger
    logger Member Posts: 126
    Answer ✓
    @pruebasEx ,
    Yes, you are. You had better to use LOCKTABLE once after RESET. As I can see, there is INSERT statement, and table should be locked to ensure no one changes it at the same time.
    By the way, see https://www.youtube.com/watch?v=2kMNUls_vwU to get more info about LOCKTABLE.
    Let's go!
  • lubost
    lubost Member Posts: 633
    Use table as temporary and reports will create its own table instance.
  • pruebasEx
    pruebasEx Member Posts: 51
    lubost wrote: »
    Use table as temporary and reports will create its own table instance.

    The problem was that maybe(surely) the table is being used by another process.

    Thanks.
  • Slawek_Guzek
    Slawek_Guzek Member Posts: 1,692
    Have you resloved it or do you still have locking issues?
    Slawek Guzek - www.yitron.co.uk
    Business Central, MS SQL Server, Wherescape RED;
  • pruebasEx
    pruebasEx Member Posts: 51
    Slawek_Guzek wrote: »
    Have you resloved it or do you still have locking issues?

    Yes, it's solved.

Categories