A real UpdateNoLocks please

rdebath

I have in recent months been trying to improve the performance of a Navision style posting routine using normal SQL techniques. That is by reducing the lock footprint of various tasks so that it's possible to run more of them at the same time. I've had quite a bit of success.

Some things are easy, some things are impossible. For example, number series. By definition these are actually impossible to multi-thread as the number series "allocate next number" is a serialisation point. So the only solution is to open a distinct connection to the database so that the 'next' number can be allocated to the user to use when they're ready. This can be done in a tiny transaction with a minor cleanup if there's a rollback. (BTW: There is an outstanding SQL feature request to be able to do this in a single connection to the database)

But what want to ask about is something that should be simple, updating things in the database with the minimum of locks. Navision uses the READUNCOMMITTED table hint, so at first glance this looks very easy, just tell Navision "UpdateNoLocks". Except, it switches to SERIALIZABLE as soon as you do a single write to the table and there's NO CONTROL it even overrides FINDSET.

So my question is simple, is there an outstanding request with Microsoft that I can attach to (vote for, whatever) for a UpdateNoLocksAndIReallyMeanNoLocks Navision transaction type. Or UNLOCKTABLE command or LOCKTABLE(FALSE) or whatever is the best solution to this.

Of course I could be wrong, is there a way to do only 'Manual locking' that I've missed?

Marije_Brummel

Unfortunately this is indeed the 'normal' behaviour of Navision on SQL Server.

This has everything to do with the fact that C/AL code should behave the same on the Classic database as in SQL Server and it behaves like this on Classic, thus on SQL. Simple example of 'weakest link' in the chain.

By dropping the Classic database it should be possible to "solve" this in v7 but in order to take real advantage of it, much of the existing code should be rewritten.

Rewriting code always leads to bugs, and NAV6 is finaly a little more stable so this should be done very, very carefully.

On top of this issue we also have the index locking behaviour of SQL Server which will also lead to locking that is difficult to predict.

rdebath

Mark Brummel wrote:

Unfortunately this is indeed the 'normal' behaviour of Navision on SQL Server.

This has everything to do with the fact that C/AL code should behave the same on the Classic database as in SQL Server and it behaves like this on Classic, thus on SQL. Simple example of 'weakest link' in the chain.

Ahh, not really. (IMO) For Native UpdateNoLocks locks the minimum it can which in this case is table locks. But for SQL it changes the Transaction Type to ForUpdate for just that table AFTER locking the minimum it can. Sure if you want to you can sell it as changing to ForUpdate locks is kind of like locking the table; I might believe it if the TABLOCK hint didn't exist and the fact that switching to ForUpdate locking isn't enough to make it work the same.

Mark Brummel wrote:

... NAV6 is finaly a little more stable

Um, really?

stryk

rdebath wrote:

Except, it switches to SERIALIZABLE as soon as you do a single write to the table and there's NO CONTROL it even overrides FINDSET.

Yep, (b)locking in NAV on SQL is a pain.
But at least this - SERIALIZABLE on update - could be a little bit improved:
https://mbs.microsoft.com/knowledgebase/KBDisplay.aspx?scid=kb$en-us$979135&wa=wsignin1.0
Requires NAV 5.0 SP1 30482 (or higher?)
By increasing the "diagnostics" value in "$ndo$dbproperty" by 4194304 ...

update [$ndo$dbproperty] set diagnostics = diagnostics + 4194304
go

... NAV will use REPEATABLE READ instead of SERIALIZABLE (details in the KB article - Caution: read first!).

Regarding the "No. Series Line", it could help to proceed like this:
Add some new fields to T309 "No. Series Line":
Dummy, Boolean
Dummy 1, Text250
Dummy 2, Text250
Dummy 3, Text250
Dummy 4, Text250
Dummy 5, Text250
Dummy 6, Text250
Dummy 7, Text250
Dummy 8, Text250

Then create dummy records which are inserted between the real No. Series records, here filling up all the Dummy-Text fields:

