Error acces Lock TABLE in nav 2013

pruebasExpruebasEx Member Posts: 51
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

  • loggerlogger 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!
  • pruebasExpruebasEx 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;
    
  • loggerlogger 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!
  • lubostlubost Member Posts: 611
    Use table as temporary and reports will create its own table instance.
  • pruebasExpruebasEx 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_GuzekSlawek_Guzek Member Posts: 1,690
    Have you resloved it or do you still have locking issues?
    Slawek Guzek
    Dynamics NAV, MS SQL Server, Wherescape RED;
    PRINCE2 Practitioner - License GR657010572SG
    GDPR Certified Data Protection Officer - PECB License DPCDPO1025070-2018-03
  • pruebasExpruebasEx Member Posts: 51
    Have you resloved it or do you still have locking issues?

    Yes, it's solved.
Sign In or Register to comment.