TryFunction and SQL Buffering

igor.chladil · 2016-04-12

Hello,

I have been trying a bit TryFunction feature in NAV 2016 CU5 and I have found an issue which looks like the SQL buffers are not cleared when the logic in the TryFunction fails.

In the example I have created a TryFunction called TryInsert(int From,int To) which tries to insert records in the numeric range. It contains no COMMITs inside or explicit errors. I wanted to test what the TryFunction help means by Changes to the database that are made with a try function are not rolled back.

The scenario goes like

IF TryInsert(5,10) THEN ;
COMMIT;
IF TryInsert(1,7) THEN ;

As all the code is in the TryFunction block I would expect this code to pass without the failure, i.e. no error message should be shown to the user. Expected result is that records 1..10 are inserted in the end and that the second TryInsert run actually fails in the try block with consumed error message when trying to insert record number 5 which was already inserted in the first TryInsert call. This bit really happens. However the code fails in the end with the real error message thrown to the user The Test Record already exists. Identification fields and values: Entry No.='1'.
But there were no 2 attempts in that code to insert record 1 twice. Which according to me means that record 1 was inserted in TryInsert(1,7) as expected AND SQL buffers inserting it for the first time were not cleared once the record was inserted. And those SQL buffers seems to be actually cleared and executed again only once the code hits the end and implicit COMMIT occurs.

Is it a bug or expected behaviour? Can anybody explain to me what the TryFunction description in the help about no rollback really means?

Thanks,
Igor

The code sample is below. I was not able to attach it as a txt file with the error message Uploaded file type is not allowed.

OBJECT Table 50000 Test Record
{
  OBJECT-PROPERTIES
  {
    Date=12/04/16;
    Time=00:00:00;
    Modified=Yes;
    Version List=Test Try Function;
  }
  PROPERTIES
  {
  }
  FIELDS
  {
    { 1   ;   ;Entry No.           ;Integer        }
  }
  KEYS
  {
    {    ;Entry No.                               ;Clustered=Yes }
  }
  FIELDGROUPS
  {
  }
  CODE
  {

    BEGIN
    END.
  }
}

OBJECT Codeunit 50000 Test Try Function
{
  OBJECT-PROPERTIES
  {
    Date=12/04/16;
    Time=09:37:35;
    Modified=Yes;
    Version List=Test Try Function;
  }
  PROPERTIES
  {
    OnRun=VAR
            TestRecord@1000000000 : Record 50000;
          BEGIN
            // initialize test
            TestRecord.DELETEALL;
            CLEARLASTERROR;
            COMMIT;

            IF NOT TryInsert(5,10) THEN
              MESSAGE('Failed 5-10 with ' + GETLASTERRORTEXT);
            COMMIT;
            IF NOT TryInsert(1,7) THEN
              MESSAGE('Failed 1-7 with ' + GETLASTERRORTEXT);

            // but why does it fail here again? With error Test Record 1 already inserted?
            MESSAGE('Count %1',TestRecord.COUNT);
          END;

  }
  CODE
  {

    [TryFunction]
    LOCAL PROCEDURE TryInsert@1000000001(FromNo@1000000000 : Integer;ToNo@1000000001 : Integer);
    VAR
      TestRecord@1000000002 : Record 50000;
      Loop@1000000003 : Integer;
    BEGIN
      FOR Loop := FromNo TO ToNo DO BEGIN
        TestRecord."Entry No." := Loop;
        TestRecord.INSERT;
      END;
    END;

    BEGIN
    END.
  }
}

kriki · 2016-04-21

Because of this COMMIT:

IF TryInsert(5,10) THEN ;
COMMIT;

C/AL starts writing to the DB until it hits an error. But instead of undoing everything, it just exists and the developer is responsible to do with he wants to do AND THEN RE-THROW AN ERROR!

When I read how Microsoft had implemented the try-function, I WAS UTTERLY HORRIFIED (and I wasn't alone vjeko.com/whats-new-in-nav-2016-splitting-atoms-with-tryfunction vjeko.com/tryfunction-lessons-learned-from-preview-posting)! If developers just try to use it but don't know what is going behind the scenes, it is possible to wreak havoc in the DB.

igor.chladil · 2016-04-22

Hi Kriki,

Thanks for your response and links to Vjeko's posts.
That COMMIT after the first TryInsert(5,10) did not cause the failure as it really failed with real user failure after the second TryInsert(1,7). And please note that Entry No.=1 was tried to be inserted exactly once in the code and it failed on it because it tried to insert it for the 2nd time. According to the Vjeko's blogs I believe this is still a bug compared to the documentation which states that all the errors will be caught by TryInsert and I can handle these myself.
It rather looks like if the following happened:

TryInsert(5 .. 10)
- Insert Entry 5..10 - OK
COMMIT
TryInsert(1 .. 7)
- Insert Entry 1..4 - OK
- Insert Entry 5 -> failure (it was inserted before), SQL buffers left in place, however this failure was caught by TryFunction
....
Implicit transaction commit -> ?empty SQL buffers? -> Insert Entry 1 again!? - I did not expect this error.

I agree with Vjekos post that TryFunctions are dangerous - these are getting us a bit closer to .NET try/catch where we have to handle the transactions and its rollbacks in such a case often ourselves. On the other hand for many scenarios I am happy that MS finally introduced TryFunctions and I do not have to create Codeunits everytime I need to achieve such a functionality.

It would be also nice if we had the option to set up the transactional behaviour for these functions - e.g. to ignore the explicit COMMITs in the way we can do it in TestCodeUnits. The Preview posting would be much more elegant and less error prone [similar to nested SQL transactions]. And then if MS provided us with a default option for TryFunction which would ignore explicit COMMITs and ROLLBACK on the first error with the option to override these defaults, it would be almost perfect for the majority of the scenarios which I need ...

Regards,
Igor

ara3n · 2016-04-23

Is MS doing anything to fix the TryFunctions? Has it been report to MS?

kriki · 2016-04-25

I re-read your post a little and I missed your whole point.

Now I tried out your code and recreated the problem.

I did another test:
I changed "TestRecord.INSERT;" to "IF TestRecord.INSERT THEN ;" and then it works correctly!

The reason is that in the first case, the buffered insert is used and the real insert is triggering at the implicit COMMIT at the end of the processing (in this case the end of the codeunit). At that point the try-function has already finished and so the error is not caught anymore. So to avoid the problem, use the IF INSERT THEN to force the insert.

Do another action (like a RESET; IF ISEMPTY THEN

on the table at the end of the try-function should also do the trick. This other action forces NAV to insert the records before it can run the new action. BUT IT DOES NOT WORK! The error comes up anyway and is not caught by the try-function.

So apart for the reasons mentioned by Vjeko in his blog (and I agree with them!). Another reason to not use them (at least at the moment), is because they are buggy and cannot be trusted fully.

And the final reason to not use them is because of performance and locking. Doing all the processing,locking and writing everything to the DB to do a rollback in the end is just horrible!

Howdy, Stranger!

Quick Links

Categories

TryFunction and SQL Buffering

Comments