SQL: Record is modified by another user

einsTeIn.NET

Hi

I have got some problems with SQL Server Option. I use a 4.03 Client with SQL Server Option to insert a record generated by an external app into the database (SingleInstance Codeunit with Events of an Automation Server). In some cases I get the following error message

Another user has modified the record for this <Tablename> after you retrieved it from the database.

Enter your changes again in the updated window, or start the interrupted activity again.

Identification fields and values:

[PK]

When I execute exactly the same code with a native database the error message doesn't occur.

There are some LOCKTABLE- and some COMMIT-statements within my code.

What should I consider when I use these statements? Especially when I have to use the SQL Server Option.


Kind regards

jannestig

though i am not a developer, this message is quite common in an sql environment and usualy has to do when a form or record related what your are viewing has been updated behind the scenes,

This has to do with refreshing of on screen values etc.

Reasoning would suggest they put the message there because they are aware of this limitation, refreshing the screen may help.

there is probably some better opinions and information available under searching the forum

kine

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).

einsTeIn.NET

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.

kine

There is difference of processing the modifications between Native server and MS SQL Option. MS SQL is using internal column "timestamp" to check the version of record. In some cases this timestamp is not updated in your variable and it is than trying to modify the record with "different" time stamp which leads to this error. Can you post the code you are using to modify the records?

einsTeIn.NET

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;

kine

In this case it will copy the timestamp too (I think...) and if you than modify PK fields and do modify, it will raise the error...

kriki

kine wrote:

In this case it will copy the timestamp too (I think...) and if you than modify PK fields and do modify, it will raise the error...

I can confirm it also copies the timestamp.

Eugene

it is hard to elaborate without seeing the actual code.

i can only give some hints at this point about how Navision works with MS-SQL option.

LOCKTABLE does not lock anything by itself. It only instructs SQL server to switch to SERIALIZABLE transactions isolation level which means reading the record in one transaction locks it so other transaction can neither change it nor read it.

Locking is done on record level in MS-SQL.
You lock only the records you have read.
FIND('+') locks the insertion of the below/next record
FIND('-') locks the insertion of the above/previous record
NEXT can lock not just the record you get by it but also several next records

einsTeIn.NET

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;

einsTeIn.NET

I got it. It has something to do with CLEAR/INIT.
When the event is fired I use INIT to initialize rec1. Native NAV acts like I expect but SQL NAV seems to have still the old record in focus. When I use CLEAR everything's fine.