SQL: Record is modified by another user

Happy New Year @all!

kine wrote:

There can be two sources of the problem:

1) Wrong code design (nested get-modify between another get-modify something like Rec1.GET-Rec2.GET-Rec2.MODIFY-Rec1.MODIFY)
2) Long time between getting the record from DB and modifying it without locking the record when reading (LOCKTABLE).

Yes, of course. These two sources are the "normal" way to get this message. But in this case neither the first one nor the second one is the reason for this message. I even made a backup of the database and restored this backup to a new native database. When I use this new native database the error message doesn't appear.

In my understanding of database management systems a new transaction should be started when a new record is processed.
The solution works like this:
Start (Event-Trigger of an Automation Server; record is passed as parameter) -> parse record -> insert/modify record in a certain table
The error message doesn't appear until the third record is processed.
And it doesn't matter if the event is fired during the processing of a former record or long time after all former records have been processed.

Then I think it as something to do with this timestamp behaviour.
I would post some code but the problem is that there isn't one bit of code where I use the modify but many.

What about the timestamp when I do something like this:

rec1 := rec2;

OK, I will try my very best to explain the situation. It is very difficult and my poor english doesn't get it better.

It's an external system to discribe a business process. When this process starts an event is fired and a string is passed as parameter into NAV. The string is parsed and some (depending on the setup) functions will be execute. I have to assure that one function will only be execute once for each string. Network problems or user interaction could be the cause of receiving the same string twice or more. (It's not exactly the same string but it would cause the same process.) For example: A string would cause the system to execute five functions and there would be a problem within the 3rd function then the next string for this process have to restart with function 3, not with function 1 again. (It's something like a state machine.) So I use a LOCKTABLE at the beginning and a COMMIT at the end of each function.

In some cases it is necessary that the process doesn't restart with function 3 but with function 2. And I think this is the problem why I get sometimes this error message.
Please notice that the error message doesn't occur within one process sequence. I have to fire the event at least three times.

...
StartStateMachine;

IF ProceedingError THEN BEGIN
  ...
  rec1.LOCKTABLE;
  IF RestorePossible THEN BEGIN
    IF rec1.DELETE THEN BEGIN
      rec1 := rec3;              // I think that will be the problem with timestamp
      IF rec1.INSERT THEN
        ...
    END ELSE BEGIN
      rec1."Abort after Error" := TRUE;
      IF NOT rec1.MODIFY THEN
        ...
    END;
  END ELSE BEGIN
    rec1."Abort after Error" := TRUE;
    IF NOT rec1.MODIFY THEN
      ...
  END;
  COMMIT;
  EXIT;
END;
...

Function StartStateMachine

RestorePossible := FALSE;
WITH rec1 DO BEGIN
  LOCKTABLE;
  IF NOT rec2.GET(...) THEN
    StartNewStatusProceeding(TRUE)
  ELSE BEGIN
    ...
    IF NOT Status.GET(...) THEN BEGIN
      ...
      EXIT;
    END;
    IF (Status."Status Type" = Status."Status Type"::Ending) OR
       rec2."Abort after Error"
    THEN
      StartNewStatusProceeding(FALSE)
    ELSE BEGIN
      ...
    END;
  END;
COMMIT;

Function StartNewStatusProceeding(NewRecord : Boolean)

WITH rec1 DO BEGIN
  LOCKTABLE;
  ...
  "Status Group" := Status."Status Group";
  "Status Code" := Status.Code;
  "Status Type" := Status."Status Type";
  IF NewRecord THEN BEGIN
    IF NOT INSERT THEN BEGIN
      ...
    END;
  END ELSE BEGIN
    IF NOT MODIFY THEN BEGIN  // this is where the error message occurs
      ...
    END;
  END;
END;