No. Series not incrementing

Hello,

the behaviour on the SQL side seems to be different with RTC. I've traced both the CC and the RTC. Both do a "set transaction isolation level serializable" on the locktable(), at least you can see it in both traces. But the question is when that happens. In the code of CU396:GetNextNo() there is some code between the locktable and the findfirst() which results in a "select * with updlock from No_ Series Line". I would suspect that not every action that results in an SQL statement on CC will result in an SQL statement in the Service Tier. AFAICR the Service Tier does some caching on its own - or at least it seemed this way to me.
I'm afraid that proving this (maybe) bug will be quite difficult. Logging the entry/exit order of the GetNextNo() request would help, but you would still have the problem.

What you can try is verifying that you have the lock on the NoSeriesLine before doing the increment. The idea is this:

- SQL Server automatically places a row lock on a record that is modified or inserted.
- A modify can fail if somebody else was faster. Try again with a locktable, in this case the client waits until
it can place a lock or it times out.
- If we write a unique value into our record, modify it and read it back, we can safely assume we have a row lock. A suitable unique value would be the connection ID of our session.

The code for the change will look like this:

- T309: Add a field named "Connection ID", type BigInteger.
- C396: In function GetNextNo() add two local variables:

L_Session Record Session
L_RetryCount Integer

The code for the changed function is here:

IF SeriesDate = 0D THEN
  SeriesDate := WORKDATE;

IF ModifySeries OR (LastNoSeriesLine."Series Code" = '') THEN BEGIN
  //OS001s os.jgl
  //workaround-try for locking: no explicit lock, try a lock through modify first
  // ***************************************************** OldSchool Solutions *************************************************
  // Original Code
  // IF ModifySeries THEN
  //   NoSeriesLine.LOCKTABLE;
  // ***************************************************** OldSchool Solutions *************************************************
  //OS001e os.jgl
  NoSeries.GET(NoSeriesCode);
  SetNoSeriesLineFilter(NoSeriesLine,NoSeriesCode,SeriesDate);
  IF NOT NoSeriesLine.FINDFIRST THEN BEGIN
    NoSeriesLine.SETRANGE("Starting Date");
    IF NOT NoSeriesLine.ISEMPTY THEN
      ERROR(
        Text004,
        NoSeriesCode,SeriesDate);
    ERROR(
      Text005,
      NoSeriesCode);
  END;
  //OS001s os.jgl
  //all the code above was for selecting the right NoSeriesLine. Now we do the locking through a modify() - 
  //we try again with locktable if it didn't work. We assume that if we can modify and read our Connection ID back,
  //then we have a reliable lock on the NoSeriesLine.
  IF ModifySeries THEN BEGIN
    L_Session.SETRANGE("My Session",TRUE);
    L_Session.FINDFIRST;
    NoSeriesLine.FINDFIRST;
    REPEAT
      NoSeriesLine."Connection ID" := L_Session."Connection ID";
      //this also forces a SERIALIZABLE lock on the line. If we succeed.
      IF NOT NoSeriesLine.MODIFY THEN BEGIN
        //somebody else was faster, try again with SERIALIZABLE lock.
        NoSeriesLine.LOCKTABLE;
        NoSeriesLine.FINDFIRST;
        //we can see who was faster here
        NoSeriesLine."Connection ID" := L_Session."Connection ID";
        NoSeriesLine.MODIFY;
      END;
      NoSeriesLine.FINDFIRST;
      //and - if locking doesn't work - here
      //The RetryCount is just a Sanity check to avoid a closed loop.
      L_RetryCount += 1;
    UNTIL (NoSeriesLine."Connection ID" = L_Session."Connection ID") OR (L_RetryCount > 10);
    IF L_RetryCount > 10 THEN
      ERROR(ErrorNoSeriesLocking,L_Session."Connection ID");
  END;
  //OS001e os.jgl
END ELSE
  NoSeriesLine := LastNoSeriesLine;

IF NoSeries."Date Order" AND (SeriesDate < NoSeriesLine."Last Date Used") THEN
  ERROR(
    Text006,
    NoSeries.Code,NoSeriesLine."Last Date Used");
NoSeriesLine."Last Date Used" := SeriesDate;
IF NoSeriesLine."Last No. Used" = '' THEN BEGIN
  NoSeriesLine.TESTFIELD("Starting No.");
  NoSeriesLine."Last No. Used" := NoSeriesLine."Starting No.";
END ELSE
  IF NoSeriesLine."Increment-by No." <= 1 THEN
    NoSeriesLine."Last No. Used" := INCSTR(NoSeriesLine."Last No. Used")
  ELSE
    IncrementNoText(NoSeriesLine."Last No. Used",NoSeriesLine."Increment-by No.");
IF (NoSeriesLine."Ending No." <> '') AND
   (NoSeriesLine."Last No. Used" > NoSeriesLine."Ending No.")
THEN
  ERROR(
    Text007,
    NoSeriesLine."Ending No.",NoSeriesCode);
IF (NoSeriesLine."Ending No." <> '') AND
   (NoSeriesLine."Warning No." <> '') AND
   (NoSeriesLine."Last No. Used" >= NoSeriesLine."Warning No.") AND
   (NoSeriesCode <> WarningNoSeriesCode) AND
   (TryNoSeriesCode = '')
THEN BEGIN
  WarningNoSeriesCode := NoSeriesCode;
  MESSAGE(
    Text007,
    NoSeriesLine."Ending No.",NoSeriesCode);
END;
NoSeriesLine.VALIDATE(Open);

IF ModifySeries THEN
  NoSeriesLine.MODIFY
ELSE
  LastNoSeriesLine := NoSeriesLine;
EXIT(NoSeriesLine."Last No. Used");

You also need a suitable text constant for "ErrorNoSeriesLocking". To use the session table you need additional rights (select permision) on the dbo.Session view.

I think it's worth a try - doing the modify should force the locking of the row. Since every client does the same, the No. Series should increment as intended.

with best regards

Jens