OBJECT Codeunit 50009 Create Dummy NoSeries
{
  OBJECT-PROPERTIES
  {
    Date=12.01.10;
    Time=15:00:58;
    Modified=Yes;
    Version List=;
  }
  PROPERTIES
  {
    OnRun=BEGIN
            NoSeriesLine.SETRANGE(Dummy, FALSE);
            IF NoSeriesLine.FINDSET THEN BEGIN
              REPEAT
                FOR m := 1 TO 5 DO BEGIN
                  NoSeriesDummy.INIT;
                  NoSeriesDummy := NoSeriesLine;
                  NoSeriesDummy."Series Code" := NoSeriesLine."Series Code" + '_' + FORMAT(m);
                  NoSeriesDummy.Dummy := TRUE;
                  IF NoSeriesDummy.INSERT THEN BEGIN
                    FOR n := 1 TO 25 DO BEGIN
                      IF STRLEN(NoSeriesDummy."Dummy 1") <= 240 THEN BEGIN
                        NoSeriesDummy."Dummy 1" := NoSeriesDummy."Dummy 1" + '1234567890';
                        NoSeriesDummy."Dummy 2" := NoSeriesDummy."Dummy 2" + '1234567890';
                        NoSeriesDummy."Dummy 3" := NoSeriesDummy."Dummy 3" + '1234567890';
                        NoSeriesDummy."Dummy 4" := NoSeriesDummy."Dummy 4" + '1234567890';
                        NoSeriesDummy."Dummy 5" := NoSeriesDummy."Dummy 5" + '1234567890';
                        NoSeriesDummy."Dummy 6" := NoSeriesDummy."Dummy 6" + '1234567890';
                        NoSeriesDummy."Dummy 7" := NoSeriesDummy."Dummy 7" + '1234567890';
                        NoSeriesDummy."Dummy 8" := NoSeriesDummy."Dummy 8" + '1234567890';
                      END;
                    END;
                    NoSeriesDummy.MODIFY;
                  END;
                END;
              UNTIL NoSeriesLine.NEXT = 0;
            END;
          END;

  }
  CODE
  {
    VAR
      NoSeriesLine@1000000000 : Record 309;
      NoSeriesDummy@1000000001 : Record 309;
      m@1000000002 : Integer;
      n@1000000003 : Integer;

    BEGIN
    END.
  }
}

Hence, this will create 5 spacer records of each about 2KB, thus in total 10KB. This actually means, that each real "No. Series Line" record is placed on a different PAGE.
When NAV is calculating the next numer of a specific "No. Serie" - e.g. S_ORD - it will lock this record ROW X plus the whole page PAG IX. This leads into blocking issues, even though another user uses a diferent "No. Serie" - e.g. P_ORD.
This is how "No. Series Line" becomes a single point of serialization. But once the records - S_ORD and P_ORD - are forced onto differente PAGES, blocking issues could be reduced ...
Of course, this never could help two processes using the same "No. Serie", but at least some blocking issues should vanish.

Hope this could help you a little.

Regards,
Jörg

rdebath

NAV 5.0 SP1 30482 (or higher?)

Excellent, We've switched to Update2 (30488) internally so I should be able to check this right away. Looks like the same change is 6.0.30609 on V6.

I understand the theoretical trade off, but I'm pretty sure that in practical terms the 'release' functionality in Navision will normally make it a non-issue. Still there may be a few spots where locking a header record is a good idea. Of course the same argument applies to repeatable read vs. committed read.

Your number series is good, you wrote it before the KB you referenced, yes? Because most of the reason for the dummy records between the number series is because of the range locks that the hotfix should suppress. Still I do wish "Always rowlock" where on a table by table basis.

But I probably won't be using your No. Series change because I'll be getting lots of numbers from the same series. I'll stick with my 'Linked table not in transaction' solution.

PS: That KB search is a tease... I got all excited when I saw a reference to 'READPAST' ... I wanted to use that from NAV a little while ago.

pdj

stryk wrote:

https://mbs.microsoft.com/knowledgebase/KBDisplay.aspx?scid=kb$en-us$979135&wa=wsignin1.0

Great, thanks for the info.
However; isn't the code in the example misleading?
I think the "MYTABLE.SETRANGE(MYTABLE."Line No.", 1, 10);" should be deleted, or am I missing the point?

rdebath

pdj wrote:

I think the "MYTABLE.SETRANGE(MYTABLE."Line No.", 1, 10);" should be deleted, or am I missing the point?

The example is not incorrect as long as document '1' doesn't have line 10. But, I think you're right because the line is unnecessary.

They're trying to say that SERIALIZABLE places range locks that block access to any record within the range, even ones that don't currently exist. But the range locks have to go between records that actually exist so if the last (or first) possible value in a range doesn't correspond to a record that exists the next record outside the range will also be locked and everything up to it.

It's sometimes called "adjacent row locking".

pdj

rdebath wrote:

pdj wrote:

I think the "MYTABLE.SETRANGE(MYTABLE."Line No.", 1, 10);" should be deleted, or am I missing the point?

The example is not incorrect as long as document '1' doesn't have line 10. But, I think you're right because the line is unnecessary.

Yes, I find the line adds all sorts of requirements to the data which isn't mentioned and confuses more than it helps.

kriki

[Topic moved from 'NAV Three Tier' forum to 'NAV/Navision Classic Client' forum]